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

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

Details

    • 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

          elenst Elena Stepanova added a comment - - edited

          Here is a somewhat simpler version.
          There are two symmetrical views, one selects CURRENT_ROLE() and another one selects CURRENT_ROLE() = 'test_role'. The first one works as expected, the second one does not.

          CREATE ROLE test_role;
          CREATE OR REPLACE SQL SECURITY INVOKER VIEW v1 AS SELECT CURRENT_ROLE() AS r;
          CREATE OR REPLACE SQL SECURITY INVOKER VIEW v2 AS SELECT CURRENT_ROLE() = 'test_role';
           
          --echo # Should be NULL
          SELECT * FROM v1;
          --echo # Should be NULL
          SELECT * FROM v2;
           
          SET ROLE test_role;
           
          --echo # Should be 'test_role'
          SELECT * FROM v1;
          --echo # Should be true (but it's not)
          SELECT * FROM v2;
           
          --echo # Should be true
          SELECT r = 'test_role' FROM v1;
           
          DROP VIEW v1;
          DROP VIEW v2;
          DROP ROLE test_role;
          

          Actual result

          CREATE ROLE test_role;
          CREATE OR REPLACE SQL SECURITY INVOKER VIEW v1 AS SELECT CURRENT_ROLE() AS r;
          CREATE OR REPLACE SQL SECURITY INVOKER VIEW v2 AS SELECT CURRENT_ROLE() = 'test_role';
          # Should be NULL
          SELECT * FROM v1;
          r
          NULL
          # Should be NULL
          SELECT * FROM v2;
          CURRENT_ROLE() = 'test_role'
          NULL
          SET ROLE test_role;
          # Should be 'test_role'
          SELECT * FROM v1;
          r
          test_role
          # Should be true (but it's not)
          SELECT * FROM v2;
          CURRENT_ROLE() = 'test_role'
          NULL
          # Should be true
          SELECT r = 'test_role' FROM v1;
          r = 'test_role'
          1
          DROP VIEW v1;
          DROP VIEW v2;
          DROP ROLE test_role;
          

          elenst Elena Stepanova added a comment - - edited Here is a somewhat simpler version. There are two symmetrical views, one selects CURRENT_ROLE() and another one selects CURRENT_ROLE() = 'test_role' . The first one works as expected, the second one does not. CREATE ROLE test_role; CREATE OR REPLACE SQL SECURITY INVOKER VIEW v1 AS SELECT CURRENT_ROLE() AS r; CREATE OR REPLACE SQL SECURITY INVOKER VIEW v2 AS SELECT CURRENT_ROLE() = 'test_role' ;   --echo # Should be NULL SELECT * FROM v1; --echo # Should be NULL SELECT * FROM v2;   SET ROLE test_role;   --echo # Should be 'test_role' SELECT * FROM v1; --echo # Should be true (but it's not) SELECT * FROM v2;   --echo # Should be true SELECT r = 'test_role' FROM v1;   DROP VIEW v1; DROP VIEW v2; DROP ROLE test_role; Actual result CREATE ROLE test_role; CREATE OR REPLACE SQL SECURITY INVOKER VIEW v1 AS SELECT CURRENT_ROLE() AS r; CREATE OR REPLACE SQL SECURITY INVOKER VIEW v2 AS SELECT CURRENT_ROLE() = 'test_role'; # Should be NULL SELECT * FROM v1; r NULL # Should be NULL SELECT * FROM v2; CURRENT_ROLE() = 'test_role' NULL SET ROLE test_role; # Should be 'test_role' SELECT * FROM v1; r test_role # Should be true (but it's not) SELECT * FROM v2; CURRENT_ROLE() = 'test_role' NULL # Should be true SELECT r = 'test_role' FROM v1; r = 'test_role' 1 DROP VIEW v1; DROP VIEW v2; DROP ROLE test_role;

          Hi Alexander!

          Can you please review a patch for this issue?
          http://lists.askmonty.org/pipermail/commits/2017-May/011191.html

          Thanks!
          Vicentiu

          cvicentiu Vicențiu Ciorbaru added a comment - Hi Alexander! Can you please review a patch for this issue? http://lists.askmonty.org/pipermail/commits/2017-May/011191.html Thanks! Vicentiu

          This patch is targeting 10.0.

          cvicentiu Vicențiu Ciorbaru added a comment - This patch is targeting 10.0.
          bar Alexander Barkov added a comment - - edited

          A similar problem is repeatable with DATABASE().

          I start mysql client without specifying a database name, and run this script:

          CREATE OR REPLACE VIEW test.v1 AS SELECT DATABASE()='test';
          SHOW CREATE VIEW test.v1;
          

          +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
          | View | Create View                                                                                                                              | character_set_client | collation_connection |
          +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
          | v1   | CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select (NULL = 'test') AS `DATABASE()='test'` | utf8                 | utf8_general_ci      |
          +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------------------
          

          Notice, DATABASE() was replaced to NULL.

          bar Alexander Barkov added a comment - - edited A similar problem is repeatable with DATABASE() . I start mysql client without specifying a database name, and run this script: CREATE OR REPLACE VIEW test.v1 AS SELECT DATABASE ()= 'test' ; SHOW CREATE VIEW test.v1; +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select (NULL = 'test') AS `DATABASE()='test'` | utf8 | utf8_general_ci | +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+--------------------- Notice, DATABASE() was replaced to NULL .

          Sent review comments by email

          bar Alexander Barkov added a comment - Sent review comments by email

          Hi Alexander!

          Thank you for your first review. I've looked into this problem some more and I've come up with a correct solution I believe. I hope the commit comment is sufficient to explain everything. Can you please review it again? I've taken inspiration from Item_param::safe_charset_converter.

          http://lists.askmonty.org/pipermail/commits/2017-June/011229.html

          Thank you!
          Vicențiu

          cvicentiu Vicențiu Ciorbaru added a comment - Hi Alexander! Thank you for your first review. I've looked into this problem some more and I've come up with a correct solution I believe. I hope the commit comment is sufficient to explain everything. Can you please review it again? I've taken inspiration from Item_param::safe_charset_converter. http://lists.askmonty.org/pipermail/commits/2017-June/011229.html Thank you! Vicențiu

          Hi Alexander,

          Please use this commit for review. I've removed a few superfluous changes to keep the patch a bit smaller.

          http://lists.askmonty.org/pipermail/commits/2017-June/011238.html

          Vicentiu

          cvicentiu Vicențiu Ciorbaru added a comment - Hi Alexander, Please use this commit for review. I've removed a few superfluous changes to keep the patch a bit smaller. http://lists.askmonty.org/pipermail/commits/2017-June/011238.html Vicentiu

          Ok to push.
          Please find minor suggestions on email.

          bar Alexander Barkov added a comment - Ok to push. Please find minor suggestions on email.

          People

            cvicentiu Vicențiu Ciorbaru
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            7 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.