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.
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 ( https://dev.mysql.com/doc/refman/5.7/en/grant.html ):
Wildcards
_
and are%
allowed when specifying database names in statementsGRANT
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 statementGRANT
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:
- Providing multiple databases. MySQL
- provide multiple db with one command