4

Is there a possibility to do something like this?

GRANT SELECT , INSERT , UPDATE , DELETE ON  `database`.`prefix_*` TO  'user'@'localhost';

Which allows me to set permissions quickly for every table that starts with "prefix_"

Taapo
  • 1,785
  • 4
  • 18
  • 35
  • Yes, but using `%` as the wildcard, not `*`, and backslash-escape the `_` because that is itself a wildcard too. `prefix\_%` – Michael Berkowski Apr 19 '15 at 12:46
  • When I use the backslash-escape command I get: `#1103 - Incorrect table name 'prefix\_%'` – Taapo Apr 19 '15 at 12:55
  • Does any table with `prefix_` currently exist? – Michael Berkowski Apr 19 '15 at 12:59
  • Yes, but it's on an old test-machine - did this wildcard situation exist from older versions, or only on newer versions? I can grant access to all tables with the "prefix_" when I specify the full table name, just not with the wildcard. – Taapo Apr 19 '15 at 13:00
  • Are you quoting with backticks as in your example? Are you granting as root, or a user with only limited specific grants on that database? I just tested and could get this to work correctly. – Michael Berkowski Apr 19 '15 at 13:09
  • Hmm, wait - the MySQL docs only specify that wildcards work in _database_ names, not mentioning table names. It works in a table name for me on MariaDB, but regular MySQL may not support that. I reopened.... https://dev.mysql.com/doc/refman/5.0/en/grant.html – Michael Berkowski Apr 19 '15 at 13:11
  • Seems like it's all or nothing on _tablenames_ -- see http://stackoverflow.com/a/1489577/776264 – Taapo Apr 19 '15 at 13:20
  • It does appear that way for regular MySQL :/ – Michael Berkowski Apr 19 '15 at 13:22

1 Answers1

3

You can use % instead of *

GRANT SELECT , INSERT , UPDATE , DELETE ON  `database`.`prefix\_%` TO  'user'@'localhost';

From the docs:

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;

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331