Debian Codegraph @

mysql-test/suite/funcs_1/r/is_schema_privileges.result

raw
1 SHOW TABLES FROM information_schema LIKE 'SCHEMA_PRIVILEGES';
2 Tables_in_information_schema (SCHEMA_PRIVILEGES)
3 SCHEMA_PRIVILEGES
4 #######################################################################
5 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6 #######################################################################
7 DROP VIEW IF EXISTS test.v1;
8 DROP PROCEDURE IF EXISTS test.p1;
9 DROP FUNCTION IF EXISTS test.f1;
10 CREATE VIEW test.v1 AS SELECT * FROM information_schema.SCHEMA_PRIVILEGES;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMA_PRIVILEGES;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.SCHEMA_PRIVILEGES;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.SCHEMA_PRIVILEGES;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.15.1: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES layout
28 #########################################################################
29 DESCRIBE information_schema.SCHEMA_PRIVILEGES;
30 Field Type Null Key Default Extra
31 GRANTEE varchar(385) NO NULL
32 TABLE_CATALOG varchar(512) NO NULL
33 TABLE_SCHEMA varchar(64) NO NULL
34 PRIVILEGE_TYPE varchar(64) NO NULL
35 IS_GRANTABLE varchar(3) NO NULL
36 SHOW CREATE TABLE information_schema.SCHEMA_PRIVILEGES;
37 Table Create Table
38 SCHEMA_PRIVILEGES CREATE TEMPORARY TABLE `SCHEMA_PRIVILEGES` (
39 `GRANTEE` varchar(385) NOT NULL,
40 `TABLE_CATALOG` varchar(512) NOT NULL,
41 `TABLE_SCHEMA` varchar(64) NOT NULL,
42 `PRIVILEGE_TYPE` varchar(64) NOT NULL,
43 `IS_GRANTABLE` varchar(3) NOT NULL
44 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
45 SHOW COLUMNS FROM information_schema.SCHEMA_PRIVILEGES;
46 Field Type Null Key Default Extra
47 GRANTEE varchar(385) NO NULL
48 TABLE_CATALOG varchar(512) NO NULL
49 TABLE_SCHEMA varchar(64) NO NULL
50 PRIVILEGE_TYPE varchar(64) NO NULL
51 IS_GRANTABLE varchar(3) NO NULL
52 SELECT GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE
53 FROM information_schema.schema_privileges WHERE table_catalog IS NOT NULL;
54 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE
55 ###############################################################################
56 # Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility
57 ###############################################################################
58 DROP DATABASE IF EXISTS db_datadict_1;
59 DROP DATABASE IF EXISTS db_datadict_2;
60 DROP DATABASE IF EXISTS db_datadict_3;
61 CREATE DATABASE db_datadict_1;
62 CREATE DATABASE db_datadict_2;
63 CREATE DATABASE db_datadict_3;
64 CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT)
65 ENGINE = MEMORY;
66 DROP USER 'testuser1'@'localhost';
67 CREATE USER 'testuser1'@'localhost';
68 DROP USER 'testuser2'@'localhost';
69 CREATE USER 'testuser2'@'localhost';
70 GRANT INSERT ON db_datadict_1.* TO 'testuser1'@'localhost';
71 GRANT INSERT ON db_datadict_2.t1 TO 'testuser1'@'localhost';
72 GRANT SELECT ON db_datadict_4.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
73 GRANT SELECT ON db_datadict_3.* TO 'testuser2'@'localhost';
74 GRANT SELECT ON db_datadict_1.* TO 'testuser2'@'localhost';
75 connect testuser1, localhost, testuser1, ,"*NO-ONE*";
76 GRANT SELECT ON db_datadict_4.* TO 'testuser2'@'localhost';
77 # Root granted INSERT db_datadict_1 to me -> visible
78 # Root granted SELECT db_datadict_1 to testuser2 -> invisible
79 # Root granted INSERT db_datadict_2.t1 (no schema-level priv!)
80 # but not db_datadict_2 to me -> invisible
81 # Root granted SELECT db_datadict_3. to testuser2 but not to me -> invisible
82 # Root granted SELECT db_datadict_4. to me -> visible
83 # I granted SELECT db_datadict_4. to testuser2 -> invisible (reality), visible(requirement)
84 # FIXME
85 SELECT * FROM information_schema.schema_privileges
86 WHERE table_schema LIKE 'db_datadict%'
87 ORDER BY grantee,table_schema,privilege_type;
88 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
89 'testuser1'@'localhost' def db_datadict_1 INSERT NO
90 'testuser1'@'localhost' def db_datadict_4 SELECT YES
91 SHOW GRANTS FOR 'testuser1'@'localhost';
92 Grants for testuser1@localhost
93 GRANT USAGE ON *.* TO `testuser1`@`localhost`
94 GRANT INSERT ON `db_datadict_1`.* TO `testuser1`@`localhost`
95 GRANT SELECT ON `db_datadict_4`.* TO `testuser1`@`localhost` WITH GRANT OPTION
96 GRANT INSERT ON `db_datadict_2`.`t1` TO `testuser1`@`localhost`
97 SHOW GRANTS FOR 'testuser2'@'localhost';
98 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'mysql'
99 connect testuser2, localhost, testuser2, ,"*NO-ONE*";
100 # Root granted SELECT db_datadict_1 to me -> visible
101 # Root granted INSERT db_datadict_1 to testuser1 -> invisible
102 # Root granted INSERT db_datadict_2.t1 but not db_datadict_1 to testuser1 -> invisible
103 # Root granted SELECT db_datadict_3. to me -> visible
104 # testuser1 granted SELECT db_datadict_4. to me -> visible
105 SELECT * FROM information_schema.schema_privileges
106 WHERE table_schema LIKE 'db_datadict%'
107 ORDER BY grantee,table_schema,privilege_type;
108 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
109 'testuser2'@'localhost' def db_datadict_1 SELECT NO
110 'testuser2'@'localhost' def db_datadict_3 SELECT NO
111 'testuser2'@'localhost' def db_datadict_4 SELECT NO
112 SHOW GRANTS FOR 'testuser1'@'localhost';
113 ERROR 42000: Access denied for user 'testuser2'@'localhost' to database 'mysql'
114 SHOW GRANTS FOR 'testuser2'@'localhost';
115 Grants for testuser2@localhost
116 GRANT USAGE ON *.* TO `testuser2`@`localhost`
117 GRANT SELECT ON `db_datadict_3`.* TO `testuser2`@`localhost`
118 GRANT SELECT ON `db_datadict_1`.* TO `testuser2`@`localhost`
119 GRANT SELECT ON `db_datadict_4`.* TO `testuser2`@`localhost`
120 connection default;
121 disconnect testuser1;
122 disconnect testuser2;
123 SELECT * FROM information_schema.schema_privileges
124 WHERE table_schema LIKE 'db_datadict%'
125 ORDER BY grantee,table_schema,privilege_type;
126 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
127 'testuser1'@'localhost' def db_datadict_1 INSERT NO
128 'testuser1'@'localhost' def db_datadict_4 SELECT YES
129 'testuser2'@'localhost' def db_datadict_1 SELECT NO
130 'testuser2'@'localhost' def db_datadict_3 SELECT NO
131 'testuser2'@'localhost' def db_datadict_4 SELECT NO
132 SHOW GRANTS FOR 'testuser1'@'localhost';
133 Grants for testuser1@localhost
134 GRANT USAGE ON *.* TO `testuser1`@`localhost`
135 GRANT INSERT ON `db_datadict_1`.* TO `testuser1`@`localhost`
136 GRANT SELECT ON `db_datadict_4`.* TO `testuser1`@`localhost` WITH GRANT OPTION
137 GRANT INSERT ON `db_datadict_2`.`t1` TO `testuser1`@`localhost`
138 SHOW GRANTS FOR 'testuser2'@'localhost';
139 Grants for testuser2@localhost
140 GRANT USAGE ON *.* TO `testuser2`@`localhost`
141 GRANT SELECT ON `db_datadict_3`.* TO `testuser2`@`localhost`
142 GRANT SELECT ON `db_datadict_1`.* TO `testuser2`@`localhost`
143 GRANT SELECT ON `db_datadict_4`.* TO `testuser2`@`localhost`
144 DROP USER 'testuser1'@'localhost';
145 DROP USER 'testuser2'@'localhost';
146 DROP DATABASE db_datadict_1;
147 DROP DATABASE db_datadict_2;
148 DROP DATABASE db_datadict_3;
149 ################################################################################
150 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES modifications
151 ################################################################################
152 DROP DATABASE IF EXISTS db_datadict;
153 CREATE DATABASE db_datadict;
154 DROP USER 'the_user'@'localhost';
155 DROP USER 'testuser1'@'localhost';
156 CREATE USER 'testuser1'@'localhost';
157 GRANT SELECT ON test.* TO 'testuser1'@'localhost';
158 SELECT * FROM information_schema.schema_privileges
159 WHERE table_schema = 'db_datadict'
160 ORDER BY grantee,table_schema,privilege_type;
161 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
162 connect testuser1, localhost, testuser1, , test;
163 SELECT * FROM information_schema.schema_privileges
164 WHERE table_schema = 'db_datadict'
165 ORDER BY grantee,table_schema,privilege_type;
166 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
167 connection default;
168 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost';
169 SELECT * FROM information_schema.schema_privileges
170 WHERE table_schema = 'db_datadict'
171 ORDER BY grantee,table_schema,privilege_type;
172 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
173 'testuser1'@'localhost' def db_datadict UPDATE NO
174 SELECT * FROM information_schema.schema_privileges
175 WHERE table_schema = 'db_datadict'
176 ORDER BY grantee,table_schema,privilege_type;
177 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
178 'testuser1'@'localhost' def db_datadict UPDATE NO
179 connection default;
180 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
181 SELECT * FROM information_schema.schema_privileges
182 WHERE table_schema = 'db_datadict'
183 ORDER BY grantee,table_schema,privilege_type;
184 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
185 'testuser1'@'localhost' def db_datadict SELECT NO
186 'testuser1'@'localhost' def db_datadict UPDATE NO
187 SELECT * FROM information_schema.schema_privileges
188 WHERE table_schema = 'db_datadict'
189 ORDER BY grantee,table_schema,privilege_type;
190 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
191 'testuser1'@'localhost' def db_datadict SELECT NO
192 'testuser1'@'localhost' def db_datadict UPDATE NO
193 # Switch to connection default
194 connection default;
195 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
196 SELECT * FROM information_schema.schema_privileges
197 WHERE table_schema = 'db_datadict'
198 ORDER BY grantee,table_schema,privilege_type;
199 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
200 'testuser1'@'localhost' def db_datadict SELECT YES
201 'testuser1'@'localhost' def db_datadict UPDATE YES
202 SELECT * FROM information_schema.schema_privileges
203 WHERE table_schema = 'db_datadict'
204 ORDER BY grantee,table_schema,privilege_type;
205 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
206 'testuser1'@'localhost' def db_datadict SELECT YES
207 'testuser1'@'localhost' def db_datadict UPDATE YES
208 connection default;
209 DROP SCHEMA db_datadict;
210 SELECT * FROM information_schema.schema_privileges
211 WHERE table_schema = 'db_datadict'
212 ORDER BY grantee,table_schema,privilege_type;
213 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
214 'testuser1'@'localhost' def db_datadict SELECT YES
215 'testuser1'@'localhost' def db_datadict UPDATE YES
216 SELECT * FROM information_schema.schema_privileges
217 WHERE table_schema = 'db_datadict'
218 ORDER BY grantee,table_schema,privilege_type;
219 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
220 'testuser1'@'localhost' def db_datadict SELECT YES
221 'testuser1'@'localhost' def db_datadict UPDATE YES
222 connection default;
223 REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost';
224 SELECT * FROM information_schema.schema_privileges
225 WHERE table_schema = 'db_datadict'
226 ORDER BY grantee,table_schema,privilege_type;
227 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
228 'testuser1'@'localhost' def db_datadict SELECT YES
229 SELECT * FROM information_schema.schema_privileges
230 WHERE table_schema = 'db_datadict'
231 ORDER BY grantee,table_schema,privilege_type;
232 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
233 'testuser1'@'localhost' def db_datadict SELECT YES
234 connection default;
235 RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
236 SELECT * FROM information_schema.schema_privileges
237 WHERE table_schema = 'db_datadict'
238 ORDER BY grantee,table_schema,privilege_type;
239 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
240 'the_user'@'localhost' def db_datadict SELECT YES
241 SELECT * FROM information_schema.schema_privileges
242 WHERE table_schema = 'db_datadict'
243 ORDER BY grantee,table_schema,privilege_type;
244 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
245 'the_user'@'localhost' def db_datadict SELECT YES
246 disconnect testuser1;
247 connect the_user, localhost, the_user, , test;
248 SELECT * FROM information_schema.schema_privileges
249 WHERE table_schema = 'db_datadict'
250 ORDER BY grantee,table_schema,privilege_type;
251 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
252 'the_user'@'localhost' def db_datadict SELECT YES
253 disconnect the_user;
254 connection default;
255 SELECT * FROM information_schema.schema_privileges
256 WHERE table_schema = 'db_datadict'
257 ORDER BY grantee,table_schema,privilege_type;
258 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
259 'the_user'@'localhost' def db_datadict SELECT YES
260 DROP USER 'the_user'@'localhost';
261 SELECT * FROM information_schema.schema_privileges
262 WHERE table_schema = 'db_datadict'
263 ORDER BY grantee,table_schema,privilege_type;
264 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
265 ########################################################################
266 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
267 # DDL on INFORMATION_SCHEMA table are not supported
268 ########################################################################
269 DROP DATABASE IF EXISTS db_datadict;
270 CREATE DATABASE db_datadict;
271 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
272 ENGINE = <engine_type>;
273 DROP USER 'testuser1'@'localhost';
274 CREATE USER 'testuser1'@'localhost';
275 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
276 INSERT INTO information_schema.schema_privileges
277 SELECT * FROM information_schema.schema_privileges;
278 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
279 UPDATE information_schema.schema_privileges SET table_schema = 'test'
280 WHERE table_name = 't1';
281 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
282 DELETE FROM information_schema.schema_privileges
283 WHERE table_schema = 'db_datadict';
284 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
285 TRUNCATE information_schema.schema_privileges;
286 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
287 CREATE INDEX my_idx_on_tables
288 ON information_schema.schema_privileges(table_schema);
289 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
290 ALTER TABLE information_schema.schema_privileges ADD f1 INT;
291 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
292 DROP TABLE information_schema.schema_privileges;
293 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
294 ALTER TABLE information_schema.schema_privileges
295 RENAME db_datadict.schema_privileges;
296 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
297 ALTER TABLE information_schema.schema_privileges
298 RENAME information_schema.xschema_privileges;
299 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
300 DROP DATABASE db_datadict;
301 DROP USER 'testuser1'@'localhost';