Debian Codegraph @

mysql-test/suite/perfschema/t/processlist_acl.test

raw
1 --echo ##
2 --echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
3 --echo ##
4 --echo ## Verify handling of the SELECT and PROCESS privileges.
5 --echo ##
6 --echo ## Test cases:
7 --echo ## - Execute SHOW PROCESSLIST (new and legacy) with all privileges
8 --echo ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with all privileges
9 --echo ## - Execute SHOW PROCESSLIST (new and legacy) with no privileges
10 --echo ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with no privileges
11 --echo ##
12 --echo ## Results must be manually verified.
13 --echo
14
15 --source include/no_protocol.inc
16 --source include/not_embedded.inc
17
18 --echo ### Setup ###
19 --echo
20 select @@global.performance_schema_show_processlist into @save_processlist;
21
22 --echo
23 --echo # Control users
24 create user user_00@localhost, user_01@localhost;
25 grant ALL on *.* to user_00@localhost;
26 grant ALL on *.* to user_01@localhost;
27
28 --echo
29 --echo # Test users
30 create user user_all@localhost, user_none@localhost;
31 grant ALL on *.* to user_all@localhost;
32 grant USAGE on *.* to user_none@localhost;
33 --echo
34 flush privileges;
35
36 --echo
37 show grants for user_all@localhost;
38 --echo
39 show grants for user_none@localhost;
40
41 --echo
42 use test;
43 create table test.t1 (s1 int, s2 int, s3 int, s4 int);
44
45 --echo
46 --echo # Connect (con_00, localhost, user_00, , )
47 connect (con_00, localhost, user_00, , );
48
49 --echo # Connect (con_01, localhost, user_01, , )
50 connect (con_01, localhost, user_01, , );
51 --echo
52 insert into test.t1 values(1, 1, 1, 1);
53 insert into test.t1 values(2, 2, 2, 2);
54 insert into test.t1 values(3, 3, 3, 3);
55 insert into test.t1 values(4, 4, 4, 4);
56
57 --echo
58 --echo # Lock test.t1, insert/update/deletes will block
59 lock tables t1 read;
60
61 --echo
62 --echo # Establish 2 connections for user_all
63 --echo # Connect (con_all_1, localhost, user_all, , )
64 connect (con_all_1, localhost, user_all, , );
65
66 --echo # Connect (con_all_2, localhost, user_all, , )
67 connect (con_all_2, localhost, user_all, , );
68 --send insert into test.t1 values (0, 0, 0, 0)
69
70 --echo
71 --echo # Establish 4 connections for user_none
72 --echo # Connect (con_none_1, localhost, user_none, , )
73 connect (con_none_1, localhost, user_none, , );
74
75 --echo # Connect (con_none_2, localhost, user_none, , )
76 connect (con_none_2, localhost, user_none, , );
77
78 --echo # Connect (con_none_3, localhost, user_none, , )
79 connect (con_none_3, localhost, user_none, , );
80
81 --echo # Connect (con_none_4, localhost, user_none, , )
82 connect (con_none_4, localhost, user_none, , );
83 --send update test.t1 set s1 = s1 + 1, s2 = s2 + 2;
84
85 --echo
86 --echo # Connection con_all_1
87 --connection con_all_1
88
89 # Note: When expecting processlist results for all users, the wait_condition
90 # and subsequent execution of SHOW PROCESSLIST should be conducted from the
91 # same connection to ensure consistent values in the Command and Info columns.
92
93 --echo
94 --echo # Wait for queries to appear in the processlist table
95 let $wait_condition = select count(*) >= 2 from information_schema.processlist
96 where state like '%metadata%';
97 --source include/wait_condition.inc
98
99 --echo
100 --echo ### Execute SHOW PROCESSLIST with all privileges
101 --echo ### Expect all users
102 --echo
103 --echo # New SHOW PROCESSLIST
104 let $pfs_spl = on;
105 --source ../include/processlist_set.inc
106 --source ../include/processlist_load.inc
107
108 --echo
109 --echo # Legacy SHOW PROCESSLIST
110 let $pfs_spl = off;
111 --source ../include/processlist_set.inc
112 --source ../include/processlist_load.inc
113
114 --echo
115 --echo
116 --echo ### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges
117 --echo ### Expect processes only from user_none
118 --echo
119 --echo # New SHOW PROCESSLIST
120 let $pfs_spl = on;
121 --source ../include/processlist_set.inc
122
123 --echo
124 --echo # Connection con_none_1
125 --connection con_none_1
126 --source ../include/processlist_load.inc
127
128 --echo
129 --echo # Confirm that only processes from user_none are visible
130 --echo
131 select count(*) as "Expect 0" from performance_schema.processlist
132 where user not in ('user_none');
133
134 --echo
135 --echo # Legacy SHOW PROCESSLIST
136 --connection con_00
137 let $pfs_spl = off;
138 --source ../include/processlist_set.inc
139
140 --echo
141 --echo # Connection con_none_1
142 --connection con_none_1
143 --source ../include/processlist_load.inc
144
145 --echo
146 --echo
147 --echo ### Clean up ###
148 --echo
149
150 --echo # Disconnect con_00
151 --connection con_00
152 --disconnect con_00
153
154 --echo # Connection con_01, unlock test.t1, disconnect
155 --connection con_01
156 unlock tables;
157 --disconnect con_01
158
159 --echo # Disconnect con_all_1
160 --connection con_all_1
161 --disconnect con_all_1
162
163 --echo # Reap con_all_2, disconnect
164 --connection con_all_2
165 --reap
166 --disconnect con_all_2
167
168 --echo # Disconnect con_none_1
169 --connection con_none_1
170 --disconnect con_none_1
171
172 --echo # Disconnect con_none_2
173 --connection con_none_2
174 --disconnect con_none_2
175
176 --echo # Disconnect con_none_3
177 --connection con_none_3
178 --disconnect con_none_3
179
180 --echo # Reap con_none_4, disconnect
181 --connection con_none_4
182 --reap
183 --disconnect con_none_4
184
185 --echo
186 --echo # Connection default
187 --connection default
188
189 --echo
190 drop table test.t1;
191 drop user user_00@localhost;
192 drop user user_01@localhost;
193 drop user user_all@localhost;
194 drop user user_none@localhost;
195 --echo
196 set @@global.performance_schema_show_processlist = @save_processlist;