mirror of
https://github.com/MariaDB/server.git
synced 2025-01-15 19:42:28 +01:00
f0ad93403f
The problem lies in how CURRENT_ROLE is defined. The
Item_func_current_role inherits from Item_func_sysconst, which defines
a safe_charset_converter to be a const_charset_converter.
During view creation, if there is no role previously set, the current_role()
function returns NULL.
This is captured on item instantiation and the
const_charset_converter call subsequently returns an Item_null.
In turn, the function is replaced with Item_null and the view is
then created with an Item_null instead of Item_func_current_role.
Without this patch, the first SHOW CREATE VIEW from the testcase would
have a where clause of WHERE role_name = NULL, while the second SHOW
CREATE VIEW would show a correctly created view.
The same applies for the DATABASE function, as it can change as well.
There is an additional problem with CURRENT_ROLE() when used in a
prepared statement. During prepared statement creation we used to set
the string_value of the function to the current role as well as the
null_value flag. During execution, if CURRENT_ROLE was not null, the
null_value flag was never set to not-null during fix_fields.
Item_func_current_user however can never be NULL so it did not show this
problem in a view before. At the same time, the CURRENT_USER() can not
be changed between prepared statement execution and creation so the
implementation where the value is stored during fix_fields is
sufficient.
Note also that DATABASE() function behaves differently during prepared
statements. See bug 25843 for details or commit
7e0ad09edf
102 lines
2.8 KiB
Text
102 lines
2.8 KiB
Text
#
|
|
# MDEV-12666 CURRENT_ROLE() does not work in a view
|
|
#
|
|
--source include/not_embedded.inc
|
|
|
|
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';
|
|
|
|
CREATE TABLE view_role_test (
|
|
id int primary key,
|
|
role_name varchar(50)
|
|
);
|
|
|
|
INSERT INTO view_role_test VALUES (1, 'test_role');
|
|
|
|
--echo #
|
|
--echo # Use the same logic for stored procedures.
|
|
--echo #
|
|
PREPARE prepared_no_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()";
|
|
|
|
--echo #
|
|
--echo # Creating a view with no CURRENT_ROLE() set and one with CURRENT_ROLE()
|
|
--echo # set. Both should produce the same SHOW CREATE VIEW output.
|
|
--echo #
|
|
CREATE
|
|
DEFINER = no_role@localhost
|
|
SQL SECURITY INVOKER
|
|
VIEW v_view_role_test_no_current_role
|
|
AS
|
|
SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
|
|
|
|
SHOW CREATE VIEW v_view_role_test_no_current_role;
|
|
|
|
|
|
--echo #
|
|
--echo # No values should be returned
|
|
--echo #
|
|
EXECUTE prepared_no_current_role;
|
|
SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
|
|
SELECT * FROM v_view_role_test_no_current_role;
|
|
|
|
--echo #
|
|
--echo # Now let's set the role. Create identical views as before. See if
|
|
--echo # their behaviour is different. It should not be.
|
|
--echo #
|
|
SET ROLE test_role;
|
|
|
|
SELECT CURRENT_USER();
|
|
SELECT CURRENT_ROLE();
|
|
|
|
--echo #
|
|
--echo # Create the VIEW and prepared Statement with a CURRENT_ROLE() set.
|
|
--echo #
|
|
CREATE
|
|
DEFINER = no_role@localhost
|
|
SQL SECURITY INVOKER
|
|
VIEW v_view_role_test_with_current_role
|
|
AS
|
|
SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
|
|
|
|
PREPARE prepared_with_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()";
|
|
|
|
SHOW CREATE VIEW v_view_role_test_with_current_role;
|
|
|
|
|
|
--echo #
|
|
--echo # Values should be returned for all select statements as we do have
|
|
--echo # a CURRENT_ROLE() active;
|
|
--echo #
|
|
EXECUTE prepared_no_current_role;
|
|
EXECUTE prepared_with_current_role;
|
|
SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
|
|
SELECT * FROM v_view_role_test_no_current_role;
|
|
SELECT * FROM v_view_role_test_with_current_role;
|
|
|
|
SET ROLE NONE;
|
|
--echo #
|
|
--echo # No values should be returned for all select statements as we do not have
|
|
--echo # a CURRENT_ROLE() active;
|
|
--echo #
|
|
EXECUTE prepared_no_current_role;
|
|
EXECUTE prepared_with_current_role;
|
|
SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
|
|
SELECT * FROM v_view_role_test_no_current_role;
|
|
SELECT * FROM v_view_role_test_with_current_role;
|
|
|
|
|
|
DROP USER has_role@'localhost';
|
|
DROP USER no_role@'localhost';
|
|
DROP ROLE test_role;
|
|
|
|
DROP table view_role_test;
|
|
DROP VIEW v_view_role_test_no_current_role;
|
|
DROP VIEW v_view_role_test_with_current_role;
|
|
DROP PREPARE prepared_no_current_role;
|
|
DROP PREPARE prepared_with_current_role;
|