Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 452
Nathan Friend Avatar asked Oct 08 '10 15:10

Nathan Friend


People also ask

How do I grant privileges to a user in SQL?

You can use the SQL GRANT statement to grant SQL SELECT, UPDATE, INSERT, DELETE, and other privileges on tables or views. The WITH GRANT OPTION clause indicates that JONES can grant to other users any of the SQL privileges you granted for the ORDER_BACKLOG table.

How do I grant select privileges to a user in MySQL?

To grant a privilege with GRANT , you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. (Alternatively, if you have the UPDATE privilege for the grant tables in the mysql system schema, you can grant any account any privilege.)


1 Answers

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.

like image 175
titanoboa Avatar answered Nov 09 '22 14:11

titanoboa