CODEWITHSUNDEEP
mysql
Robin
Robin

Reputation: 613

ERROR 1143 (42000): SELECT command denied

I use a user who has the update privileges to execute a sql:

update stu set age = 27 where name='zjw';

I got this error:

ERROR 1143 (42000): SELECT command denied to user 'update_user'@'localhost' for column 'name' in table 'stu'

The table like this:

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

update_user's privileges is:

grant update on *.* to 'update_user'@'%';

MySQL version is 5.1.73.

thanks.

Upvotes: 0

Views: 11755

Answers (3)

shinxg
shinxg

Reputation: 520

because when where clause is used in update, the datebase will select the rows that meet the where condition, so select privilege is needed while granting update privilege.

The SELECT privilege is also needed for other statements that read column values. For example, SELECT is needed for columns referenced on the right hand side of col_name=expr assignment in UPDATE statements or for columns named in the WHERE clause of DELETE or UPDATE statements.

mysql manual here

Upvotes: 0

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

When you execute

UPDATE `stu` SET age = 27 WHERE name = 'zjw';

the SQL engine has to first select the rows it needs to update.

Therefore, if you do not have the SELECT privilege, you cannot perform such update, even if you have the UPDATE privilege.

Check out the Manual for the Grant Syntax.

Upvotes: 7

Zafar Malik
Zafar Malik

Reputation: 6844

@Robin as per your last comment, try to understand your update statement-

UPDATE `stu` SET age = 27 WHERE name = 'zjw';

Your above update statement first try to fetch the records where name='zjw', so if your name column is indexed then select use index and select directly only records those have value 'zjw' else it will scan whole table and select 'zjw' wherever in table.

Means first mysql use select statement internally before update, so you also need select privileges with any other privileges like update/delete etc.

so your grant command should be-

GRANT SELECT, UPDATE on db.* to 'myuser'@'localhost' identified by 'mypass';

You should give permission only specific IP or localhost as per requirement instead of globally by '%' which is risky.

Upvotes: 2

Related Questions