Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9788

User permissions on procedures

    XMLWordPrintable

    Details

    • 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?

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            diegohellas Diego Hellas
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: