The fundamental problem you have is this:
You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.
— http://dev.mysql.com/doc/refman/5.6/en/grant.html
This makes sense, since if it were otherwise, then you could just grant privileges to yourself.
There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user). If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.
The problem here is that I am invoking mysql as user danielsank, but that user does not have privileges to make new tables, nor to see the mysql database!
The issue becomes clear by invoking mysql as root
$ mysql -u root
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
Now we can see the mysql database, and we can check it for user information
mysql> USE DATABASE mysql;
mysql> SELECT User, Host, Password FROM user;
+------------------+-------------------------+-----------------------------+
| User | Host | Password |
+------------------+-------------------------+-----------------------------+
| root | localhost | |
| root | <my machine name> | |
| root | 127.0.0.1 | |
| root | ::1 | |
| debian-sys-maint | localhost | *<a really long string> |
| danielsank | localhost | |
+------------------+-------------------------+-----------------------------+
We can create the new database
mysql> CREATE DATABASE purchases;
and grant permissions to danielsank
mysql> GRANT ALL PRIVILEGES ON purchases . * TO 'danielsank'@'localhost';
Best Answer
I don't see any option in pt-show-grants to do this kind of restriction. There are options for
--onlyand--ignorespecific users, but not tables. Nor is there an automatic option to ignore grants on nonexistant tables.I also glanced in the code, and it doesn't seem to break out the tables to scan through them for any reason.
For what it's worth, I don't see any error when I try to grant to a non-existant table. I tested on Percona Server 5.6.16:
Can you tell me anything else about your version of MySQL and the specific error that it produces when you try to run that grant script?
Re your comment:
The difference between ALL PRIVILEGES and specific privileges seems to be deliberate, according to Bug #10406 Grant all command does not give error though table does not exist.
If you use
GRANT CREATE, SELECT ... ON test.notexist TO 'user'@'%'then this works. As long as you includeCREATEprivilege, then it permits the grant to the non-existant table.Anyway, that's interesting trivia, but it doesn't address your original question.
This has been requested before, but never implemented. It was recorded as a "blueprint" (like a feature request) here: https://blueprints.launchpad.net/percona-toolkit/+spec/pt-show-grants-for-nonexistent-tables
Perhaps someone would like to implement a patch and contribute it to Percona Toolkit?