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.
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.
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.)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With