MySql grant permissions on all databases beginning with <string>

Question

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';

Answer 1

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)