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