MySQL Grant for more than one database

Question

I'm trying to set the privileges for two databases at once. I know it is possible to assign them in two statements. Is there a way to do it in one?

I tried

GRANT ALL PRIVILEGES 
       ON mydb1.*, mydb2.*
       TO 'reader'@'localhost'
       IDENTIFIED BY 'mypassword';

But it only seems to work for one database.

Answer 1

No you can't, as you can see in the GRANT syntax diagram. Although apparently, you can use the wildcard *.* to apply the grant to all databases, but I wouldn't do that.

Answer 2

From the GRANT documentation (https://dev.mysql.com/doc/refman/5.7/en/grant.html):

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level. This means, for example, that if you want to use a _ character as part of a database name, you should specify it as \_ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar\`.* TO ....

So, in order to do the above (mydb1 and mydb2), just do

GRANT ALL PRIVILEGES ON `mydb%`.* TO 'reader'@'localhost';

Etc. (Assuming you don't have a mydb3 or mydb_funkytown, etc., that you also don't want to grant privileges on.)

See also:

• Grant on multiple databases. MySQL
• grant to multiple db using one command