CODEWITHSUNDEEP
mysqlsql-grant
fooquency
fooquency

Reputation: 1653

Can wildcards be used on tablenames for a GRANT in MySQL

Is it possible in MySQL to do a GRANT to a user on a set of tables within a database, e.g. to allow CREATE AND DROP ing of some table names but not others?

Neither of these seem to work:

GRANT SELECT ON  `testdb`.`%_testing` TO  'wildcardtest'@'localhost';
GRANT SELECT ON  `testdb`.`testing%` TO  'wildcardtest'@'localhost';

and the MySQL manual doesn't seem to give an answer either way.

Upvotes: 5

Views: 5063

Answers (3)

Jørgen Skoglund Hansen
Jørgen Skoglund Hansen

Reputation: 51

Create a new empty database . Give it access to the original database ( use a user who allready have access to original database ) in this new database CREATE VIEW test as SELECT * from originaldatabase.tablename WHERE conditions...

Then give test user access to NewDatabase whith GRANT select on NewDatabase.* to 'testuser'@'localhost'

Then only create views for the tables you want testuser to access.

Also remember you can do a USER() in the WHERE part of the view:

example: create view test as select * from original.customer where mysql_user = USER()

In the original.customer you must then have a column 'mysql_user' and every row the test user is allowed to see must have testuser@localhost as a entry

The testuser will see all the created views as tables in the database 'test'

Upvotes: 1

Ian Clelland
Ian Clelland

Reputation: 44142

The only wildcard that works in the GRANT statement is *

GRANT SELECT ON `testdb`.* TO 'user'@'localhost';
GRANT SELECT ON *.* TO 'privilegeduser'@'localhost';

It's all or one; there's no facility for dynamic matching of table names to granted privileges.

Upvotes: 6

Justin Grant
Justin Grant

Reputation: 46683

Nope. You can separate table names with commas but can't use wildcards in a GRANT.

Upvotes: 1

Related Questions