| 1 |
# |
| 2 |
# Check access to I_S.x_extensions tables |
| 3 |
# |
| 4 |
CREATE SCHEMA db_datadict; |
| 5 |
CREATE USER 'testuser1'@'localhost'; |
| 6 |
CREATE USER 'testuser2'@'localhost'; |
| 7 |
CREATE TABLE db_datadict.t1 |
| 8 |
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT, |
| 9 |
UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4)); |
| 10 |
CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2; |
| 11 |
GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost'; |
| 12 |
GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost'; |
| 13 |
CREATE TABLE db_datadict.t2 |
| 14 |
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4)); |
| 15 |
GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost'; |
| 16 |
# Checking tables_extensions as root |
| 17 |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 18 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT |
| 19 |
def db_datadict t1 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 16384 0 1 <create time> NULL NULL utf8mb4_0900_ai_ci NULL |
| 20 |
def db_datadict t2 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 NULL <create time> NULL NULL utf8mb4_0900_ai_ci NULL |
| 21 |
def db_datadict v1 VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL <create time> NULL NULL NULL NULL NULL VIEW |
| 22 |
SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 23 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 24 |
def db_datadict t1 NULL NULL |
| 25 |
def db_datadict t2 NULL NULL |
| 26 |
def db_datadict v1 NULL NULL |
| 27 |
SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS; |
| 28 |
TABLES_ROWS TABLES_EXT_ROWS |
| 29 |
3 3 |
| 30 |
# Checking columns_extensions as root |
| 31 |
SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 32 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION SRS_ID |
| 33 |
def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) MUL select,insert,update,references NULL |
| 34 |
def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select,insert,update,references NULL |
| 35 |
def db_datadict t1 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NULL |
| 36 |
def db_datadict t1 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int PRI auto_increment select,insert,update,references NULL |
| 37 |
def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) PRI select,insert,update,references NULL |
| 38 |
def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select,insert,update,references NULL |
| 39 |
def db_datadict t2 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NULL |
| 40 |
def db_datadict t2 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int PRI select,insert,update,references NULL |
| 41 |
def db_datadict v1 f1 1 0 NO int NULL NULL 10 0 NULL NULL NULL int select,insert,update,references NULL |
| 42 |
def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int select,insert,update,references NULL |
| 43 |
SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 44 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 45 |
def db_datadict t1 f1 NULL NULL |
| 46 |
def db_datadict t1 f2 NULL NULL |
| 47 |
def db_datadict t1 f3 NULL NULL |
| 48 |
def db_datadict t1 f4 NULL NULL |
| 49 |
def db_datadict t2 f1 NULL NULL |
| 50 |
def db_datadict t2 f2 NULL NULL |
| 51 |
def db_datadict t2 f3 NULL NULL |
| 52 |
def db_datadict t2 f4 NULL NULL |
| 53 |
def db_datadict v1 f1 NULL NULL |
| 54 |
def db_datadict v1 f2 NULL NULL |
| 55 |
SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS; |
| 56 |
COLUMNS_ROWS COLUMNS_EXT_ROWS |
| 57 |
10 10 |
| 58 |
# Checking table_constraints_extensions as root |
| 59 |
SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 60 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED |
| 61 |
def db_datadict MUL_IDX db_datadict t1 UNIQUE YES |
| 62 |
def db_datadict PRIMARY db_datadict t1 PRIMARY KEY YES |
| 63 |
def db_datadict PRIMARY db_datadict t2 PRIMARY KEY YES |
| 64 |
SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 65 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 66 |
def db_datadict MUL_IDX t1 NULL NULL |
| 67 |
def db_datadict PRIMARY t1 NULL NULL |
| 68 |
def db_datadict PRIMARY t2 NULL NULL |
| 69 |
SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS; |
| 70 |
CONSTRAINTS_ROWS CONSTRAINTS_EXT_ROWS |
| 71 |
3 3 |
| 72 |
# Checking tables_extensions as testuser1 |
| 73 |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 74 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT |
| 75 |
def db_datadict t1 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 16384 0 1 <create time> NULL NULL utf8mb4_0900_ai_ci NULL |
| 76 |
def db_datadict v1 VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL <create time> NULL NULL NULL NULL NULL VIEW |
| 77 |
SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 78 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 79 |
def db_datadict t1 NULL NULL |
| 80 |
def db_datadict v1 NULL NULL |
| 81 |
SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS; |
| 82 |
TABLES_ROWS TABLES_EXT_ROWS |
| 83 |
2 2 |
| 84 |
# Checking columns_extensions as testuser1 |
| 85 |
SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 86 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION SRS_ID |
| 87 |
def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) MUL select NULL |
| 88 |
def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select NULL |
| 89 |
def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int select NULL |
| 90 |
SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 91 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 92 |
def db_datadict t1 f1 NULL NULL |
| 93 |
def db_datadict t1 f2 NULL NULL |
| 94 |
def db_datadict v1 f2 NULL NULL |
| 95 |
SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS; |
| 96 |
COLUMNS_ROWS COLUMNS_EXT_ROWS |
| 97 |
3 3 |
| 98 |
# Checking table_constraints_extensions as testuser1 |
| 99 |
SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 100 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED |
| 101 |
def db_datadict MUL_IDX db_datadict t1 UNIQUE YES |
| 102 |
def db_datadict PRIMARY db_datadict t1 PRIMARY KEY YES |
| 103 |
SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 104 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 105 |
def db_datadict MUL_IDX t1 NULL NULL |
| 106 |
def db_datadict PRIMARY t1 NULL NULL |
| 107 |
SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS; |
| 108 |
CONSTRAINTS_ROWS CONSTRAINTS_EXT_ROWS |
| 109 |
2 2 |
| 110 |
# Checking tables_extensions as testuser1 |
| 111 |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 112 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT |
| 113 |
def db_datadict t2 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 NULL <create time> NULL NULL utf8mb4_0900_ai_ci NULL |
| 114 |
SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 115 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 116 |
def db_datadict t2 NULL NULL |
| 117 |
SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS; |
| 118 |
TABLES_ROWS TABLES_EXT_ROWS |
| 119 |
1 1 |
| 120 |
# Checking columns_extensions as testuser2 |
| 121 |
SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 122 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION SRS_ID |
| 123 |
def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) PRI insert NULL |
| 124 |
def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text insert NULL |
| 125 |
SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 126 |
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 127 |
def db_datadict t2 f1 NULL NULL |
| 128 |
def db_datadict t2 f2 NULL NULL |
| 129 |
SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS; |
| 130 |
COLUMNS_ROWS COLUMNS_EXT_ROWS |
| 131 |
2 2 |
| 132 |
# Checking table_constraints_extensions as testuser2 |
| 133 |
SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 134 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED |
| 135 |
def db_datadict PRIMARY db_datadict t2 PRIMARY KEY YES |
| 136 |
SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 137 |
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE |
| 138 |
def db_datadict PRIMARY t2 NULL NULL |
| 139 |
SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS; |
| 140 |
CONSTRAINTS_ROWS CONSTRAINTS_EXT_ROWS |
| 141 |
1 1 |
| 142 |
# Cleanup |
| 143 |
DROP TABLE db_datadict.t2; |
| 144 |
DROP TABLE db_datadict.t1; |
| 145 |
DROP USER 'testuser2'@'localhost'; |
| 146 |
DROP USER 'testuser1'@'localhost'; |
| 147 |
DROP SCHEMA db_datadict; |