Home / server / Questions / 140714
Accepted
scetoaux
Asked: 2010-05-12 08:01:46 +0800 CST

MySQL asking a user for SUPER privilege to perform a delete

  • 772

When trying to do a delete operation on a table, mysql reports the following error:

Error code 1227: Access denied; you need the SUPER privilege for this operation.

However, my user has this privilege granted for all tables in the schema:

GRANT ALL PRIVILEGES ON myschema.* TO 'my_admin'@'%'

How come it asks me for SUPER privilege for a delete?

mysql security sql permissions database

4 Answers

  1. Best Answer

    Are you sure that you're not logged in as some less privileged user? You get the privileges of the user you are logged in as, not of all users that you conceivably could log in as.

    If [email protected] has fewer privileges than myadmin@% and you are logging in from 10.11.12.13, you get the former's privileges.

    Do \s from a mysql client to see what "current user" you are, then SHOW GRANTS FOR that user.

    You did do FLUSH PRIVILEGES after executing the GRANT, I assume.

    • 3

  2. I had the same problem. It was an incomplete installation that caused it. I was unable to run mysql from the command line with root access because I had not set a root password. So I re-installed mysql (didn't need to) - oh yeah backed up my tables first using mysqldump: mysqldump --all-databases > huge_dump.dump ( this didn't ask me for a password ) Here's the key - Run the mysql_secure_installation script:

    mysql_secure_installation

    Bla Bla Bla - - - Enter current password for root (enter for none); HIT ENTER since you have not set a root password yet

    Set root password? [Y/n] y <--- say yes !! New password: kick_me_hard Re-enter new password: kick_me_hard Password updated successfully! Reloading privileges tables... . . .Success!

    Now you can login using phpMyAdmin or command line:

    mysql -u root -p

    Enter password: kick_me_hard Type 'help;' or '\h' for help bla bla bla

    mysql>

    Now you are the coolest guy(gal) around since you fixed it. Unless you are the only one around - well then you are still the coolest one around!

    • 2

  3. Have a look in the mysql.* tables. It is possible that some permissions have been set on that table that remove your access. I know MySQL's permissions don't normally work that way, but it's worth looking at.

    Also, does the table's file itself have the correct file system permissions? If MySQL can't write to it, it might confuse the permissions subsystem as to what's wrong.

    • 0

  4. Another possibility is: maybe there's a trigger (such as a delete EVENT) against that table. If you delete rows on that table, it will issue the trigger, but the trigger needs the SUPER privilege to execute.

    • 0