3

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?

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
scetoaux
  • 1,289
  • 2
  • 12
  • 26
  • 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 May 11 '10 at 21:00

4 Answers4

3

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 myadmin@10.11.12.13 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.

David M
  • 614
  • 4
  • 15
  • 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. – scetoaux May 11 '10 at 21:04
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!

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
rich
  • 21
  • 1
0

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.

staticsan
  • 1,529
  • 1
  • 11
  • 14
0

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.

Alex Forbes
  • 2,452
  • 2
  • 20
  • 26
user117765
  • 1