############################################################################### # WL#12098: MySQL system users # # # # This test file covers the scenarios around adding/removing the # # restriction_list in mysql.user.user_attributes column. # # # # The restriction_list is a key:value pair. Database name serves as key, # # and an array of static privileges which are prohibited on that database # # serves as value. # # # # Refer the test file - partial_revokes_dml.test for the usage of # # partial revokes once they are created # # # ############################################################################### # Save the initial number of concurrent sessions --source include/count_sessions.inc --disable_query_log CALL mtr.add_suppression("one or more privileges granted through"); CALL mtr.add_suppression("these are not valid database privileges"); CALL mtr.add_suppression("corresponding global privilege(s) are not granted"); let $saved_partial_revokes = `SELECT @@global.partial_revokes`; --disable_warnings SET GLOBAL partial_revokes= ON; --enable_warnings --enable_query_log --let $TEST_MODE = NORMAL --source ../../auth_sec/include/partial_revokes_add_remove.inc --echo ------------------------------------------------------------------------- --echo Toggling the partial_revokes system variable --echo ------------------------------------------------------------------------- CREATE USER foo; --echo # 1. Add partial revoke and then try to turn OFF the system variable GRANT INSERT ON *.* to foo; REVOKE INSERT ON mysql.* FROM foo; --error ER_PARTIAL_REVOKES_EXIST SET @@global.partial_revokes = OFF; --echo # 2. Remove partial revoke and then try to turn OFF the system variable GRANT INSERT ON mysql.* to foo; SET @@global.partial_revokes = OFF; --echo # 3. Drop user that had partial revoke and --echo # then try to turn OFF the system variable SET @@global.partial_revokes = ON; REVOKE INSERT ON mysql.* FROM foo; --error ER_PARTIAL_REVOKES_EXIST SET @@global.partial_revokes = OFF; DROP USER foo; SHOW VARIABLES LIKE 'partial_revokes'; SET @@global.partial_revokes = OFF; --echo # create the user and its partial revokes again SET @@global.partial_revokes = ON; CREATE USER foo; GRANT INSERT ON *.* to foo; REVOKE INSERT ON mysql.* FROM foo; --echo # 4. After server restart @@partial_revokes must be turned ON --echo # with a warning in server log. --echo --echo # Restart server. --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --shutdown_server --source include/wait_until_disconnected.inc # Remove the server log file so that we find the expected warning only once. perl; my $filetodelete = "$ENV{'MYSQLTEST_VARDIR'}/log/mysqld.1.err"; while (-e $filetodelete) { unlink $filetodelete; sleep 1; } EOF --exec echo "restart:--partial_revokes=OFF" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --enable_reconnect --source include/wait_until_connected_again.inc --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err --let $assert_select=.* \[Warning\] \[[^]]*\] \[[^]]*\] At least one partial revoke .* Turning ON the system variable .* --let $assert_text= Found the expected warning line in the server log. --let $assert_count=1 --source include/assert_grep.inc --echo # system variable must be ON. Partial revokes must exists. SHOW VARIABLES LIKE 'partial_revokes'; SHOW GRANTS FOR foo; DROP USER foo; --echo # 5. @@partial_revokes can be turned OFF after there exists no --echo # partial revokes in the user_attributes table SET GLOBAL partial_revokes = OFF; --echo # 6. After the Flush privileges, SHOW stmts should display correct information. --echo # CREATE USER bar; GRANT SELECT ON *.* TO bar; UPDATE mysql.user SET user_attributes = '{"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]}' WHERE USER = 'bar'; FLUSH PRIVILEGES; SHOW VARIABLES LIKE 'partial_revokes'; SHOW GRANTS FOR bar; --connect(bar_con, localhost, bar,,,$MASTER_MYPORT) --error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM mysql.user; --connection default --disconnect bar_con DROP USER bar; --echo # 7. Adding the restriction through DML with persisted partial_revokes=OFF --echo # fails server to restart. --let MYSQLD_DATADIR = `select @@datadir` --let MYSQLD_LOG=$MYSQL_TMP_DIR/server.log CREATE USER baz; SET PERSIST partial_revokes = OFF; GRANT SELECT ON *.* TO baz; UPDATE mysql.user SET user_attributes = '{"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]}' WHERE USER = 'baz'; --echo # Restart server with partial_revokes=OFF. --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --shutdown_server --source include/wait_until_disconnected.inc --echo # Try starting the server: must fail due to persist variable. --error 1 --exec $MYSQLD --console --gdb --datadir=$MYSQLD_DATADIR --port=$MYSQLD_PORT --socket=$MYSQLD_SOCKET >> $MYSQLD_LOG 2>&1 --remove_file $MYSQLD_LOG # Remove the persist option to restart the server --remove_file $MYSQLD_DATADIR/mysqld-auto.cnf --exec echo "restart:" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --enable_reconnect --source include/wait_until_connected_again.inc --echo # system variable must be ON. Partial revokes must exists. SHOW VARIABLES LIKE 'partial_revokes'; SHOW GRANTS FOR baz; DROP USER baz; --echo --------Toggling the system variable tests end -------------------------- --echo ------------------------------------------------------------------------- --echo # Start the server with --skip-grant-tables option --echo ------------------------------------------------------------------------- --let $restart_parameters = "restart:--skip-grant-tables" --let $wait_for_tcpsocket_status = no_wait --source include/restart_mysqld.inc --enable_reconnect --source include/wait_until_connected_again.inc SET GLOBAL partial_revokes = ON; --let $restart_parameters = restart: --source include/restart_mysqld.inc --enable_reconnect --source include/wait_until_connected_again.inc --echo # --echo # Make the variable persitent this time --echo # SET PERSIST partial_revokes = ON; --let $restart_parameters = "restart:--skip-grant-tables" --let $wait_for_tcpsocket_status = no_wait --source include/restart_mysqld.inc --enable_reconnect --source include/wait_until_connected_again.inc RESET PERSIST; --let $restart_parameters = restart: --source include/restart_mysqld.inc --enable_reconnect --source include/wait_until_connected_again.inc --echo ------------ End test with --skip-grant-tables option ------------------- --disable_query_log --disable_warnings eval SET GLOBAL partial_revokes = $saved_partial_revokes; --enable_warnings --enable_query_log # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc call mtr.add_suppression(".*At least one partial revoke .* Turning ON the system variable .*");