MySql 授予对所有以 开头的数据库的权限

Question

如果一个项目需要多个以相同 <string> 开头的 mySql 数据库并且希望授予用户对所有用户的相同权限，最优雅的方法是什么？

例子:

<string>_db_1
<string>_db_2
<string>_db_3
...
<string>_db_n

GRANT <privileges> ON <database> . * TO 'user'@'localhost';

Answer 1

您可以尝试以下方法:

mysql> create user 'alex'@'localhost' identified by 'xxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant create,select,insert,update,delete on `somestring_db_%`.* to 'alex'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

[root@test ~]# mysql -u alex -p
输入密码:
欢迎使用 MySQL 监视器。命令以 ; 结尾或\g。
你的 MySQL 连接 ID 是 7
服务器版本:5.7.28 MySQL 社区服务器 (GPL)
....
...
..
输入“帮助；”或 '\h' 寻求帮助。键入 '\c' 清除当前输入语句。

您正在通过 unix 套接字强制执行 ssl 连接。请考虑
关闭 ssl，因为它不通过 unix 套接字建立连接
更安全。

mysql> show grants;
+-------------------------------------------------------------------------------------------+
| Grants for alex@localhost |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO 'alex'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON somestring_db_%.* TO 'alex'@'localhost' |
+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create database somestring_db_1;
Query OK, 1 row affected (0.00 sec)

mysql> create database somestring_db_2;
Query OK, 1 row affected (0.00 sec)

mysql> create database somestring_db_3;
Query OK, 1 row affected (0.00 sec)

mysql> use somestring_db_1;
Database changed
mysql> create table test ( id int not null,name varchar(10) not null );
Query OK, 0 rows affected (0.50 sec)

mysql> insert into test values (1,'Hey'),(2,'there!'),(3,'It Works!!');
Query OK, 3 rows affected (0.18 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 1 | Hey |
| 2 | there! |
| 3 | It Works!! |
+----+------------+
3 rows in set (0.01 sec)

mysql> delete from test;
Query OK, 3 rows affected (0.25 sec)

mysql> select * from test;
Empty set (0.00 sec)