mysql/mariadb授权时如何排除某个表

概述

最近有个需求，在授权时排除某个表，百度了一些资料，发现没有其他方案，只能对单个表进行授权，假如库里有几百 上千个表，工作量非常大，后来百度到一个批量生成授权语句的SQL，现在把步骤记录一下。

创建一个新的账户

首先创建一个新的用户

CREATE USER new_user;

生成授权语句

SELECT
	CONCAT( "GRANT ALL PRIVILEGES ON db_name.", table_name, " TO 'new_user'@'localhost' IDENTIFIED BY 'password';" )  -- 这里的db_name填写需要授权的库名，table_name不用改
FROM
	information_schema.TABLES 
WHERE
	table_schema = "db_name"  -- 这里的db_name填写需要授权的库名
	AND `table_name` != "exclude_table_name"; -- 这里填写排除的表

执行完毕以后，会生成如下授权语句，数量为：所有表数量减去排除的表数量，生成以后检查一下数量是否正确

GRANT ALL PRIVILEGES ON db_name.table_name TO 'new_user'@'localhost' IDENTIFIED BY 'password';

执行生成的语句

登录mariadb，复制刚才生成的语句，执行。切记，不要在第三方SQL工具里执行，只能在mariadb客户端里执行。

为排除的表授权

这一步为排除的表单独进行授权，我这里只授权了SELECT权限

GRANT SELECT ON db_name.exclude_table_name TO 'new_user'@'localhost' IDENTIFIED BY 'password';

测试

使用刚才创建的用户登录mysql，执行SELECT正常，执行INSERT，UPDATE，DROP则提示：

ERROR 1142 (42000): XXX command denied to user 'new_user'@'localhost' for table 'exclude_table_name'