Search code examples
mysqlprivilegesmysql-error-1064sql-grant

select privilege on more than one table

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
Solution

  • 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.