Mysql – Wildcard for table permissions

MySQLpermissions

I want to grant a user permission to select,insert,update,delete on a few tables in the prod database. However, I find it time consuming to write each grant statement out per table. Is it possible to use a wildcard?

There are 300 tables, the user only needs access to 18 of them. The tables the user needs access to are prefixed 'vs_'.

Can I do a grant select,insert,update,delete on prod.vs_\* ? I know prod.\* is possible but wasn't sure on the prefix of a table.

Best Answer

No, the wildcard for table names can only be *, and does not permit other characters or patterns.

You could generate the 18 GRANT statements you need:

SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%';

Capture the output of that query, and then run it as a series of statements.

Related Solutions

MySQL Grant Permission

'insert' permission should be all you need to do an insert:

create database stack;
create user dummy;
create table stack.t(v integer primary key);
grant insert on stack.* to dummy;

as dummy:

insert into stack.t values(1);
Query OK, 1 row affected (0.00 sec)

select * from stack.t;
ERROR 1142 (42000): SELECT command denied to user 'dummy'@'localhost' for table 't'

and the grant like this will even apply to tables created after the grant was made - it gives a database privilege

As you also want to select you will also need to grantselecton stack.* to dummy

What is the mysql.db Table Used For?

From the docs, mysql.db is the table that handles database-specific GRANTS. That is to say, if you explicitly indicated a database in your GRANT command, it would show up in this table:

GRANT SELECT, INSERT ON foo.* TO `bar`@`localhost`;

So the user bar@localhost would have SELECT and INSERT marked as 'Y' in the mysql.db table.

To remove entries from this table, likewise you need to specify the database:

REVOKE SELECT, INSERT ON foo.* FROM `bar`@`localhost`;

Issuing a REVOKE INSERT ON *.* statement (all databases) will not affect this table.

Also, a DROP USER statement will clean up all the entries in the various mysql.* tables that deal with user authentication, such as mysql.db.

Best Answer

Related Solutions

MySQL Grant Permission

What is the mysql.db Table Used For?

Related Question