这篇文章讨论了一种有用的方法，可以将除一个特定表之外的所有数据库的所有权限授予用户。

MySQL 不支持将所有数据库的所有权限授予用户，然后撤消该用户对特定表的所有权限。
这会产生一个错误，指出没有针对特定表的授权规则。
例如：

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'

为了实现这一目标，我们需要为每个数据库/表单独授予权限。
有一种方便的方法可以在 information_schema.tables 上执行 SELECT CONCAT 以创建授权命令 SQL 脚本，然后通过 mysql 客户端执行该脚本。
例如：

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;