Debian Codegraph @

mysql-test/suite/information_schema/t/applicable_roles.test

raw
1 # This test case test INFORMATION_SCHEMA.APPLICABLE_ROLES as well as
2 # INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS, because the later
3 # is just a view over INFORMATION_SCHEMA.APPLICABLE_ROLES listing only roles
4 # with IS_GRANTABLE='Yes'.
5
6 --echo # SETUP SCHEMA
7 --source suite/information_schema/include/roles_schema.inc
8
9 --echo #
10 --echo # USER WITH NO ROLES ASSIGNED.
11 --echo #
12 connect(con1, localhost, user0, foo, test);
13
14 --echo # List all APPLICABLE ROLES for user0
15 SELECT
16 USER, /* SQL Standard */
17 HOST, /* MySQL Specific */
18 GRANTEE, /* SQL Standard */
19 GRANTEE_HOST, /* MySQL Specific */
20 ROLE_NAME, /* SQL Standard */
21 ROLE_HOST, /* MySQL Specific */
22 IS_GRANTABLE, /* SQL Standard */
23 IS_DEFAULT, /* MySQL Specific */
24 IS_MANDATORY /* MySQL Specific */
25 FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
26 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS;
27
28 disconnect con1;
29 connection default;
30
31 --let $ORDER_BY= ORDER BY GRANTEE, ROLE_NAME
32
33 --echo # USER WITH ROLES.
34
35 --echo #
36 --echo # Case 1: Login as user1
37 --echo #
38 --let USER_NAME=user1
39 --let ROLE_LIST=hr_rules
40 --let $TEST_TABLE=APPLICABLE_ROLES
41 --source suite/information_schema/include/roles_i_s_test.inc
42 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
43 --source suite/information_schema/include/roles_i_s_test.inc
44
45 --echo #
46 --echo # Case 2: Login as user2
47 --echo #
48 --let USER_NAME=user2
49 --let ROLE_LIST=hr_rules
50 --let $TEST_TABLE=APPLICABLE_ROLES
51 --source suite/information_schema/include/roles_i_s_test.inc
52 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
53 --source suite/information_schema/include/roles_i_s_test.inc
54
55 --echo #
56 --echo # Case 3: Login as lead_user1 with mandatory roles.
57 --echo #
58
59 # Set role task2 as mandatory which is not directly granted to lead_user1
60 SET GLOBAL mandatory_roles = 'task2';
61 --let USER_NAME=lead_user1
62 --let ROLE_LIST=NONE
63 --let $TEST_TABLE=APPLICABLE_ROLES
64 --source suite/information_schema/include/roles_i_s_test.inc
65 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
66 --source suite/information_schema/include/roles_i_s_test.inc
67
68 # Set role hr_access as mandatory which is directly granted to lead_user1
69 SET GLOBAL mandatory_roles = 'hr_access';
70 --let ROLE_LIST=hr_access
71 --let $TEST_TABLE=APPLICABLE_ROLES
72 --source suite/information_schema/include/roles_i_s_test.inc
73 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
74 --source suite/information_schema/include/roles_i_s_test.inc
75
76 # Set role hr_access as mandatory and enable it upon connection.
77 SET GLOBAL activate_all_roles_on_login=on;
78 --let $TEST_TABLE=APPLICABLE_ROLES
79 --source suite/information_schema/include/roles_i_s_test.inc
80 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
81 --source suite/information_schema/include/roles_i_s_test.inc
82 SET GLOBAL activate_all_roles_on_login=off;
83
84 SET GLOBAL mandatory_roles = default;
85
86 --echo #
87 --echo # Case 4: Login as lead_user2 with default roles.
88 --echo #
89 SET DEFAULT ROLE hr_access TO lead_user2;
90 --let USER_NAME=lead_user2
91 --let ROLE_LIST=task2_lead
92 --let $TEST_TABLE=APPLICABLE_ROLES
93 --source suite/information_schema/include/roles_i_s_test.inc
94 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
95 --source suite/information_schema/include/roles_i_s_test.inc
96 SET DEFAULT ROLE NONE TO lead_user1;
97
98 --echo #
99 --echo # Case 5: Login as project_lead_user with role graph.
100 --echo #
101 --let USER_NAME=project_lead_user
102 --let ROLE_LIST=hr_access
103 --let $TEST_TABLE=APPLICABLE_ROLES
104 --source suite/information_schema/include/roles_i_s_test.inc
105 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
106 --source suite/information_schema/include/roles_i_s_test.inc
107
108 --echo #
109 --echo # Case 6: Login as project_lead_user with cyclic role graph.
110 --echo #
111 --echo # Form a role graph loop.
112 --echo #
113 --echo # reader
114 --echo # |
115 --echo # V
116 --echo # updater<------------\
117 --echo # | |
118 --echo # |``````````````| |
119 --echo # V V |
120 --echo # task1_lead task2_lead |
121 --echo # | | |
122 --echo # ````````|``````` |
123 --echo # V |
124 --echo # project_lead-----------/
125 --echo #
126 connection default;
127 # GRANT project_lead to updater;
128 INSERT INTO mysql.role_edges (FROM_USER,FROM_HOST,TO_USER,TO_HOST)
129 VALUES('project_lead','%','updater','%');
130 FLUSH PRIVILEGES;
131
132 --let USER_NAME=project_lead_user
133 --let ROLE_LIST=hr_access
134 --let $TEST_TABLE=APPLICABLE_ROLES
135 --source suite/information_schema/include/roles_i_s_test.inc
136 --let $TEST_TABLE=ADMINISTRABLE_ROLE_AUTHORIZATIONS
137 --source suite/information_schema/include/roles_i_s_test.inc
138
139 REVOKE project_lead FROM updater;
140
141 --echo #
142 --echo # Bug#30292102 WL10895 APPLICABLE_ROLES DISPLAYS
143 --echo # DUPLICATE ENTRY FOR ROLE
144 --echo #
145 CREATE USER u1 IDENTIFIED BY 'foo';
146 CREATE ROLE r1;
147 GRANT r1 to u1;
148 GRANT SELECT ON test.* TO u1;
149
150 SET GLOBAL mandatory_roles='r1';
151 GRANT r1 TO u1 WITH ADMIN OPTION;
152 SET GLOBAL activate_all_roles_on_login = 'ON';
153
154 connect(con1, localhost, u1, foo, test);
155 --echo # Should not show duplicate entry for r1->u1 edge after the fix.
156 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
157
158 connection default;
159 disconnect con1;
160 SET GLOBAL mandatory_roles=default;
161 SET GLOBAL activate_all_roles_on_login=default;
162 DROP USER u1;
163 DROP ROLE r1;
164
165 --echo # Cleanup
166 --source suite/information_schema/include/roles_schema_clean.inc