Debian Codegraph @

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

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