[MDEV-12666] CURRENT_ROLE() and DATABASE() does not work in a view Created: 2017-05-02  Updated: 2020-08-25  Resolved: 2017-06-15

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Views
Affects Version/s: 10.0, 10.1, 10.1.22, 10.2
Fix Version/s: 10.0.32

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 1
Labels: roles, view

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.

 Comments   
Comment by Elena Stepanova [ 2017-05-02 ]

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;

Comment by Vicențiu Ciorbaru [ 2017-05-22 ]

Hi Alexander!

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

Thanks!
Vicentiu

Comment by Vicențiu Ciorbaru [ 2017-05-22 ]

This patch is targeting 10.0.

Comment by Alexander Barkov [ 2017-05-23 ]

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.

Comment by Alexander Barkov [ 2017-05-23 ]

Sent review comments by email

Comment by Vicențiu Ciorbaru [ 2017-06-07 ]

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

Comment by Vicențiu Ciorbaru [ 2017-06-08 ]

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

Comment by Alexander Barkov [ 2017-06-13 ]

Ok to push.
Please find minor suggestions on email.

Generated at Thu Feb 08 07:59:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.