Bug #81860 read_only is not allowed for SUPER because of prelocking

• Submitted: 15 Jun 2016 10:18
• Modified: 15 Jun 2016 11:36
• Reporter: Sergei Golubchik
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[15 Jun 2016 10:18] Sergei Golubchik

Description:
the server checks read_only when tables are being locked. because of prelocking a non-SUPER user might need to write-lock a table that he has no rights to write to.

How to repeat:
create user foo@localhost;
grant select,insert on test.* to foo@localhost;
create table t1 (a int);
delimiter ||;
create procedure p2()
begin
  insert t1 values (1);
end||
create definer=foo@localhost function f1() returns int
begin
  call p2();
  return 0;
end||
delimiter ;||
set global read_only=1;
connect (foo,localhost,foo);
select test.f1();
disconnect foo;
connection default;
set global read_only=0;
drop procedure p2;
drop function f1;
drop table t1;
drop user foo@localhost;

[15 Jun 2016 11:36] MySQL Verification Team

Hello Sergei,

Thank you for the report and test case.

Thanks,
Umesh

[15 Jun 2016 11:37] MySQL Verification Team

-- 5.6.31/32

main.81860                               [ fail ]
        Test ended at 2016-06-15 13:14:26

CURRENT_TEST: main.81860
mysqltest: At line 17: query 'select test.f1()' failed: 1370: execute command denied to user 'foo'@'localhost' for routine 'test.f1'

-- 5.7.13/14

set global read_only=1;
main.81860                               [ fail ]
        Test ended at 2016-06-15 13:32:47

CURRENT_TEST: main.81860
mysqltest: At line 17: query 'select test.f1()' failed: 1370: execute command denied to user 'foo'@'localhost' for routine 'test.f1'
safe_process[28377]: Child process: 28378, exit: 1

-- 5.5.50

set global read_only=1;
main.81860                               [ fail ]
        Test ended at 2016-06-15 13:33:18

CURRENT_TEST: main.81860
mysqltest: At line 17: query 'select test.f1()' failed: 1290: The MySQL server is running with the --read-only option so it cannot execute this statement