update command is denied for user

问题:

I've been using this free hosting site for development and testing.

I couldn't use an UPDATE MySQL command to change the database values, even though the user is supposed to be allowed to use all commands from cPanel.

Then, I've tested the same with a default user, it still won't work. However, it works fine on my system.

The MySQL error is

UPDATE command denied to user 'test'@'localhost' for table 'content'

Other commands are working fine.

Why is this happening? And how can it be prevented? Or any solution for this?

And I am very sure that users have permission to use the UPDATE command because I can use phpMyAdmin with the same user and modify the MySQL fields.

I don't understand why some MySQL commands from PHP are denied for a user who was given all priviliges and can do everything via phpMyAdmin. Given that script, phpMyAdmin and the SQL host are on the same server.

回答1:

For everyone who have tried answering this question here is my sincere thanks. I have found the problem and solution.

my sql query is like this

UPDATE `dblayer`.`test` SET `title` = 'hello a' WHERE `test`.`id` =1;

which I got from phpmyadmin and it works perfectly on my system. But when I work on the servers it doesn't work and it says command denied may be because

`dblayer`.`test`

I am trying to select the table globally (or something like that, I'm not sure) but if my sql query is

UPDATE `test` SET `title` = 'hello a' WHERE `test`.`id` =1;

it works on my server too.

回答2:

As everyone else said, it's permission issue. I am sure you probably checked, but just in case. After you log into phpmyadmin, run the following:

SELECT USER(); 

That should spit out 'test'@'localhost' as indicated in all the comments above

Then run

SHOW GRANTS FOR  'test'@'localhost'

That should give you all privs for that user. When you get results, select 'Options', Full Text and click go to get full text.

Make sure permissions in the output have something like that:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `your_database`.* TO 'test'@'localhost'

You might not be able to get GRANT ALL on some hosting setups but as long as you got INSERT, UPDATE, DELETE you should definitely be able to update the data

回答3:

Your user doesn't have the right permissions. You need to give it access to the UPDATE command, like so:

GRANT UPDATE ON database.* TO test@'localhost' IDENTIFIED BY 'password';

If you are using a graphical tool to manage the database - e.g. PHPMyAdmin or SQLYog, etc - you should be able to use those tools to enable these permissions too.

回答4:

I had the same problem, the permission was right but it was working in some environments and not in others. I was using a table name like

scheme.TABLE_NAME.

Without changing the permission but using just

table_name

worked in all environments.

回答5:

Sometimes it is a case sensitive problem.

MySQL also says access denied even if the table is not available. Please make sure that you have no case sensitive issues.

回答6:

the my sql error is UPDATE command denied to user 'test'@'localhost' for table 'content'

why is this happening? And how to prevent it? Or any solution for this?

It's happening for what it says it's happening: the user test does not have update permissions on table content. Something like this should grant the user the required permission:

GRANT UPDATE ON database.content TO test@'localhost' IDENTIFIED BY 'password';

*password above is just a place holder. You should use the real one.

回答7:

A UPDATE command denied error can on some web hosts be the result of reaching the MySQL database space limit.

回答8:

Provide the full access to test user with ip as localhost.Check the user table from mysql db.

Login as root and enter into mysql database and then to user table.You will be finding the current privileges of your test user.

回答9:

This error came up for me when I was creating triggers, which included an update clause, for remote MySql instance (via JawsDB).

Instead of setting up trigger this:

CREATE TRIGGER updateRecentDateFromProcGen After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration`` For Each Row BEGIN UPDATE ``mq6swfzd39ygjejl``.``users`` SET mostRecentDateAllActivities = NEW.date WHERE NEW.userID = users.ID; END

I set it up like this

CREATE TRIGGER updateRecentDateFromProcGen After Insert On ``mq6swfzd39ygjejl``.``proceduregeneration`` For Each Row BEGIN UPDATE ``users`` SET mostRecentDateAllActivities = NEW.date WHERE NEW.userID = users.ID; END Basically, I just removed the database name in the UPDATE line and left the table name by itself.

Note, wherever you see the `` there should only be one tilde not two. If anyone knows how to fix that in stack overflow's markup please let me know!

回答10:

With mysql you can just do SELECT * mysql.user

Look at the privileges and then update the ones you want. I like that more personally then using the above commands because I can see all of my options available in that table.

 UPDATE  mysql.user 
 SET  Insert_priv =  'Y',
 Update_priv =  'Y' 
 WHERE  user.Host =  'localhost' AND  user.User =  'test';

After updating the privileges I noticed I would have to restart the mysql server for the client to see them:

 /etc/init.d/mysql restart 

Then it would work fine