# derived from the NIST tests converted to MySQL # ist001 warnings around SUN.TESTREPORT --disable_abort_on_error --echo # DROP the users if they already exist --error 0,1396 eval DROP USER testdb_1@localhost; --error 0,1396 eval DROP USER testdb_2@localhost; --error 0,1396 eval DROP USER testdb_3@localhost; # --echo # CREATE the users testdb_1, testdb_2, testdb_3 eval CREATE USER testdb_1@localhost identified by 'PWD'; eval CREATE USER testdb_2@localhost identified by 'PWD'; eval CREATE USER testdb_3@localhost identified by 'PWD'; # --echo # GRANT them all rights including grant option to schemas having the name of the user eval grant ALL ON testdb_1.* to testdb_1@localhost with grant option; eval grant ALL ON testdb_2.* to testdb_2@localhost with grant option; eval grant ALL ON testdb_3.* to testdb_3@localhost with grant option; # --echo # Establish for all new created users a session --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (testdb_1,localhost,testdb_1,PWD,test); connection default; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (testdb_2,localhost,testdb_2,PWD,test); connection default; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (testdb_3,localhost,testdb_3,PWD,test); connection default; #------------------------------------------------------------------------ # --source suite/nist/t/schema/schema1.mysql --echo # Switch to session of testdb_1 connection testdb_1 ; --disable_warnings DROP SCHEMA IF EXISTS testdb_1; --enable_warnings CREATE SCHEMA testdb_1; USE testdb_1 ; CREATE TABLE t1 (f1 CHAR(4), f2 CHAR(4), f3 CHAR(3)); CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1; eval GRANT INSERT ON v1 TO testdb_1@localhost WITH GRANT OPTION; eval GRANT INSERT ON v1 TO testdb_2@localhost WITH GRANT OPTION; eval GRANT INSERT ON v1 TO testdb_3@localhost WITH GRANT OPTION; # -- ************* End of Schema ************* # Additionale statement COMMIT WORK; connection default; #------------------------------------------------- # --source suite/nist/t/schema/schema5.mysql --echo # Switch to session of testdb_2 connection testdb_2 ; --disable_warnings DROP SCHEMA IF EXISTS testdb_2; --enable_warnings CREATE SCHEMA testdb_2; USE testdb_2 ; COMMIT WORK; connection default; #---------------------------------------------- # --source suite/nist/t/schema/schema8.mysql --echo # Switch to session of testdb_3 connection testdb_3 ; --disable_warnings DROP SCHEMA IF EXISTS testdb_3; --enable_warnings CREATE SCHEMA testdb_3; USE testdb_3 ; CREATE VIEW v1 AS SELECT f1, f2, f3 FROM testdb_1.v1; eval GRANT INSERT ON v1 TO testdb_1@localhost; eval GRANT INSERT ON v1 TO testdb_2@localhost; eval GRANT INSERT ON v1 TO testdb_3@localhost; COMMIT WORK; connection default; #---------------------------------------------- # --source suite/nist/t/sql/ist001.mysql --echo # Switch to session of testdb_2 connection testdb_2 ; use testdb_2 ; -- TEST:0603 INFO_SCHEM.TABLES definition! --echo # I get here the suspicious warnings Warning 1356 View 'testdb_3.v1' references invalid ..... SELECT * FROM INFORMATION_SCHEMA.COLUMNS B; SELECT * FROM INFORMATION_SCHEMA.COLUMNS A WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); -- Verify CONSTRAINT CHECK_TABLE_IN_COLUMNS --echo # One statement from original NIST testscript ist001 --echo # I get here the suspicious warnings Warning 1356 View 'testdb_3.v1' references invalid ..... SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A WHERE NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); connection default; ### Cleanup # # Disconnect the users connection default; disconnect testdb_1; disconnect testdb_2; disconnect testdb_3; # # Drop the users and the SCHEMAs --disable_warnings let $new_dba= testdb_1; --error 0,1396 eval DROP USER $new_dba@localhost; --error 0,1217 eval DROP SCHEMA IF EXISTS $new_dba; let $new_dba= testdb_2; --error 0,1396 eval DROP USER $new_dba@localhost; --error 0,1217 eval DROP SCHEMA IF EXISTS $new_dba; let $new_dba= testdb_3; --error 0,1396 eval DROP USER $new_dba@localhost; --error 0,1217 eval DROP SCHEMA IF EXISTS $new_dba; --enable_warnings