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

CURRENT_ROLE() and DATABASE() does not work in a view

    Details

    • Sprint:
      10.3.1-1

      Description

      The CURRENT_ROLE() function does not seem to work in a view.

      Let's say that I create the following users and role:

      CREATE USER has_role@'localhost';
      GRANT ALL PRIVILEGES ON *.* TO has_role@'localhost';
       
      CREATE ROLE test_role;
      GRANT test_role TO has_role@'localhost';
       
      CREATE USER no_role@'localhost';
      GRANT ALL PRIVILEGES ON *.* TO no_role@'localhost';
      

      And I create the following table:

      CREATE TABLE view_role_test (
      	id int primary key,
      	role_name varchar(50)
      );
       
      INSERT INTO view_role_test VALUES (1, 'test_role');
      

      Now let's say that I create the following view:

      CREATE OR REPLACE 
      	DEFINER = no_role@localhost
      	SQL SECURITY INVOKER
      VIEW v_view_role_test
       
      AS
      	SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
      

      Since I specified "SQL SECURITY INVOKER", the call of CURRENT_ROLE() should return the current role of the user who is invoking the view.

      Now let's log in as the "has_role" user and select the "test_role" role:

      MariaDB [db1]> SELECT CURRENT_USER();
      +--------------------+
      | CURRENT_USER()     |
      +--------------------+
      | has_role@localhost |
      +--------------------+
      1 row in set (0.00 sec)
       
      MariaDB [db1]> SET ROLE test_role;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db1]> SELECT CURRENT_ROLE();
      +----------------+
      | CURRENT_ROLE() |
      +----------------+
      | test_role      |
      +----------------+
      1 row in set (0.00 sec)
      

      And let's see what happens if I query the view as this user:

      MariaDB [db1]> SELECT * FROM v_view_role_test;
      Empty set (0.00 sec)
      

      We got no results.

      But if we manually execute the query from the view, we do get results:

      MariaDB [db1]> SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
      +----+-----------+
      | id | role_name |
      +----+-----------+
      |  1 | test_role |
      +----+-----------+
      1 row in set (0.00 sec)
      

      It seems that CURRENT_ROLE() does not work in a view.

        Attachments

          Activity

            People

            • Assignee:
              cvicentiu Vicentiu Ciorbaru
              Reporter:
              GeoffMontee Geoff Montee
            • Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: