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