update command is denied for user

Question

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.

Answer 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.

Answer 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