Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.22, 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
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.
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.
--echo # Should be NULL
--echo # Should be NULL
--echo # Should be 'test_role'
--echo # Should be true (but it's not)
--echo # Should be true
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;