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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.