Grant privileges on several tables with specific prefix
Solution 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.
Solution 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 ....
Chad
Updated on November 21, 2020Comments
-
Chad over 3 years
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';
-
user470714 about 13 yearsOP didn't have the courtesy to accept this answer, but it helped me so +1
-
jestro over 12 yearsYou saved the day for me sir!
-
Ramon de la Fuente over 11 yearsawsome solution, if you need to add specific privileges on a lot of (prefixed) tables.
-
Gjordis about 9 yearsSame works for PostgreSQL too if you just lose the single quotes concatenated around the username. +1
-
Nam G VU over 7 yearsIt seems like we CANNOT use wildcard for table names
-
Danny Bullis over 5 yearsThis only works for dynamically specifying the name of the database, instead of dynamically specifying the table names, unfortunately.
-
Courtney Miles over 5 yearsThis does not work in 5.7. As described in the docs at GRANT Syntax: "When a database name not is used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine, wildcard characters are treated as normal characters."
-
Sergii Golubev over 3 yearsNice solution. But it does not cover a case a new table with that prefix is being created after a user was granted privileges.