[MDEV-9788] User permissions on procedures Created: 2016-03-24  Updated: 2022-09-08

Status: Stalled
Project: MariaDB Server
Component/s: Authentication and Privilege System, Data Definition - Procedure
Affects Version/s: 10.0.19, 10.1.12
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Diego Hellas Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: upstream
Environment:

CentOS 7
MariaDB 10.1.12
read_only = 1

Sprint: 10.0.26

 Description   

This does not happen in MariaDB 10.0, or in MySQL.

I have a server where read_only is enabled. But I have a procedure that needs to perform an INSERT to it have created a user with the SUPER permission.

If the procedures below were created in MariaDB 10.0.x or MySQL, the execution is was successful!

CREATE TABLE test.test_hellas
 
(
 
	id int auto_increment primary key
 
	, data timestamp default current_timestamp
 
	,  txt varchar(100)
 
);
 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'hellas'@'%' IDENTIFIED BY '123';
 
GRANT SELECT, SUPER, EXECUTE, TRIGGER ON *.* TO 'hellas_super'@'%' IDENTIFIED BY '123';
 
GRANT INSERT ON test.test_hellas TO `hellas_super`@`%`;
 
 
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS test_hellas$$
 
CREATE DEFINER=`hellas`@`%` PROCEDURE test_hellas()
 
    READS SQL DATA
 
BEGIN
 
	CALL test_hellas2();
 
END$$
 
DELIMITER ;
 
 
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS test_hellas2$$
 
CREATE DEFINER=`hellas`@`%` PROCEDURE test_hellas2()
 
    READS SQL DATA
 
BEGIN
 
	DECLARE a INT DEFAULT 0;
 
	SET a = f_test_hellas();
 
END$$
 
DELIMITER ;
 
 
 
DELIMITER $$
 
DROP FUNCTION IF EXISTS `f_test_hellas`$$
 
CREATE DEFINER=`hellas`@`%` FUNCTION `f_test_hellas`() RETURNS int(11)
 
    READS SQL DATA
 
    DETERMINISTIC
 
BEGIN
 
	CALL teste_hellas3();
 
	RETURN 1;
 
END$$
 
DELIMITER ;
 
 
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS teste_hellas3$$
 
CREATE DEFINER=`hellas_super`@`%` PROCEDURE teste_hellas3()
 
    READS SQL DATA
 
BEGIN
 
	INSERT INTO test.test_hellas(txt) VALUES('AAA');
 
END$$
 
DELIMITER ;

call test_hellas();
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

To work in MariaDB 10.1 I need to create the procedure test_hellas2 with DEFINER = hellas_super. But why?

DELIMITER $$
 
DROP PROCEDURE IF EXISTS test_hellas2$$
 
CREATE DEFINER=`hellas_super`@`%` PROCEDURE test_hellas2()
 
    READS SQL DATA
 
BEGIN
 
	DECLARE a INT DEFAULT 0;
 
	SET a = f_test_hellas();
 
END$$
 
DELIMITER ;

call test_hellas();
Query OK, 1 row affected, 1 warning (0.01 sec)

This is a bug or had a change in permissions mechanisms?

 Comments   
Comment by Elena Stepanova [ 2016-04-22 ]

I don't see any difference in behavior between 10.1 and 10.0 – for me 10.0 also returns an error (tried 10.0.24, 10.0.21, 10.0.11). Which version are you using?
You are getting a warning upon execution on 10.0, what does it say?

Comment by Diego Hellas [ 2016-04-25 ]

@Elena, I'm not talking between versions MariaDB10.x, but compared with MySQL.
In MySQL/Percona Server this error does not happen.

Comment by Elena Stepanova [ 2016-04-25 ]

@Elena, I'm not talking between versions MariaDB10.x

Actually, you were, the description says:

If the procedures below were created in MariaDB 10.0.x or MySQL, the execution is was successful!

But okay, we will ignore that part.

Comment by Diego Hellas [ 2016-04-26 ]

@Elena, I'll remake the my tests, now I was in doubt.

Comment by Diego Hellas [ 2016-05-24 ]

@Elena, you are right, I remeka the test in MariaDB 10.0.19 and the same error.
When I tested I should not be set to read only the server.
I redid the tests, and only work in MySQL 5.0.
I believe this task can be close.

Comment by Elena Stepanova [ 2016-06-09 ]

diegohellas, thanks for checking.

I'm not sure it should be closed just yet. I think something is actually wrong here, but wanted to get rid of the confusion regarding different versions and such.

In pseudo-code below bold names stand for routines with super privileges.

First scenario:

proc3 modifies data
func calls proc3
proc2 executes func
proc1 calls proc2
superuser calls proc1 => gets the error (can't modify the data in read-only mode)

Second scenario:

proc3 modifies data
func calls proc3
proc2 executes func
proc1 calls proc2
superuser calls proc1 => it works

Third scenario (not described above, I it tried additionally):

proc3 modifies data
func calls proc3
proc2 executes func
proc1 calls proc2
superuser calls proc1 => gets the error (can't modify the data in read-only mode)

The difference just does not make sense to me.
If permissions to override read-only are checked on the level of routine which actually modifies the data, all scenarios should work.
If they are checked on all levels in the execution chain, we should get the failure for all scenarios.
And even if on whatever reason they are only checked on levels above a function, but ignored below the function (which is not documented anywhere), still, scenarios 2 and 3 should work identically.

I will ask serg for the second opinion.
If the behavior turns out to be wrong, then whether we should fix it in MariaDB or re-file it for MySQL is a different question.

Comment by Sergei Golubchik [ 2016-06-15 ]

Reported the upstream bug: https://bugs.mysql.com/bug.php?id=81860

Comment by Diego Hellas [ 2016-10-12 ]

Hi @Sergei Golubchik

Thank you for your attention!

More information.
If I disable written to the binary log, the error does not happen.

Generated at Thu Feb 08 07:37:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.