Grant privileges on several tables with specific prefix

I’m using the table prefix method for having several clients use the same database. The number of tables created per client will be ~55. Instead of doing all of the granting by manually listing out the tables, can I do something like the following?

GRANT SELECT,INSERT,UPDATE,DELETE ON database.prefix_* TO 'username'@'localhost' IDENTIFIED BY 'password';

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Advance Note: This is not my answer. I found it at http://lists.mysql.com/mysql/202610 and have copied and pasted for simplicity credit to Stephen Cook

You can use the INFORMATION_SCHEMA.TABLES view to generate the GRANT
statements for you. Write a query along these lines:

SELECT   CONCAT('GRANT SELECT ON test.', TABLE_NAME, ' to ''foouser'';')
FROM     INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA = 'test'
      AND TABLE_NAME LIKE 'foo_%'

Then run it, copy the results, and run those results as a query or
script. You can of course get as crazy as you want with this, for
example if you do this for many users maybe write a stored procedure
that takes a parameter for the username and can therefore be used as a
tool whenever you need it.

It isn’t a syntax you asked for, but it is a nice trick that works.

—

Replace the table schema ‘test’ with the name of your database. foo_% can be replaced with the appropraite prefix_%

I tried this on my own and it worked great.

Method 2

I’m not sure if you can wildcard table names, you can definitely wildcard database names though. Watch out though as _ is a wildcard matching any single character (like . in a regular expression).

The Documention is here: http://dev.mysql.com/doc/refman/5.5/en/grant.html

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

Method 3

using bash:

mysql -Ne "show tables from test like 'foo_%'" | xargs -I {} mysql -Ne "GRANT SELECT ON test.{} TO 'foouser'@'%'"

Method 4

The following

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

works fine (tested with MySQL 5.6.12 on Windows)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating