1# 2# MDEV-12666 CURRENT_ROLE() does not work in a view 3# 4--source include/not_embedded.inc 5 6CREATE USER has_role@'localhost'; 7GRANT ALL PRIVILEGES ON *.* TO has_role@'localhost'; 8 9CREATE ROLE test_role; 10GRANT test_role TO has_role@'localhost'; 11 12CREATE USER no_role@'localhost'; 13GRANT ALL PRIVILEGES ON *.* TO no_role@'localhost'; 14 15CREATE TABLE view_role_test ( 16 id int primary key, 17 role_name varchar(50) 18 ); 19 20INSERT INTO view_role_test VALUES (1, 'test_role'); 21 22--echo # 23--echo # Use the same logic for stored procedures. 24--echo # 25PREPARE prepared_no_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()"; 26 27--echo # 28--echo # Creating a view with no CURRENT_ROLE() set and one with CURRENT_ROLE() 29--echo # set. Both should produce the same SHOW CREATE VIEW output. 30--echo # 31CREATE 32DEFINER = no_role@localhost 33SQL SECURITY INVOKER 34VIEW v_view_role_test_no_current_role 35AS 36SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); 37 38SHOW CREATE VIEW v_view_role_test_no_current_role; 39 40 41--echo # 42--echo # No values should be returned 43--echo # 44EXECUTE prepared_no_current_role; 45SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); 46SELECT * FROM v_view_role_test_no_current_role; 47 48--echo # 49--echo # Now let's set the role. Create identical views as before. See if 50--echo # their behaviour is different. It should not be. 51--echo # 52SET ROLE test_role; 53 54SELECT CURRENT_USER(); 55SELECT CURRENT_ROLE(); 56 57--echo # 58--echo # Create the VIEW and prepared Statement with a CURRENT_ROLE() set. 59--echo # 60CREATE 61DEFINER = no_role@localhost 62SQL SECURITY INVOKER 63VIEW v_view_role_test_with_current_role 64AS 65SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); 66 67PREPARE prepared_with_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()"; 68 69SHOW CREATE VIEW v_view_role_test_with_current_role; 70 71 72--echo # 73--echo # Values should be returned for all select statements as we do have 74--echo # a CURRENT_ROLE() active; 75--echo # 76EXECUTE prepared_no_current_role; 77EXECUTE prepared_with_current_role; 78SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); 79SELECT * FROM v_view_role_test_no_current_role; 80SELECT * FROM v_view_role_test_with_current_role; 81 82SET ROLE NONE; 83--echo # 84--echo # No values should be returned for all select statements as we do not have 85--echo # a CURRENT_ROLE() active; 86--echo # 87EXECUTE prepared_no_current_role; 88EXECUTE prepared_with_current_role; 89SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE(); 90SELECT * FROM v_view_role_test_no_current_role; 91SELECT * FROM v_view_role_test_with_current_role; 92 93 94DROP USER has_role@'localhost'; 95DROP USER no_role@'localhost'; 96DROP ROLE test_role; 97 98DROP table view_role_test; 99DROP VIEW v_view_role_test_no_current_role; 100DROP VIEW v_view_role_test_with_current_role; 101DROP PREPARE prepared_no_current_role; 102DROP PREPARE prepared_with_current_role; 103