CODEWITHSUNDEEP
mysqlsyntaxpermissionssql-grant
JR Lawhorne
JR Lawhorne

Reputation: 3302

GRANT with database name wildcard in MySQL?

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

Upvotes: 51

Views: 56141

Answers (3)

Travis
Travis

Reputation: 669

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 ....

Upvotes: 9

johnson
johnson

Reputation: 151

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

back-tics are needed for the database name

Edited:Underscore is now escaped.

Upvotes: 15

JR Lawhorne
JR Lawhorne

Reputation: 3302

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

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

Upvotes: 97

Related Questions