Search code examples
mysqlpermissionsprefixsql-grant

Granting permissions in MySQL to tables with certain prefixes

What can be the quickest way to grant permissions to a mysql table with certain prefixes?

GRANT ALL ON prefix_*.* to ''@'';

Any help is most welcome.

I believe, it is be a common problem.

Solution

  • Instead of using table prefixes, put the tables in separate databases. That is, work with the database, using its build-in mechanisms. MySQL makes it easy to grant access to all tables in a database. This has the additional advantage that as new tables are added into each database, the permissions just work. You don't have to keep going back and fiddling with permissions to add tables.

    If you go down your path of using prefixes, I think you will need to use prepared statements.