CODEWITHSUNDEEP
mysqlsql-grant
Frank Vilea
Frank Vilea

Reputation: 8477

MySQL Grant for more than one database

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.

Upvotes: 4

Views: 6095

Answers (3)

Aaron Wallentine
Aaron Wallentine

Reputation: 2496

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:

Upvotes: 3

Jan S
Jan S

Reputation: 1837

You can either grant privileges to all databases (using *.*) or one at a time, but not to 2 at one time.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

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.

Upvotes: 3

Related Questions