Debian Codegraph @

mysql-test/suite/sys_vars/t/default_table_encryption_basic.test

raw
1
2 --echo # This file contains test cases to validate the behavior of system
3 --echo # variables 'default_table_encryption' with various permissions a user
4 --echo # can have.
5
6 CREATE USER u1@localhost;
7 GRANT ALL ON db1.* TO u1@localhost;
8 connect (con1, localhost, u1);
9 SELECT CURRENT_USER();
10 SHOW GRANTS FOR CURRENT_USER();
11
12 --echo #
13 --echo # VARIABLE USED BY NORMAL USER.
14 --echo #
15
16 --echo #
17 --echo # SET default_table_encryption
18 --echo #
19
20 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
21 SET SESSION default_table_encryption=true;
22
23 SET SESSION default_table_encryption=false;
24
25 SET SESSION default_table_encryption=DEFAULT;
26
27 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
28 SET GLOBAL default_table_encryption=true;
29
30 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
31 SET PERSIST default_table_encryption=true;
32
33 --error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
34 SET PERSIST_ONLY default_table_encryption=true;
35
36 --echo #
37 --echo # VARIABLE USED BY USER WITH TABLE_ENCRYPTION_ADMIN privilege.
38 --echo #
39
40 connection default;
41 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
42 connection con1;
43 SELECT CURRENT_USER();
44 SHOW GRANTS FOR CURRENT_USER();
45
46 --echo #
47 --echo # SET default_table_encryption
48 --echo #
49
50 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
51 SET SESSION default_table_encryption=true;
52
53 SET SESSION default_table_encryption=false;
54
55 SET SESSION default_table_encryption=DEFAULT;
56
57 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
58 SET GLOBAL default_table_encryption=true;
59
60 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
61 SET PERSIST default_table_encryption=true;
62
63 --error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
64 SET PERSIST_ONLY default_table_encryption=true;
65
66 --echo #
67 --echo # VARIABLE USED BY USER WITH SYSTEM_VARIABLES_ADMIN privilege.
68 --echo #
69
70 connection default;
71 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
72 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
73 connection con1;
74 SELECT CURRENT_USER();
75 SHOW GRANTS FOR CURRENT_USER();
76
77 --echo #
78 --echo # SET default_table_encryption
79 --echo #
80
81 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
82 SET SESSION default_table_encryption=true;
83
84 SET SESSION default_table_encryption=false;
85
86 SET SESSION default_table_encryption=DEFAULT;
87
88 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
89 SET GLOBAL default_table_encryption=true;
90
91 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
92 SET PERSIST default_table_encryption=true;
93
94 --error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
95 SET PERSIST_ONLY default_table_encryption=true;
96
97 --echo #
98 --echo # VARIABLE USED BY USER WITH BOTH TABLE_ENCRYPTION_ADMIN and
99 --echo # SYSTEM_VARIABLES_ADMIN privilege.
100 --echo #
101
102 connection default;
103 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
104 connection con1;
105 SELECT CURRENT_USER();
106 SHOW GRANTS FOR CURRENT_USER();
107
108 --echo #
109 --echo # SET default_table_encryption
110 --echo #
111
112 SET SESSION default_table_encryption=true;
113 SELECT @@session.default_table_encryption = true;
114
115 SET SESSION default_table_encryption=false;
116 SELECT @@session.default_table_encryption = false;
117
118 SET SESSION default_table_encryption=DEFAULT;
119 SELECT @@session.default_table_encryption = false;
120
121 SET GLOBAL default_table_encryption=false;
122 SELECT @@global.default_table_encryption = false;
123
124 SET PERSIST default_table_encryption=true;
125 SELECT @@global.default_table_encryption = true;
126
127 --error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
128 SET PERSIST_ONLY default_table_encryption=true;
129
130 --echo #
131 --echo # VARIABLE USED BY USER WITH PERSIST_RO_VARIABLES_ADMIN,
132 --echo # TABLE_ENCRYPTION_ADMIN && SYSTEM_VARIABLES_ADMIN privilege.
133 --echo #
134
135 connection default;
136 GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO u1@localhost;
137 connection con1;
138 SELECT CURRENT_USER();
139 SHOW GRANTS FOR CURRENT_USER();
140
141 --echo #
142 --echo # SET default_table_encryption
143 --echo #
144
145 SET SESSION default_table_encryption=true;
146 SELECT @@session.default_table_encryption = true;
147
148 SET GLOBAL default_table_encryption=false;
149 SELECT @@global.default_table_encryption = false;
150
151 SET PERSIST default_table_encryption=true;
152 SELECT @@global.default_table_encryption = true;
153
154 SET PERSIST_ONLY default_table_encryption=false;
155 SELECT @@global.default_table_encryption = true;
156
157 --echo #
158 --echo # Test changing the value to invalid value
159 --echo #
160
161 --Error ER_WRONG_VALUE_FOR_VAR
162 SET GLOBAL default_table_encryption = -1;
163 SELECT @@global.default_table_encryption;
164 --Error ER_WRONG_VALUE_FOR_VAR
165 SET GLOBAL default_table_encryption = 100000000000;
166 SELECT @@global.default_table_encryption;
167 --Error ER_WRONG_TYPE_FOR_VAR
168 SET GLOBAL default_table_encryption = 10000.01;
169 SELECT @@global.default_table_encryption;
170 --Error ER_WRONG_VALUE_FOR_VAR
171 SET @@global.default_table_encryption = 'test';
172 SELECT @@global.default_table_encryption;
173 --Error ER_WRONG_VALUE_FOR_VAR
174 SET @@global.default_table_encryption = ' ';
175 SELECT @@global.default_table_encryption;
176
177 --echo #
178 --echo # VARIABLE USED BY USER WITH SUPER privilege.
179 --echo #
180
181 connection default;
182 disconnect con1;
183 REVOKE TABLE_ENCRYPTION_ADMIN, SYSTEM_VARIABLES_ADMIN,
184 PERSIST_RO_VARIABLES_ADMIN ON *.* FROM u1@localhost;
185 GRANT SUPER ON *.* TO u1@localhost;
186 connect (con1, localhost, u1);
187 SELECT CURRENT_USER();
188 SHOW GRANTS FOR CURRENT_USER();
189
190 --echo #
191 --echo # SET default_table_encryption
192 --echo #
193
194 SET SESSION default_table_encryption=true;
195 SELECT @@session.default_table_encryption = true;
196
197 SET GLOBAL default_table_encryption=false;
198 SELECT @@global.default_table_encryption = false;
199
200 SET PERSIST default_table_encryption=true;
201 SELECT @@global.default_table_encryption = true;
202
203 --error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
204 SET PERSIST_ONLY default_table_encryption=true;
205
206 connection default;
207 disconnect con1;
208 REVOKE SUPER ON *.* FROM u1@localhost;
209 DROP USER u1@localhost;
210 RESET PERSIST;
211 SET GLOBAL default_table_encryption=default;