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.