Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a mysql user that currently has the following privileges:

mysql> show grants;
+--...--+
| Grants for debian sys-maint@localhost
+--...--+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD '*DA604C65D4EFF216D1F61FCD42726FA881FB6562' WITH GRANT OPTION |
+--...--+

I'd like it to be able to do that to another user:

mysql> grant all privileges on *.* to 'foo'@'%' identified by 'bar';
ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

What am I doing wrong?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Your sys-maint@localhost user doesn't have ALL privileges. It's lacking the CREATE TABLESPACE privilege.

If your user really had ALL privileges then when you did a SHOW GRANTS; you'd see:

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD '*DA604C65D4EFF216D1F61FCD42726FA881FB6562' WITH GRANT OPTION

To be able to do a GRANT ALL on another or new user, the user issuing the GRANT ALL must themselves have every permission or the grant will fail.

To grant your 'sys-maint@localhost' user CREATE TABLESPACE permissions issue the following command from an account that does have enough rights to do so (e.g. root):

GRANT CREATE TABLESPACE ON *.* TO 'sys-maint@localhost';

Or when logged in as sys-maint@localhost (and this works on MySQL 5.5) do the following:

USE mysql;
UPDATE user SET `Create_tablespace_priv`='Y' 
            WHERE user='sys-maint' AND host='localhost';
FLUSH PRIVILEGES;

Logout then log back in again (QUIT if using the command line mysql client or disconnect if using a GUI client such as HeidiSQL) and sys-maint@localhost should be able to issue the GRANT ALL command.

This works because your sys-maint@localhost already has sufficient privileges to update the user table.

And as always, when tinkering with MySQL permissions, remember to do a FLUSH PRIVILEGES;

share|improve this answer
    
It seems though that the FLUSH PRIVILEGES is not sufficient. I have to disconnect the session and reconnect before I can grant to the other user. –  Johannes Ernst May 8 '12 at 3:05
    
@JohannesErnst - I did mention that near the end "Logout then log back in again". –  Kev May 8 '12 at 3:07
    
@JohannesErnst - anyhoo....did it work? –  Kev May 8 '12 at 3:08
    
Yes, it does work. Thank you. The trick looking for the summary "all privileges" in the "show grants" is worthwhile to know. –  Johannes Ernst May 8 '12 at 4:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.