Debian Codegraph @

mysql-test/suite/sys_vars/r/default_table_encryption_basic.result

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