MySQL批量生成赋权语句 - 牧人池塘 - DBA杂谈录

分类: MySQL

2023-06-12

暂无评论

MySQL批量生成赋权语句

赋予test1库的aa开头的表SELECT,UPDATE权限，快速生成SQL:

mysql> SELECT CONCAT('GRANT SELECT,UPDATE ON test1.', table_name, ' TO adm@\'%\';') AS grant_statement FROM information_schema.tables WHERE table_schema = 'test1' AND table_name LIKE 'aa%' ;
+-------------------------------------------------------+
| grant_statement                                       |
+-------------------------------------------------------+
| GRANT SELECT,UPDATE ON test1.aa110 TO adm@'%';        |
| GRANT SELECT,UPDATE ON test1.aa112_110 TO adm@'%';    |
| GRANT SELECT,UPDATE ON test1.aa_dd112_110 TO adm@'%'; |
| GRANT SELECT,UPDATE ON test1.aadd112110 TO adm@'%';   |
+-------------------------------------------------------+
4 rows in set (0.00 sec)

将结果输出到/tmp/output.txt中：

mysql> SELECT CONCAT('GRANT SELECT,UPDATE ON test1.', table_name, ' TO adm@\'%\';') AS grant_statement FROM information_schema.tables WHERE table_schema = 'test1' AND table_name LIKE 'aa%' INTO OUTFILE '/tmp/output.txt';
Query OK, 4 rows affected (0.00 sec)

[root@AlmaLinux9 tmp]# cat /tmp/output.txt
GRANT SELECT,UPDATE ON test1.aa110 TO adm@'%';
GRANT SELECT,UPDATE ON test1.aa112_110 TO adm@'%';
GRANT SELECT,UPDATE ON test1.aa_dd112_110 TO adm@'%';
GRANT SELECT,UPDATE ON test1.aadd112110 TO adm@'%';

最后使用管理账号执行生成的SQL语句即可。

Tag标签:「赋权 mysql grant」更新时间:「2023-07-20 14:33:56」阅读次数:「840」

相关博文：