Bug #92254 select for update produces error 1143 (42000) since mysql 8
Submitted: 31 Aug 2018 7:56 Modified: 3 Oct 2018 2:02
Reporter: Hans Desmet Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:8.0.12 OS:Windows
Assigned to: CPU Architecture:x86 (64 bit)
Tags: regression

[31 Aug 2018 7:56] Hans Desmet
Description:
First, you make an example database and an example user:
--------------------------------------------------------
create database bank charset utf8mb4;
use bank;
create table accounts (
  id int unsigned not null primary key,
  name varchar(50) not null
);
insert into accounts(id,name) values (1, 'test1');
create user if not exists john identified by 'john';
grant all on accounts to john;

Then you login as john and you execute following statement:
-----------------------------------------------------------
select * from accounts where id=1 for update;
You get following error:
ERROR 1143 (42000): SELECT command denied to user 'john'@'localhost' for column 'id' in table 'accounts'

This did NOT give an error in MySQL 5.

How to repeat:
See description.
[31 Aug 2018 9:29] MySQL Verification Team
Hello Hans Desmet,

Thank you for the report!
Verified as described with 8.0.12 build.

regards,
Umesh
[3 Oct 2018 2:02] Paul DuBois
Posted by developer:
 
Fixed in 8.0.14.

Incorrect privilege checking could produce an error for SELECT ...
FOR UPDATE statements that executed successfully in MySQL 5.7.