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

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


But it only works for one database.


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.



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

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



From the GRANT documentation ( ):

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:



All Articles