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」阅读次数:「99」