9

I have given a user full control over one table in a database. Hoever it appears they cant delete records.

I've tried as a privilaged user:

GRANT DELETE ON databasename.tablename TO username@'%';
flush privileges;

But delete stil doesn't work

ERROR 1142 (42000): DELETE command denied to user 'username'@'localhost' for table 'tablename'

Any ideas?

Cheers,

Nathan.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Nathan Friend
  • 205
  • 1
  • 3
  • 10

2 Answers2

12

Your GRANT statement might need additional quotes for the username: 'username'@'%'.

You can check the user's privileges in the mysql database. Check the tables user, db, hosts, tables_priv.

You might have entries with the same username and different hostnames, like 'username'@'localhost' and 'username'@'%'.

The MySQL-Documentation describes in which order MySQL evaluates these tables:

http://dev.mysql.com/doc/refman/5.1/en/request-access.html

If you have an entry in the table tables_priv allowing the user to DELETE, that should normally be sufficient.

AFAIK you do not need to run FLUSH PRIVILEGES after a GRANT - you only need to FLUSH if you modify the privilege tables manually with INSERT,DELETE etc.

titanoboa
  • 2,548
  • 15
  • 12
0

You might try this:

May be your mySQL workbench does not allow the delete function on Global Privilege on your current user

Go to Administration - User and Privilege > Select User Account > Go to Administrative Roles Tab > then on Global Privileges check the DELETE.