Home / server / Questions / 386903
Accepted
Johannes Ernst
Asked: 2012-05-08 17:17:51 +0800 CST

mysql grant option

  • 772

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?

mysql permissions

1 Answers

  1. Best Answer

    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;

    • 5