Grant privileges on several tables with specific prefix

Question

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';

It's strange. [Here](http://stackoverflow.com/a/5989300/344347) is the same question with positive answers. — Pavel Strakhov, May 23 '12 at 21:40
@PavelStrakhov I know your comment is kinda old, but this question is about table wildcard (which is not supported by MySQL) while your linked question is about database wildcard (which is supported) — leemes, Jul 25 '14 at 12:37

score 45 · Accepted Answer · edited May 04 '11 at 18:35

45

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.

edited May 04 '11 at 18:35

Ike Walker

64,401
14
110
109

answered May 04 '11 at 17:06

CogitoErgoSum

2,879
5
32
45

5

OP didn't have the courtesy to accept this answer, but it helped me so +1 – user470714 Jun 10 '11 at 19:14
You saved the day for me sir! – jestro Jan 09 '12 at 13:31
awsome solution, if you need to add specific privileges on a lot of (prefixed) tables. – Ramon de la Fuente Oct 03 '12 at 10:45
1

Same works for PostgreSQL too if you just lose the single quotes concatenated around the username. +1 – Gjordis Jun 23 '15 at 06:12
1

Nice solution. But it does not cover a case a new table with that prefix is being created after a user was granted privileges. – Sergii Golubev Feb 12 '21 at 12:30

score 4 · Answer 2 · edited Feb 09 '12 at 21:01

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

It seems like we CANNOT use wildcard for table names – Nam G VU Jan 17 '17 at 07:35 — Nam G VU, Jan 17 '17 at 07:35

score 0 · Answer 3 · answered Apr 08 '13 at 12:58

0

using bash:

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

answered Apr 08 '13 at 12:58

tilo

1

score 0 · Answer 4 · edited Jun 04 '23 at 09:58

0

The following

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

works fine (tested with MySQL 5.6.12 on Windows)

edited Jun 04 '23 at 09:58

Valerio Bozz

1,176
16
32

answered Dec 04 '13 at 15:32

Grynn

1,254
11
18

4

This only works for dynamically specifying the name of the database, instead of dynamically specifying the table names, unfortunately. – Danny Bullis Feb 11 '19 at 22:08
3

This does not work in 5.7. As described in the docs at [GRANT Syntax](https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-quoting): _"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."_ – Courtney Miles Feb 18 '19 at 04:30

Grant privileges on several tables with specific prefix

4 Answers4

Linked

Related