select privilege on more than one table

Question

How do I grant select privilege on more than one table in a single statement?

mysql> grant select on dbName.crw_changes to sink;
Query OK, 0 rows affected (0.02 sec)

mysql> grant select on dbName.crw_changes, dbName.bs_services to sink;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' dbName.bs_services at line 1

Answer 1

You can't do it in standard SQL. More than one user is permitted, but only one table.

That's generally true for most SQL dialects, too. There are reasons for the limitation, most notably related to checking permissions on the separate tables, though they were perhaps more relevant in the 1980s than they are now.

Answer 2

You can't. GRANT SELECT only works on one table at a time.

There's a workaround, however. You can do something like:

SELECT   CONCAT('GRANT SELECT ON test.', TABLE_NAME, ' to ''foouser'';')
FROM     INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA = 'test'
          AND TABLE_NAME LIKE 'foo_%'

The above extracts tables in the 'test' schema that start with 'foo_' and prints them out. You can then take those names and copy and paste them into a stored procedure or query builder window and execute them.

By the way, the code is untested (by me). I took it from http://lists.mysql.com/mysql/202610.