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

SET ROLE doesn't work in stored procedures

    XMLWordPrintable

    Details

      Description

      I created a procedure with a DEFINER. The definer has a role. I tried to enable the role with SET ROLE within the procedure, but it doesn't work.

      USE test;
       
      CREATE ROLE r;
      GRANT ALL PRIVILEGES ON *.* TO r;
       
      CREATE USER u@'%';
      GRANT r TO u@'%';
      GRANT SELECT ON *.* TO u@'%';
       
       
      DELIMITER ||
       
      DROP PROCEDURE IF EXISTS test_role_in_procedure ||
      CREATE DEFINER = u@'%' PROCEDURE test_role_in_procedure()
              SQL SECURITY DEFINER
      BEGIN
              SET ROLE r;
      END ||
       
      DELIMITER ;
       
      MariaDB [test]> CALL test_role_in_procedure();
      ERROR 1370 (42000): execute command denied to user 'u'@'%' for routine 'test.test_role_in_procedure'

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            f_razzoli Federico Razzoli
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: