GRANT DELETE ON database.table TO username@'%';

Question

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.

Answer 1

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.

Answer 2

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.