Grant user to multiple db but only one table

Question

I'm trying to grant access one user to many db that have been created on my system like this:

dbUser_1 dbUser_2 ...

and so on.

I can use GRANT SELECT, INSERT, UPDATE ON `dbUser\_%`.* TO 'kenny'@'localhost'; To let kenny user to insert, select and update ALLL the table of each db.

I'm looking for make something like this: ATENTION, this don't work: GRANT SELECT, INSERT, UPDATE ON `dbUser\_%`.*`bill` TO 'kenny'@'localhost';

To let kenny get access to all the bill tables on each db.

¿It is possible?

I'm using mysql. Thanks in advance.

Voli Runbekcisi · Accepted Answer · 2020-05-29 14:32:10Z

0

You can use this:

GRANT SELECT, INSERT, UPDATE ON DBNAME.TABLE_NAME TO USERNAME

So it will be something like this:

GRANT SELECT, INSERT, UPDATE ON dbUser\_%.* TO 'kenny'@'localhost';

For more check: https://dev.mysql.com/doc/refman/5.7/en/grant.html

Update: When specifying a table level privilege, wildcards in the DB are treated as the character _ and %. They are not wildcards anymore. See the GRANT documentation under "Object Quoting Guidelines" (@mhost)

edited May 29, 2020 at 14:32

answered May 27, 2020 at 10:35

Voli Runbekcisi

135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Antonio Over a year ago

Hi Voli, this answer don't works for me because i'm looking for Grant access to all the db with the same prefix (dbUser_1, dbUser_2 ...) but only one of the tables of each database (bill) When I try to Grant with wildcards, the system tell me that the "Table doesn't exist"... I need this solution to let the future db of the sistem be updated by this user. Thanks for your aswer.

Voli Runbekcisi Over a year ago

I can only redirect you to this: stackoverflow.com/questions/2668591/… Sorry that I couldn't help you with this :)

Karol Murawski · Accepted Answer · 2025-02-14 11:56:53Z

0

There is another option:

GRANT ALL PRIVILEGES ON `%`.`my_table` TO `myuser`@`myhost`;
REVOKE INSERT,CREATE,DROP,UPDATE,DELETE,ALTER,REFERENCES,CREATE VIEW,TRIGGER,INDEX,SHOW VIEW ON `%`.`my_table` FROM `myuser`@`myhost`;

I had today similar problem and same error as you.

answered Feb 14 at 11:56

Karol Murawski

3943 silver badges12 bronze badges

Collectives™ on Stack Overflow

Grant user to multiple db but only one table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related