Grant privileges on several tables with specific prefix

mysql database grant database-permissions
Chad picture Chad · May 4, 2011 · Viewed 27.6k times · Source

I'm using the table prefix method for having several clients use the same database. The number of tables created per client will be ~55. Instead of doing all of the granting by manually listing out the tables, can I do something like the following?

GRANT SELECT,INSERT,UPDATE,DELETE ON database.prefix_* TO 'username'@'localhost' IDENTIFIED BY 'password';

Answer

CogitoErgoSum picture CogitoErgoSum · May 4, 2011

Advance Note: This is not my answer. I found it at http://lists.mysql.com/mysql/202610 and have copied and pasted for simplicity credit to Stephen Cook

You can use the INFORMATION_SCHEMA.TABLES view to generate the GRANT statements for you. Write a query along these lines:

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

Then run it, copy the results, and run those results as a query or script. You can of course get as crazy as you want with this, for example if you do this for many users maybe write a stored procedure that takes a parameter for the username and can therefore be used as a tool whenever you need it.

It isn't a syntax you asked for, but it is a nice trick that works.

--

Replace the table schema 'test' with the name of your database. foo_% can be replaced with the appropraite prefix_%

I tried this on my own and it worked great.

Related questions

Why is a "GRANT USAGE" created the first time I grant a user privileges?

I'm new to the admin side of DBMS and was setting up a new database tonight (using MySQL) when I noticed this. After granting a user a privilege for the first time, another grant is created that looks like GRANT …

Read More
Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

I have a MySQL user called dump with the following perms: GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ... GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%' GRANT SELECT, LOCK TABLES ON `myschema`.* TO 'dump'@'%' …

Read More
ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES)

I Installed MySQL on my CentOS 6.4 server. I logged into my root and changed its password. Later I thought that I should make a new user and use that user as my default user, So I created a new user …

Read More