Debian Codegraph @

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

raw
1 # This file contains test cases to validate the behavior of system
2 # variable 'table_encryption_privilege_check' with various
3 # permissions a user --echo # 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 table_encryption_privilege_check
18 #
19 SET SESSION table_encryption_privilege_check=true;
20 ERROR HY000: Variable 'table_encryption_privilege_check' is a GLOBAL variable and should be set with SET GLOBAL
21 SET GLOBAL table_encryption_privilege_check=true;
22 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
23 SET PERSIST table_encryption_privilege_check=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_ONLY table_encryption_privilege_check=true;
26 ERROR 42000: Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation
27 #
28 # VARIABLE USED BY USER WITH TABLE_ENCRYPTION_ADMIN privilege.
29 #
30 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
31 SELECT CURRENT_USER();
32 CURRENT_USER()
33 u1@localhost
34 SHOW GRANTS FOR CURRENT_USER();
35 Grants for u1@localhost
36 GRANT USAGE ON *.* TO `u1`@`localhost`
37 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO `u1`@`localhost`
38 GRANT ALL PRIVILEGES ON `db1`.* TO `u1`@`localhost`
39 #
40 # SET table_encryption_privilege_check
41 #
42 SET GLOBAL table_encryption_privilege_check=true;
43 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
44 SET PERSIST table_encryption_privilege_check=true;
45 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
46 SET PERSIST_ONLY table_encryption_privilege_check=true;
47 ERROR 42000: Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation
48 #
49 # VARIABLE USED BY USER WITH SYSTEM_VARIABLES_ADMIN privilege.
50 #
51 REVOKE TABLE_ENCRYPTION_ADMIN ON *.* FROM u1@localhost;
52 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
53 SELECT CURRENT_USER();
54 CURRENT_USER()
55 u1@localhost
56 SHOW GRANTS FOR CURRENT_USER();
57 Grants for u1@localhost
58 GRANT USAGE ON *.* TO `u1`@`localhost`
59 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO `u1`@`localhost`
60 GRANT ALL PRIVILEGES ON `db1`.* TO `u1`@`localhost`
61 #
62 # SET table_encryption_privilege_check
63 #
64 SET GLOBAL table_encryption_privilege_check=true;
65 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
66 SET PERSIST table_encryption_privilege_check=true;
67 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
68 SET PERSIST_ONLY table_encryption_privilege_check=true;
69 ERROR 42000: Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation
70 #
71 # VARIABLE USED BY USER WITH BOTH TABLE_ENCRYPTION_ADMIN and
72 # SYSTEM_VARIABLES_ADMIN privilege.
73 #
74 GRANT TABLE_ENCRYPTION_ADMIN ON *.* TO u1@localhost;
75 SELECT CURRENT_USER();
76 CURRENT_USER()
77 u1@localhost
78 SHOW GRANTS FOR CURRENT_USER();
79 Grants for u1@localhost
80 GRANT USAGE ON *.* TO `u1`@`localhost`
81 GRANT SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN ON *.* TO `u1`@`localhost`
82 GRANT ALL PRIVILEGES ON `db1`.* TO `u1`@`localhost`
83 #
84 # SET table_encryption_privilege_check
85 #
86 SET GLOBAL table_encryption_privilege_check=true;
87 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
88 SET PERSIST table_encryption_privilege_check=true;
89 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
90 SET PERSIST_ONLY table_encryption_privilege_check=true;
91 ERROR 42000: Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation
92 #
93 # VARIABLE USED BY USER WITH PERSIST_RO_VARIABLES_ADMIN,
94 # TABLE_ENCRYPTION_ADMIN && SYSTEM_VARIABLES_ADMIN privilege.
95 #
96 GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO u1@localhost;
97 SELECT CURRENT_USER();
98 CURRENT_USER()
99 u1@localhost
100 SHOW GRANTS FOR CURRENT_USER();
101 Grants for u1@localhost
102 GRANT USAGE ON *.* TO `u1`@`localhost`
103 GRANT PERSIST_RO_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN ON *.* TO `u1`@`localhost`
104 GRANT ALL PRIVILEGES ON `db1`.* TO `u1`@`localhost`
105 #
106 # SET table_encryption_privilege_check
107 #
108 SET GLOBAL table_encryption_privilege_check=true;
109 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
110 SET PERSIST table_encryption_privilege_check=true;
111 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
112 SET PERSIST_ONLY table_encryption_privilege_check=true;
113 SELECT @@global.table_encryption_privilege_check = false;
114 @@global.table_encryption_privilege_check = false
115 1
116 #
117 # VARIABLE USED BY USER WITH SUPER privilege.
118 #
119 REVOKE TABLE_ENCRYPTION_ADMIN, SYSTEM_VARIABLES_ADMIN,
120 PERSIST_RO_VARIABLES_ADMIN ON *.* FROM u1@localhost;
121 GRANT SUPER ON *.* TO u1@localhost;
122 Warnings:
123 Warning 1287 The SUPER privilege identifier is deprecated
124 SELECT CURRENT_USER();
125 CURRENT_USER()
126 u1@localhost
127 SHOW GRANTS FOR CURRENT_USER();
128 Grants for u1@localhost
129 GRANT SUPER ON *.* TO `u1`@`localhost`
130 GRANT ALL PRIVILEGES ON `db1`.* TO `u1`@`localhost`
131 #
132 # SET table_encryption_privilege_check
133 #
134 SET GLOBAL table_encryption_privilege_check=false;
135 SELECT @@global.table_encryption_privilege_check = false;
136 @@global.table_encryption_privilege_check = false
137 1
138 SET PERSIST table_encryption_privilege_check=true;
139 SELECT @@global.table_encryption_privilege_check = true;
140 @@global.table_encryption_privilege_check = true
141 1
142 SET PERSIST_ONLY table_encryption_privilege_check=true;
143 ERROR 42000: Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation
144 REVOKE SUPER ON *.* FROM u1@localhost;
145 Warnings:
146 Warning 1287 The SUPER privilege identifier is deprecated
147 DROP USER u1@localhost;
148 RESET PERSIST;
149 SET GLOBAL table_encryption_privilege_check=default;