SHOW TABLES FROM information_schema LIKE 'SCHEMATA_EXTENSIONS'; Tables_in_information_schema (SCHEMATA_EXTENSIONS) SCHEMATA_EXTENSIONS ####################################################################### # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT ####################################################################### DROP VIEW IF EXISTS test.v1; DROP PROCEDURE IF EXISTS test.p1; DROP FUNCTION IF EXISTS test.f1; CREATE VIEW test.v1 AS SELECT * FROM information_schema.SCHEMATA_EXTENSIONS; CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMATA_EXTENSIONS; CREATE FUNCTION test.f1() returns BIGINT BEGIN DECLARE counter BIGINT DEFAULT NULL; SELECT COUNT(*) INTO counter FROM information_schema.SCHEMATA_EXTENSIONS; RETURN counter; END// # Attention: The printing of the next result sets is disabled. SELECT * FROM information_schema.SCHEMATA_EXTENSIONS; SELECT * FROM test.v1; CALL test.p1; SELECT test.f1(); DROP VIEW test.v1; DROP PROCEDURE test.p1; DROP FUNCTION test.f1; ######################################################################### # Testcase 3.2.9.1: INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS layout; ######################################################################### DESCRIBE information_schema.SCHEMATA_EXTENSIONS; Field Type Null Key Default Extra CATALOG_NAME varchar(64) NO NULL SCHEMA_NAME varchar(64) NO NULL OPTIONS varchar(256) YES NULL SHOW CREATE TABLE information_schema.SCHEMATA_EXTENSIONS; View Create View character_set_client collation_connection SCHEMATA_EXTENSIONS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`SCHEMATA_EXTENSIONS` AS select `cat`.`name` AS `CATALOG_NAME`,`sch`.`name` AS `SCHEMA_NAME`,get_dd_schema_options(`sch`.`options`) AS `OPTIONS` from (`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) where (0 <> can_access_database(`sch`.`name`)) utf8mb3 utf8mb3_general_ci SHOW COLUMNS FROM information_schema.SCHEMATA_EXTENSIONS; Field Type Null Key Default Extra CATALOG_NAME varchar(64) NO NULL SCHEMA_NAME varchar(64) NO NULL OPTIONS varchar(256) YES NULL SELECT catalog_name, schema_name FROM information_schema.schemata_extensions WHERE catalog_name != 'def'; CATALOG_NAME SCHEMA_NAME ######################################################################### # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS # accessible information ######################################################################### DROP DATABASE IF EXISTS db_datadict_1; DROP DATABASE IF EXISTS db_datadict_2; CREATE DATABASE db_datadict_1; CREATE DATABASE db_datadict_2; DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; CREATE USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; CREATE USER 'testuser3'@'localhost'; GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost'; GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost'; GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost'; SELECT * FROM information_schema.schemata_extensions WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME OPTIONS def db_datadict_1 def db_datadict_2 SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 db_datadict_2 # Establish connection testuser1 (user=testuser1) SELECT * FROM information_schema.schemata_extensions WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME OPTIONS def db_datadict_1 SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 # Establish connection testuser2 (user=testuser2) SELECT * FROM information_schema.schemata_extensions WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME OPTIONS def db_datadict_1 def db_datadict_2 SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 db_datadict_2 # Establish connection testuser3 (user=testuser3) SELECT * FROM information_schema.schemata_extensions WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME OPTIONS SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) # Switch to connection default and close connections testuser1,testuser2, # testuser3 DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; DROP DATABASE db_datadict_1; DROP DATABASE db_datadict_2; ######################################################################### # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: # INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS modifications ######################################################################### DROP DATABASE IF EXISTS db_datadict; SELECT * FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; CATALOG_NAME SCHEMA_NAME OPTIONS CREATE DATABASE db_datadict; SELECT * FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; CATALOG_NAME SCHEMA_NAME OPTIONS def db_datadict SELECT schema_name, options FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; SCHEMA_NAME OPTIONS db_datadict ALTER SCHEMA db_datadict READ ONLY=1; SELECT schema_name, options FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; SCHEMA_NAME OPTIONS db_datadict READ ONLY=1 ALTER SCHEMA db_datadict READ ONLY=0; SELECT schema_name, options FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; SCHEMA_NAME OPTIONS db_datadict SELECT schema_name FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; SCHEMA_NAME db_datadict DROP DATABASE db_datadict; SELECT schema_name FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; SCHEMA_NAME ######################################################################## # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and # DDL on INFORMATION_SCHEMA tables are not supported ######################################################################## DROP DATABASE IF EXISTS db_datadict; CREATE DATABASE db_datadict; INSERT INTO information_schema.schemata_extensions (catalog_name, schema_name, options) VALUES ('def', 'db1', ''); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' INSERT INTO information_schema.schemata_extensions SELECT * FROM information_schema.schemata_extensions; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.schemata_extensions SET options = 'abc' WHERE schema_name = 'db_datadict'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.schemata_extensions SET catalog_name = 't_4711'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DELETE FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.schemata_extensions; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' CREATE INDEX i1 ON information_schema.schemata_extensions(schema_name); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.schemata_extensions ADD f1 INT; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE information_schema.schemata_extensions; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.schemata_extensions RENAME db_datadict.schemata_extensions; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.schemata_extensions RENAME information_schema.xschemata_extensions; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP DATABASE db_datadict;