The post discusses a useful method to grant all privileges on all databases except one specific table to a user.
MySQL does not support granting all privileges on all databases to a user then revoking all privileges on a specific table from that user. This produces an error saying there was no grant rule for the specific table. For example:
mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.80 sec) mysql> CREATE TABLE testdb.testtable (i int); Query OK, 0 rows affected (0.99 sec) mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'testpass'; Query OK, 0 rows affected (0.12 sec)
mysql> GRANT ALL ON *.* TO 'test'@'%'; Query OK, 0 rows affected (0.18 sec)
mysql> SHOW GRANTS FOR 'test'@'%'\G *************************** 1. row *************************** Grants for test@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO 'test'@'%' *************************** 2. row *************************** Grants for test@%: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO 'test'@'%' 2 rows in set (0.00 sec)
mysql> REVOKE ALL ON testdb.testtable FROM 'test'@'%'; ERROR 1147 (42000): There is no such grant defined for user 'test' on host '%' on table 'testtable'
To achieve this goal, you need to grant individually per database/table. There is a handy way to perform a SELECT CONCAT on information_schema.tables to create your grant commands SQL script, and then execute the script via mysql client. For example:
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';
mysql> SELECT CONCAT("GRANT ALL PRIVILEGES ON ", table_schema, ".*", " TO 'test'@'%';") INTO OUTFILE "/var/lib/mysql-files/mytest1.sql"
FROM information_schema.TABLES
WHERE table_schema <> "testdb"
GROUP BY table_schema;
mysql> SOURCE /var/lib/mysql-files/mytest1.sql;
mysql> SELECT CONCAT("GRANT ALL PRIVILEGES ON ", table_schema, ".", table_name, " TO 'test'@'%';") INTO OUTFILE "/var/lib/mysql-files/mytest2.sql"
FROM information_schema.TABLES
WHERE table_schema = "testdb" AND table_name <> "testtable";
mysql> SOURCE /var/lib/mysql-files/mytest2.sql;