GRANT with database name wildcard in MySQL?

Question

I want to create a user 'projectA' that has the same permissions to every database named 'projectA_%'

I know its possible but MySQL doesn't like my syntax:

grant all on 'projectA\_%'.* to 'projectA'@'%';

Reference: http://dev.mysql.com/doc/refman/5.1/en/grant.html

score 90 · Accepted Answer · answered Apr 19 '10 at 15:29

90

If I use back-tics instead of single quotes in the syntax, it appears to work just fine:

grant all on `projectA\_%`.* to `projectA`@`%`;

answered Apr 19 '10 at 15:29

JR Lawhorne

3,192
4
31
41

5

back-tics are only needed for the database name, not the user – glarrain Nov 07 '12 at 21:36
1

This not works for me when I use `specific table name` instead of `*` e.g. ``grant all on `projectA%`.`some_table%` to `some_user`@`%`;`` – Nam G VU Jan 17 '17 at 07:27
@NamGVU When specifying a table level privilege, wildcards in the DB are treated as the character _ and %. They are not wildcards anymore. See the GRANT documentation under "Object Quoting Guidelines" – mhost Jul 11 '19 at 20:31

score 15 · Answer 2 · edited Sep 13 '13 at 15:42

15

GRANT ALL PRIVILEGES ON `projectA\_%`.* TO 'projectA'@'%' IDENTIFIED BY 'your_passwd';

back-tics are needed for the database name

Edited:Underscore is now escaped.

edited Sep 13 '13 at 15:42

sabgenton

1,823
1
12
20

answered Jan 18 '13 at 06:42

johnson

151
1
2

score 8 · Answer 3 · answered Jul 11 '14 at 14:02

As per MySQL's GRANT documentation:

The “_” and “%” wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels. 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 ....

GRANT with database name wildcard in MySQL?

3 Answers3

Linked