MySql 授予对所有以 <string> 开头的数据库的权限. [英] MySql grant permissions on all databases beginning with <string>

问题描述

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

示例:

_db_1<字符串>_db_2<字符串>_db_3...<字符串>_db_nGRANT <特权>ON<数据库>.* TO 'user'@'localhost';

解决方案

您可以尝试以下方法:

<块引用><块引用>

mysql> 创建用户 'alex'@'localhost' 标识为 'xxxxxxxxxxxx';
查询正常，0 行受影响(0.00 秒)

mysql> 将 `somestring_db_%`.* 上的创建、选择、插入、更新、删除权限授予 'alex'@'localhost';
查询正常，0 行受影响(0.00 秒)

mysql> 刷新权限；
查询正常，0 行受影响(0.00 秒)

mysql> \q
再见

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

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

<块引用><块引用>

mysql> 显示授权；
+-------------------------------------------------------------------------------------------+
|给 alex@localhost |
+-------------------------------------------------------------------------------------------+
|授予对 的使用权. 给 'alex'@'localhost' |
|GRANT SELECT、INSERT、UPDATE、DELETE、CREATE ON somestring_db_%.* TO 'alex'@'localhost' |
+-------------------------------------------------------------------------------------------+
2 行(0.00 秒)

mysql> 创建数据库 somestring_db_1;
查询正常，1 行受影响(0.00 秒)

mysql> 创建数据库 somestring_db_2;
查询正常，1 行受影响(0.00 秒)

mysql> 创建数据库 somestring_db_3;
查询正常，1 行受影响(0.00 秒)

mysql> 使用 somestring_db_1;
数据库已更改
mysql> 创建表测试 ( id int not null,name varchar(10) not null );
查询正常，0 行受影响(0.50 秒)

mysql> 插入测试值 (1,'Hey'),(2,'there!'),(3,'It Works!!');
查询正常，3 行受影响(0.18 秒)
记录:3 重复:0 警告:0

mysql> select * from test;
+----+------------+
|身份证 |姓名 |
+----+------------+
|1 |嘿|
|2 |那里！|
|3 |有用！！|
+----+------------+
3 行(0.01 秒)

mysql> 从测试中删除；
查询正常，3 行受影响(0.25 秒)

mysql> select * from test;
空集(0.00 秒)

If a project requires multiple mySql databases beginning with the same <string> and one wishes to grant a user the same permissions on all of them, what is the most elegant way?

example:

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

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

解决方案

Here's how you can try :

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
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.28 MySQL Community Server (GPL)
....
...
..
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure.

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)

这篇关于MySql 授予对所有以 &lt;string&gt; 开头的数据库的权限.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！