CODEWITHSUNDEEP
mysqldatabasesql-grant
Antonio
Antonio

Reputation: 66

Grant user to multiple db but only one table

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.

Upvotes: 1

Views: 906

Answers (2)

Karol Murawski
Karol Murawski

Reputation: 384

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.

Upvotes: 0

Voli Runbekcisi
Voli Runbekcisi

Reputation: 13

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)

Upvotes: 0

Related Questions