3

My database has multiple tables that begin with 'field_data_', and I would like to grant a user SELECT access with just MySQL query using the wildcard.

I tried

GRANT SELECT ON db.field_data_% TO a_user@'localhost';

but am getting MySQL syntax error near the %.

pmagunia
  • 1,718
  • 1
  • 22
  • 33
  • Very similar to: http://stackoverflow.com/questions/6288554/mysql-grant-all-privileges-to-database-except-one-table which uses either dynamic or generates the necessary list using concat that you can then run. – xQbert Dec 13 '13 at 19:56
  • The problem with that is the script won't grant privileges for new tables. Isn't there someway to fix the syntax in the original post or can wildcards not be used with tables ? – pmagunia Dec 13 '13 at 20:11
  • 1
    No. (at least not to my knowledge) new tables require grants be applied on the new table. or you have to use roles to abstract user from table grant to the role, then grant role to user. but even this requires you to grant the role to the new table... – xQbert Dec 13 '13 at 20:48
  • Just found this: http://stackoverflow.com/questions/1489427/can-wildcards-be-used-on-tablenames-for-a-grant-in-mysql – pmagunia Dec 13 '13 at 20:57
  • this may be useful too http://stackoverflow.com/questions/2668591/grant-with-database-name-wildcard-in-mysql?rq=1 – pmagunia Dec 13 '13 at 21:00

0 Answers0