Debian Codegraph @

mysql-test/suite/roles/current_role_view-12666.test

raw
1 #
2 # MDEV-12666 CURRENT_ROLE() does not work in a view
3 #
4 --source include/not_embedded.inc
5
6 CREATE USER has_role@'localhost';
7 GRANT ALL PRIVILEGES ON *.* TO has_role@'localhost';
8
9 CREATE ROLE test_role;
10 GRANT test_role TO has_role@'localhost';
11
12 CREATE USER no_role@'localhost';
13 GRANT ALL PRIVILEGES ON *.* TO no_role@'localhost';
14
15 CREATE TABLE view_role_test (
16 id int primary key,
17 role_name varchar(50)
18 );
19
20 INSERT INTO view_role_test VALUES (1, 'test_role');
21
22 --echo #
23 --echo # Use the same logic for stored procedures.
24 --echo #
25 PREPARE 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 #
31 CREATE
32 DEFINER = no_role@localhost
33 SQL SECURITY INVOKER
34 VIEW v_view_role_test_no_current_role
35 AS
36 SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
37
38 SHOW CREATE VIEW v_view_role_test_no_current_role;
39
40
41 --echo #
42 --echo # No values should be returned
43 --echo #
44 EXECUTE prepared_no_current_role;
45 SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
46 SELECT * 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 #
52 SET ROLE test_role;
53
54 SELECT CURRENT_USER();
55 SELECT CURRENT_ROLE();
56
57 --echo #
58 --echo # Create the VIEW and prepared Statement with a CURRENT_ROLE() set.
59 --echo #
60 CREATE
61 DEFINER = no_role@localhost
62 SQL SECURITY INVOKER
63 VIEW v_view_role_test_with_current_role
64 AS
65 SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
66
67 PREPARE prepared_with_current_role FROM "SELECT * from view_role_test WHERE role_name = CURRENT_ROLE()";
68
69 SHOW 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 #
76 EXECUTE prepared_no_current_role;
77 EXECUTE prepared_with_current_role;
78 SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
79 SELECT * FROM v_view_role_test_no_current_role;
80 SELECT * FROM v_view_role_test_with_current_role;
81
82 SET 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 #
87 EXECUTE prepared_no_current_role;
88 EXECUTE prepared_with_current_role;
89 SELECT * FROM view_role_test WHERE role_name = CURRENT_ROLE();
90 SELECT * FROM v_view_role_test_no_current_role;
91 SELECT * FROM v_view_role_test_with_current_role;
92
93
94 DROP USER has_role@'localhost';
95 DROP USER no_role@'localhost';
96 DROP ROLE test_role;
97
98 DROP table view_role_test;
99 DROP VIEW v_view_role_test_no_current_role;
100 DROP VIEW v_view_role_test_with_current_role;
101 DROP PREPARE prepared_no_current_role;
102 DROP PREPARE prepared_with_current_role;