| 1 |
--echo # |
| 2 |
--echo # Check access to I_S.x_extensions tables |
| 3 |
--echo # |
| 4 |
|
| 5 |
CREATE SCHEMA db_datadict; |
| 6 |
CREATE USER 'testuser1'@'localhost'; |
| 7 |
CREATE USER 'testuser2'@'localhost'; |
| 8 |
|
| 9 |
CREATE TABLE db_datadict.t1 |
| 10 |
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT, |
| 11 |
UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4)); |
| 12 |
CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2; |
| 13 |
GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost'; |
| 14 |
GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost'; |
| 15 |
CREATE TABLE db_datadict.t2 |
| 16 |
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4)); |
| 17 |
GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost'; |
| 18 |
|
| 19 |
let $verify_tables_ext= 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; |
| 20 |
|
| 21 |
let $verify_columns_ext= 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; |
| 22 |
|
| 23 |
let $verify_table_constraints_ext= 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; |
| 24 |
|
| 25 |
let $tables_q= 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; |
| 26 |
|
| 27 |
let $tables_ext_q= SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name; |
| 28 |
|
| 29 |
let $columns_q= SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 30 |
|
| 31 |
let $columns_ext_q= SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name; |
| 32 |
|
| 33 |
let $table_constraints_q= SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 34 |
|
| 35 |
let $table_constraints_ext_q= SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name; |
| 36 |
|
| 37 |
--echo # Checking tables_extensions as root |
| 38 |
eval $tables_q; |
| 39 |
eval $tables_ext_q; |
| 40 |
eval $verify_tables_ext; |
| 41 |
|
| 42 |
--echo # Checking columns_extensions as root |
| 43 |
eval $columns_q; |
| 44 |
eval $columns_ext_q; |
| 45 |
eval $verify_columns_ext; |
| 46 |
|
| 47 |
--echo # Checking table_constraints_extensions as root |
| 48 |
eval $table_constraints_q; |
| 49 |
eval $table_constraints_ext_q; |
| 50 |
eval $verify_table_constraints_ext; |
| 51 |
|
| 52 |
connect (testuser1, localhost, testuser1, , db_datadict); |
| 53 |
--echo # Checking tables_extensions as testuser1 |
| 54 |
eval $tables_q; |
| 55 |
eval $tables_ext_q; |
| 56 |
eval $verify_tables_ext; |
| 57 |
|
| 58 |
--echo # Checking columns_extensions as testuser1 |
| 59 |
eval $columns_q; |
| 60 |
eval $columns_ext_q; |
| 61 |
eval $verify_columns_ext; |
| 62 |
|
| 63 |
--echo # Checking table_constraints_extensions as testuser1 |
| 64 |
eval $table_constraints_q; |
| 65 |
eval $table_constraints_ext_q; |
| 66 |
eval $verify_table_constraints_ext; |
| 67 |
|
| 68 |
connect (testuser2, localhost, testuser2, , db_datadict); |
| 69 |
--echo # Checking tables_extensions as testuser1 |
| 70 |
eval $tables_q; |
| 71 |
eval $tables_ext_q; |
| 72 |
eval $verify_tables_ext; |
| 73 |
|
| 74 |
--echo # Checking columns_extensions as testuser2 |
| 75 |
eval $columns_q; |
| 76 |
eval $columns_ext_q; |
| 77 |
eval $verify_columns_ext; |
| 78 |
|
| 79 |
--echo # Checking table_constraints_extensions as testuser2 |
| 80 |
eval $table_constraints_q; |
| 81 |
eval $table_constraints_ext_q; |
| 82 |
eval $verify_table_constraints_ext; |
| 83 |
|
| 84 |
connection default; |
| 85 |
disconnect testuser1; |
| 86 |
disconnect testuser2; |
| 87 |
|
| 88 |
--echo # Cleanup |
| 89 |
|
| 90 |
DROP TABLE db_datadict.t2; |
| 91 |
DROP TABLE db_datadict.t1; |
| 92 |
|
| 93 |
DROP USER 'testuser2'@'localhost'; |
| 94 |
DROP USER 'testuser1'@'localhost'; |
| 95 |
DROP SCHEMA db_datadict; |