Grant on multiple databases. MySQL

Question

How to grant on multiple databases? MySQL.

Something like

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE
ON 'databasesprefix%'.*
TO testuser@localhost IDENTIFIED BY 'testpasswd';

Solution 1

your example should work. from the (5.5) manual:

The “_” and “%” wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels.

with % matching any number (even zero) of characters, and _ matching exactly one character. if you want a _ in your database name, you have to escape it as \_. also watch the other caveats from the manual.

<UPDATE>as the other answer points out: if the database name contains wildcards, it has to be quoted with the identifier quote character, the backtick (“`”)</UPDATE>

Solution 2

You just need to use backticks instead of quotes around the db_name prefix.

I think this will work:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE
ON `databasesprefix%`.*
TO testuser@localhost IDENTIFIED BY 'testpasswd';

Comments

[quantumSoup]

"You can't use wildcards in the db_name of a grant statement." Yes you can.