John Ratliff wrote:
I'm trying to use a grant statement to grant privileges to a user on a set of database names.
e.g. for some user k, I want them to be able to have complete access to any database named k_*.
I know this can be done. The test database is setup this way by default.
Here is what I tried:
grant all on k_*.* to 'user'@'somedomain' identified by 'somepassword'
It replies with an 'you have an error in you SQL syntax somewhere.'
However, I can do this:
grant all on k_db.* to 'user'@'somedomain' identified by 'somepassword' update db set db = 'k\_%' where user = 'user'
This is how the test database looked in the mysql.db table, so I mirrored it. And that works.
But, can this be done in a grant statement? I was fairly sure I did this before, but this is a new installation and it doesn't work.
I've tried variations of the *. I tried k_%.* k_%, "k\_%.*", etc. None of these work.
I have looked in the mysql manual in the user creation section, but I haven't found anything similar to what I'm trying to do. The grant/revoke syntax also tells nothing. It just says db_name.* if you want to grant privileges on a database.
My mysql version is 4.0.18.
Thanks,
John Ratliff
You must have missed it. From the GRANT and REVOKE Syntax page in the manual <http://dev.mysql.com/doc/mysql/en/GRANT.html>:
Note: the `_' and `%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a `_' character as part of a database name, you should specify it as `\_' in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....
So, you need to backslash escape the underscore, and you need to quote the db name with backticks.
GRANT ALL ON `k\_%`.* TO [EMAIL PROTECTED] IDENTIFIED BY 'somepassword';
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
