MySQL Bugs: #115779: wrong privileges when update

Bug #115779	wrong privileges when update
Submitted:	6 Aug 2024 7:10	Modified:	12 Aug 2024 2:10
Reporter:	seaver wu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I create a user, grant update database privilege and select privilege to a table of this database. But when I update the table, it prints an error message.
ERROR 1143 (42000): SELECT command denied to user 'user'@'localhost' for column 'id' in table 't1'

How to repeat:
// login by root
create database tmp;
use tmp;
create table t1(id int);
insert into t1 values(1);
CREATE USER 'user'@'%' IDENTIFIED BY 'user';
grant update,drop,create view,show view on tmp.* to 'user'@'%';
grant select,insert,update,delete on tmp.t1 to 'user'@'%';

// login by user
update tmp.t1 set id=1 where id=1;
ERROR 1143 (42000): SELECT command denied to user 'user'@'localhost' for column 'id' in table 't1'

// login by root
revoke update,drop,create view,show view on tmp.* from 'user'@'%';

// login by user
update tmp.t1 set id=1 where id=1;

Hi Mr. WU,

Thank you for your bug report.

However, this is not a bug.

You need to grant SELECT privileges on the entire table, since UPDATE requires reading the entire table in order to change it.

This is all described in our Reference Manual.

Not a bug.

thanks

You are truly welcome.