MySQL asking a user for SUPER privilege to perform a delete

Question

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?

Solution 1

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.

Solution 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!

Comments

[scetoaux]

I'm definitely missing something here. Even "root" user cannot perform the update... As root user: mysql> delete from myschema.mytable where username='myuser'; ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation Any clue?

[scetoaux]

Thanks for the reply. Yeah I've double checked the username and the hostname wildcards, I've flushed privileges several times and, born in desperation, even bounced the mysql instance. Nothing worked.