I am while using table prefix way of getting several clients make use of the same database. The amount of tables produced per client is going to be ~55. Rather than doing all the granting by by hand listing the tables, can one make a move such as the following?
GRANT SELECT,INSERT,UPDATE,DELETE ON database.prefix_* TO 'username'@'localhost' IDENTIFIED BY 'password';
Advance Note: This isn't my answer. I discovered it at http://lists.mysql.com/mysql/202610 and also have replicated and copied and pasted for simplicity credit to Stephen Prepare
You should use the data_SCHEMA.TABLES view to create the GRANT claims for you personally. Write a question along wrinkles:
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 outcomes, and run individuals results like a query or script. You are able to obviously get as crazy as you would like with this particular, for example should you choose this for a lot of customers maybe write a saved procedure that can take a parameter for that username and may therefore be utilized for a tool whenever you really need it.
It is not a syntax you requested, but it's a pleasant trick that actually works.
Replace the table schema 'test' using the title of the database. foo_% could be changed using the appropraite prefix_%
I attempted this by myself also it labored great.
I am unsure if you're able to wildcard table names, you are able to certainly wildcard database names though. Be careful though as
_ is really a wildcard matching any single character (like
. inside a regular expression).
The Documention is here now: http://dev.mysql.com/doc/refman/5.5/en/grant.html
The “” and “%” wildcards are allowed when indicating database names in GRANT claims that grant rights in the global or database levels. What this means is, for instance, that if you wish to make use of a “” character as a part of a database title, you need to specify it as being “_” within the GRANT statement, to avoid the consumer from being able to view additional databases matching the wildcard pattern for instance, GRANT ... ON
foo\_bar.* TO ....