CODEWITHSUNDEEP
mysqlprivileges
Grim...
Grim...

Reputation: 16953

Allowing a MySQL user to grant themselves privileges to a new database

I have a MySQL user, which we'll call Tom. Tom has global CREATE and SHOW DATABASES privileges, with the 'GRANT' option. The user is assigned SELECT, INSERT, UPDATE and DELETE privileges on a per-database basis. However, after Tom creates a new database, he can't set his SELECT etc. privileges, despite having a global GRANT option. The error is (predictably) : 

CREATE DATABASE `my_new_db`; -- this is fine
GRANT SELECT , INSERT , UPDATE , DELETE ON `my_new_db` . * TO 'Tom'@'%';
*Access denied for user 'Tom'@'%' to database 'my_new_db'*

This is being done via PHP, but I doubt that makes much difference.

Someone tell me what I'm doing wrong! Ta =]

Upvotes: 5

Views: 1343

Answers (2)

perissf
perissf

Reputation: 16273

You are doing correctly. It's by design. However what you can do is written in the Reference Manual: "MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for databases or tables that are to be created at a later time."

http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges

Upvotes: 2

kbolino
kbolino

Reputation: 1764

You can't grant privileges you don't have. Tom needs to have global SELECT, INSERT, UPDATE, and DELETE privileges in order to grant them to others.

Upvotes: 2

Related Questions