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(292) NO
32 TABLE_CATALOG varchar(512) NO
33 TABLE_SCHEMA varchar(64) NO
34 PRIVILEGE_TYPE varchar(64) NO
35 IS_GRANTABLE varchar(3) NO
36 SHOW CREATE TABLE information_schema.SCHEMA_PRIVILEGES;
37 Table Create Table
38 SCHEMA_PRIVILEGES CREATE TEMPORARY TABLE `SCHEMA_PRIVILEGES` (
39 `GRANTEE` varchar(292) NOT NULL DEFAULT '',
40 `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
41 `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
42 `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
43 `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
44 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
45 SHOW COLUMNS FROM information_schema.SCHEMA_PRIVILEGES;
46 Field Type Null Key Default Extra
47 GRANTEE varchar(292) NO
48 TABLE_CATALOG varchar(512) NO
49 TABLE_SCHEMA varchar(64) NO
50 PRIVILEGE_TYPE varchar(64) NO
51 IS_GRANTABLE varchar(3) NO
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 'mysql.sys'@'localhost' def sys TRIGGER
56 'mysql.session'@'localhost' def performance_schema SELECT
57 ''@'%' def test SELECT
58 ''@'%' def test INSERT
59 ''@'%' def test UPDATE
60 ''@'%' def test DELETE
61 ''@'%' def test CREATE
62 ''@'%' def test DROP
63 ''@'%' def test REFERENCES
64 ''@'%' def test INDEX
65 ''@'%' def test ALTER
66 ''@'%' def test CREATE TEMPORARY TABLES
67 ''@'%' def test LOCK TABLES
68 ''@'%' def test CREATE VIEW
69 ''@'%' def test SHOW VIEW
70 ''@'%' def test CREATE ROUTINE
71 ''@'%' def test EVENT
72 ''@'%' def test TRIGGER
73 ###############################################################################
74 # Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility
75 ###############################################################################
76 DROP DATABASE IF EXISTS db_datadict_1;
77 DROP DATABASE IF EXISTS db_datadict_2;
78 DROP DATABASE IF EXISTS db_datadict_3;
79 CREATE DATABASE db_datadict_1;
80 CREATE DATABASE db_datadict_2;
81 CREATE DATABASE db_datadict_3;
82 CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT)
83 ENGINE = <engine_type>;
84 DROP USER 'testuser1'@'localhost';
85 CREATE USER 'testuser1'@'localhost';
86 DROP USER 'testuser2'@'localhost';
87 CREATE USER 'testuser2'@'localhost';
88 GRANT INSERT ON db_datadict_1.* TO 'testuser1'@'localhost';
89 GRANT INSERT ON db_datadict_2.t1 TO 'testuser1'@'localhost';
90 GRANT SELECT ON db_datadict_4.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
91 GRANT SELECT ON db_datadict_3.* TO 'testuser2'@'localhost';
92 GRANT SELECT ON db_datadict_1.* TO 'testuser2'@'localhost';
93 # Establish connection testuser1 (user=testuser1)
94 GRANT SELECT ON db_datadict_4.* TO 'testuser2'@'localhost';
95 # Root granted INSERT db_datadict_1 to me -> visible
96 # Root granted SELECT db_datadict_1 to testuser2 -> invisible
97 # Root granted INSERT db_datadict_2.t1 (no schema-level priv!)
98 # but not db_datadict_2 to me -> invisible
99 # Root granted SELECT db_datadict_3. to testuser2 but not to me -> invisible
100 # Root granted SELECT db_datadict_4. to me -> visible
101 # I granted SELECT db_datadict_4. to testuser2 -> invisible (reality), visible(requirement)
102 # FIXME
103 SELECT * FROM information_schema.schema_privileges
104 WHERE table_schema LIKE 'db_datadict%'
105 ORDER BY grantee,table_schema,privilege_type;
106 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
107 'testuser1'@'localhost' def db_datadict_1 INSERT NO
108 'testuser1'@'localhost' def db_datadict_4 SELECT YES
109 SHOW GRANTS FOR 'testuser1'@'localhost';
110 Grants for testuser1@localhost
111 GRANT USAGE ON *.* TO `testuser1`@`localhost`
112 GRANT INSERT ON `db_datadict_1`.* TO `testuser1`@`localhost`
113 GRANT SELECT ON `db_datadict_4`.* TO `testuser1`@`localhost` WITH GRANT OPTION
114 GRANT INSERT ON `db_datadict_2`.`t1` TO `testuser1`@`localhost`
115 SHOW GRANTS FOR 'testuser2'@'localhost';
116 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
117 # Establish connection testuser2 (user=testuser2)
118 # Root granted SELECT db_datadict_1 to me -> visible
119 # Root granted INSERT db_datadict_1 to testuser1 -> invisible
120 # Root granted INSERT db_datadict_2.t1 but not db_datadict_1 to testuser1 -> invisible
121 # Root granted SELECT db_datadict_3. to me -> visible
122 # testuser1 granted SELECT db_datadict_4. to me -> visible
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 'testuser2'@'localhost' def db_datadict_1 SELECT NO
128 'testuser2'@'localhost' def db_datadict_3 SELECT NO
129 'testuser2'@'localhost' def db_datadict_4 SELECT NO
130 SHOW GRANTS FOR 'testuser1'@'localhost';
131 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'user'
132 SHOW GRANTS FOR 'testuser2'@'localhost';
133 Grants for testuser2@localhost
134 GRANT USAGE ON *.* TO `testuser2`@`localhost`
135 GRANT SELECT ON `db_datadict_1`.* TO `testuser2`@`localhost`
136 GRANT SELECT ON `db_datadict_3`.* TO `testuser2`@`localhost`
137 GRANT SELECT ON `db_datadict_4`.* TO `testuser2`@`localhost`
138 # Switch to connection default and close connections testuser1 and testuser2
139 SELECT * FROM information_schema.schema_privileges
140 WHERE table_schema LIKE 'db_datadict%'
141 ORDER BY grantee,table_schema,privilege_type;
142 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
143 'testuser1'@'localhost' def db_datadict_1 INSERT NO
144 'testuser1'@'localhost' def db_datadict_4 SELECT YES
145 'testuser2'@'localhost' def db_datadict_1 SELECT NO
146 'testuser2'@'localhost' def db_datadict_3 SELECT NO
147 'testuser2'@'localhost' def db_datadict_4 SELECT NO
148 SHOW GRANTS FOR 'testuser1'@'localhost';
149 Grants for testuser1@localhost
150 GRANT USAGE ON *.* TO `testuser1`@`localhost`
151 GRANT INSERT ON `db_datadict_1`.* TO `testuser1`@`localhost`
152 GRANT SELECT ON `db_datadict_4`.* TO `testuser1`@`localhost` WITH GRANT OPTION
153 GRANT INSERT ON `db_datadict_2`.`t1` TO `testuser1`@`localhost`
154 SHOW GRANTS FOR 'testuser2'@'localhost';
155 Grants for testuser2@localhost
156 GRANT USAGE ON *.* TO `testuser2`@`localhost`
157 GRANT SELECT ON `db_datadict_1`.* TO `testuser2`@`localhost`
158 GRANT SELECT ON `db_datadict_3`.* TO `testuser2`@`localhost`
159 GRANT SELECT ON `db_datadict_4`.* TO `testuser2`@`localhost`
160 DROP USER 'testuser1'@'localhost';
161 DROP USER 'testuser2'@'localhost';
162 DROP DATABASE db_datadict_1;
163 DROP DATABASE db_datadict_2;
164 DROP DATABASE db_datadict_3;
165 ################################################################################
166 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES modifications
167 ################################################################################
168 DROP DATABASE IF EXISTS db_datadict;
169 CREATE DATABASE db_datadict;
170 DROP USER 'the_user'@'localhost';
171 DROP USER 'testuser1'@'localhost';
172 CREATE USER 'testuser1'@'localhost';
173 GRANT SELECT ON test.* TO 'testuser1'@'localhost';
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 # Establish connection testuser1 (user=testuser1)
179 SELECT * FROM information_schema.schema_privileges
180 WHERE table_schema = 'db_datadict'
181 ORDER BY grantee,table_schema,privilege_type;
182 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
183 # Switch to connection default
184 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost';
185 SELECT * FROM information_schema.schema_privileges
186 WHERE table_schema = 'db_datadict'
187 ORDER BY grantee,table_schema,privilege_type;
188 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
189 'testuser1'@'localhost' def db_datadict UPDATE NO
190 # Switch to connection testuser1
191 SELECT * FROM information_schema.schema_privileges
192 WHERE table_schema = 'db_datadict'
193 ORDER BY grantee,table_schema,privilege_type;
194 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
195 'testuser1'@'localhost' def db_datadict UPDATE NO
196 # Switch to connection default
197 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
198 SELECT * FROM information_schema.schema_privileges
199 WHERE table_schema = 'db_datadict'
200 ORDER BY grantee,table_schema,privilege_type;
201 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
202 'testuser1'@'localhost' def db_datadict SELECT NO
203 'testuser1'@'localhost' def db_datadict UPDATE NO
204 # Switch to connection testuser1
205 SELECT * FROM information_schema.schema_privileges
206 WHERE table_schema = 'db_datadict'
207 ORDER BY grantee,table_schema,privilege_type;
208 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
209 'testuser1'@'localhost' def db_datadict SELECT NO
210 'testuser1'@'localhost' def db_datadict UPDATE NO
211 # Switch to connection default
212 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
213 SELECT * FROM information_schema.schema_privileges
214 WHERE table_schema = 'db_datadict'
215 ORDER BY grantee,table_schema,privilege_type;
216 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
217 'testuser1'@'localhost' def db_datadict SELECT YES
218 'testuser1'@'localhost' def db_datadict UPDATE YES
219 # Switch to connection testuser1
220 SELECT * FROM information_schema.schema_privileges
221 WHERE table_schema = 'db_datadict'
222 ORDER BY grantee,table_schema,privilege_type;
223 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
224 'testuser1'@'localhost' def db_datadict SELECT YES
225 'testuser1'@'localhost' def db_datadict UPDATE YES
226 # Switch to connection default
227 DROP SCHEMA db_datadict;
228 SELECT * FROM information_schema.schema_privileges
229 WHERE table_schema = 'db_datadict'
230 ORDER BY grantee,table_schema,privilege_type;
231 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
232 'testuser1'@'localhost' def db_datadict SELECT YES
233 'testuser1'@'localhost' def db_datadict UPDATE YES
234 # Switch to connection testuser1
235 SELECT * FROM information_schema.schema_privileges
236 WHERE table_schema = 'db_datadict'
237 ORDER BY grantee,table_schema,privilege_type;
238 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
239 'testuser1'@'localhost' def db_datadict SELECT YES
240 'testuser1'@'localhost' def db_datadict UPDATE YES
241 # Switch to connection default
242 REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost';
243 SELECT * FROM information_schema.schema_privileges
244 WHERE table_schema = 'db_datadict'
245 ORDER BY grantee,table_schema,privilege_type;
246 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
247 'testuser1'@'localhost' def db_datadict SELECT YES
248 # Switch to connection testuser1
249 SELECT * FROM information_schema.schema_privileges
250 WHERE table_schema = 'db_datadict'
251 ORDER BY grantee,table_schema,privilege_type;
252 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
253 'testuser1'@'localhost' def db_datadict SELECT YES
254 # Switch to connection default
255 RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
256 SELECT * FROM information_schema.schema_privileges
257 WHERE table_schema = 'db_datadict'
258 ORDER BY grantee,table_schema,privilege_type;
259 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
260 'the_user'@'localhost' def db_datadict SELECT YES
261 # Switch to connection testuser1
262 SELECT * FROM information_schema.schema_privileges
263 WHERE table_schema = 'db_datadict'
264 ORDER BY grantee,table_schema,privilege_type;
265 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
266 'the_user'@'localhost' def db_datadict SELECT YES
267 # Close connection testuser1
268 # Establish connection the_user (user=the_user)
269 SELECT * FROM information_schema.schema_privileges
270 WHERE table_schema = 'db_datadict'
271 ORDER BY grantee,table_schema,privilege_type;
272 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
273 'the_user'@'localhost' def db_datadict SELECT YES
274 # Close connection the_user
275 # Switch to connection default
276 SELECT * FROM information_schema.schema_privileges
277 WHERE table_schema = 'db_datadict'
278 ORDER BY grantee,table_schema,privilege_type;
279 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
280 'the_user'@'localhost' def db_datadict SELECT YES
281 DROP USER 'the_user'@'localhost';
282 SELECT * FROM information_schema.schema_privileges
283 WHERE table_schema = 'db_datadict'
284 ORDER BY grantee,table_schema,privilege_type;
285 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
286 ########################################################################
287 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
288 # DDL on INFORMATION_SCHEMA table are not supported
289 ########################################################################
290 DROP DATABASE IF EXISTS db_datadict;
291 CREATE DATABASE db_datadict;
292 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
293 ENGINE = <engine_type>;
294 DROP USER 'testuser1'@'localhost';
295 CREATE USER 'testuser1'@'localhost';
296 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
297 INSERT INTO information_schema.schema_privileges
298 SELECT * FROM information_schema.schema_privileges;
299 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
300 UPDATE information_schema.schema_privileges SET table_schema = 'test'
301 WHERE table_name = 't1';
302 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
303 DELETE FROM information_schema.schema_privileges
304 WHERE table_schema = 'db_datadict';
305 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
306 TRUNCATE information_schema.schema_privileges;
307 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
308 CREATE INDEX my_idx_on_tables
309 ON information_schema.schema_privileges(table_schema);
310 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
311 ALTER TABLE information_schema.schema_privileges ADD f1 INT;
312 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
313 DROP TABLE information_schema.schema_privileges;
314 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
315 ALTER TABLE information_schema.schema_privileges
316 RENAME db_datadict.schema_privileges;
317 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
318 ALTER TABLE information_schema.schema_privileges
319 RENAME information_schema.xschema_privileges;
320 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
321 DROP DATABASE db_datadict;
322 DROP USER 'testuser1'@'localhost';