Clever Geek Handbook

Providing MySQL for more than one database

I am trying to set privileges for two databases at once. I know they can be assigned in two statements. Is there a way to do this in one?

I tried

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

But it only works for one database.

+2


source to share


3 answers


No, you cannot, as you can see in the GRANT syntax diagram . While apparently you can use a wildcard *.*

to apply the grant to all databases, but I wouldn't do that.



+3


source


You can either grant privileges for all databases (using *.*

), or one at a time, but not up to 2 at a time.



0


source


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

Wildcards _

and are %

allowed when specifying database names in statements GRANT

that grant database-level privileges. This means, for example, that if you want to use a character _

as part of a database name, you must specify it as \_

in the statement GRANT

so that the user cannot access additional databases matching the template pattern; e.g. GRANT ... ON`foo\_bar\`.* TO ....

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

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

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

See also:

0


source






More articles:


All Articles