| 1 |
############################################################################### |
| 2 |
# WL#12098: MySQL system users # |
| 3 |
# # |
| 4 |
# This test file covers the scenarios around adding/removing the # |
| 5 |
# restriction_list in mysql.user.user_attributes column. # |
| 6 |
# # |
| 7 |
# The restriction_list is a key:value pair. Database name serves as key, # |
| 8 |
# and an array of static privileges which are prohibited on that database # |
| 9 |
# serves as value. # |
| 10 |
# # |
| 11 |
# Refer the test file - partial_revokes_dml.test for the usage of # |
| 12 |
# partial revokes once they are created # |
| 13 |
# # |
| 14 |
############################################################################### |
| 15 |
|
| 16 |
|
| 17 |
# Save the initial number of concurrent sessions |
| 18 |
--source include/count_sessions.inc |
| 19 |
|
| 20 |
--disable_query_log |
| 21 |
CALL mtr.add_suppression("one or more privileges granted through"); |
| 22 |
CALL mtr.add_suppression("these are not valid database privileges"); |
| 23 |
CALL mtr.add_suppression("corresponding global privilege(s) are not granted"); |
| 24 |
|
| 25 |
let $saved_partial_revokes = `SELECT @@global.partial_revokes`; |
| 26 |
--disable_warnings |
| 27 |
SET GLOBAL partial_revokes= ON; |
| 28 |
--enable_warnings |
| 29 |
--enable_query_log |
| 30 |
--let $TEST_MODE = NORMAL |
| 31 |
--source ../../auth_sec/include/partial_revokes_add_remove.inc |
| 32 |
|
| 33 |
--echo ------------------------------------------------------------------------- |
| 34 |
--echo Toggling the partial_revokes system variable |
| 35 |
--echo ------------------------------------------------------------------------- |
| 36 |
CREATE USER foo; |
| 37 |
--echo # 1. Add partial revoke and then try to turn OFF the system variable |
| 38 |
GRANT INSERT ON *.* to foo; |
| 39 |
REVOKE INSERT ON mysql.* FROM foo; |
| 40 |
--error ER_PARTIAL_REVOKES_EXIST |
| 41 |
SET @@global.partial_revokes = OFF; |
| 42 |
--echo # 2. Remove partial revoke and then try to turn OFF the system variable |
| 43 |
GRANT INSERT ON mysql.* to foo; |
| 44 |
SET @@global.partial_revokes = OFF; |
| 45 |
--echo # 3. Drop user that had partial revoke and |
| 46 |
--echo # then try to turn OFF the system variable |
| 47 |
SET @@global.partial_revokes = ON; |
| 48 |
REVOKE INSERT ON mysql.* FROM foo; |
| 49 |
--error ER_PARTIAL_REVOKES_EXIST |
| 50 |
SET @@global.partial_revokes = OFF; |
| 51 |
DROP USER foo; |
| 52 |
SHOW VARIABLES LIKE 'partial_revokes'; |
| 53 |
SET @@global.partial_revokes = OFF; |
| 54 |
--echo # create the user and its partial revokes again |
| 55 |
SET @@global.partial_revokes = ON; |
| 56 |
CREATE USER foo; |
| 57 |
GRANT INSERT ON *.* to foo; |
| 58 |
REVOKE INSERT ON mysql.* FROM foo; |
| 59 |
--echo # 4. After server restart @@partial_revokes must be turned ON |
| 60 |
--echo # with a warning in server log. |
| 61 |
--echo |
| 62 |
--echo # Restart server. |
| 63 |
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect |
| 64 |
--shutdown_server |
| 65 |
--source include/wait_until_disconnected.inc |
| 66 |
# Remove the server log file so that we find the expected warning only once. |
| 67 |
perl; |
| 68 |
my $filetodelete = "$ENV{'MYSQLTEST_VARDIR'}/log/mysqld.1.err"; |
| 69 |
while (-e $filetodelete) { |
| 70 |
unlink $filetodelete; |
| 71 |
sleep 1; |
| 72 |
} |
| 73 |
EOF |
| 74 |
--exec echo "restart:--partial_revokes=OFF" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect |
| 75 |
--enable_reconnect |
| 76 |
--source include/wait_until_connected_again.inc |
| 77 |
--let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err |
| 78 |
--let $assert_select=.* \[Warning\] \[[^]]*\] \[[^]]*\] At least one partial revoke .* Turning ON the system variable .* |
| 79 |
--let $assert_text= Found the expected warning line in the server log. |
| 80 |
--let $assert_count=1 |
| 81 |
--source include/assert_grep.inc |
| 82 |
--echo # system variable must be ON. Partial revokes must exists. |
| 83 |
SHOW VARIABLES LIKE 'partial_revokes'; |
| 84 |
SHOW GRANTS FOR foo; |
| 85 |
DROP USER foo; |
| 86 |
--echo # 5. @@partial_revokes can be turned OFF after there exists no |
| 87 |
--echo # partial revokes in the user_attributes table |
| 88 |
SET GLOBAL partial_revokes = OFF; |
| 89 |
--echo # 6. After the Flush privileges, SHOW stmts should display correct information. |
| 90 |
--echo # |
| 91 |
CREATE USER bar; |
| 92 |
GRANT SELECT ON *.* TO bar; |
| 93 |
UPDATE mysql.user SET user_attributes = '{"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]}' WHERE USER = 'bar'; |
| 94 |
FLUSH PRIVILEGES; |
| 95 |
SHOW VARIABLES LIKE 'partial_revokes'; |
| 96 |
SHOW GRANTS FOR bar; |
| 97 |
--connect(bar_con, localhost, bar,,,$MASTER_MYPORT) |
| 98 |
--error ER_TABLEACCESS_DENIED_ERROR |
| 99 |
SELECT * FROM mysql.user; |
| 100 |
--connection default |
| 101 |
--disconnect bar_con |
| 102 |
DROP USER bar; |
| 103 |
--echo # 7. Adding the restriction through DML with persisted partial_revokes=OFF |
| 104 |
--echo # fails server to restart. |
| 105 |
--let MYSQLD_DATADIR = `select @@datadir` |
| 106 |
--let MYSQLD_LOG=$MYSQL_TMP_DIR/server.log |
| 107 |
CREATE USER baz; |
| 108 |
SET PERSIST partial_revokes = OFF; |
| 109 |
GRANT SELECT ON *.* TO baz; |
| 110 |
UPDATE mysql.user SET user_attributes = '{"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]}' WHERE USER = 'baz'; |
| 111 |
--echo # Restart server with partial_revokes=OFF. |
| 112 |
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect |
| 113 |
--shutdown_server |
| 114 |
--source include/wait_until_disconnected.inc |
| 115 |
|
| 116 |
--echo # Try starting the server: must fail due to persist variable. |
| 117 |
--error 1 |
| 118 |
--exec $MYSQLD --console --gdb --datadir=$MYSQLD_DATADIR --port=$MYSQLD_PORT --socket=$MYSQLD_SOCKET >> $MYSQLD_LOG 2>&1 |
| 119 |
--remove_file $MYSQLD_LOG |
| 120 |
# Remove the persist option to restart the server |
| 121 |
--remove_file $MYSQLD_DATADIR/mysqld-auto.cnf |
| 122 |
--exec echo "restart:" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect |
| 123 |
--enable_reconnect |
| 124 |
--source include/wait_until_connected_again.inc |
| 125 |
--echo # system variable must be ON. Partial revokes must exists. |
| 126 |
SHOW VARIABLES LIKE 'partial_revokes'; |
| 127 |
SHOW GRANTS FOR baz; |
| 128 |
DROP USER baz; |
| 129 |
--echo --------Toggling the system variable tests end -------------------------- |
| 130 |
|
| 131 |
--echo ------------------------------------------------------------------------- |
| 132 |
--echo # Start the server with --skip-grant-tables option |
| 133 |
--echo ------------------------------------------------------------------------- |
| 134 |
--let $restart_parameters = "restart:--skip-grant-tables" |
| 135 |
--let $wait_for_tcpsocket_status = no_wait |
| 136 |
--source include/restart_mysqld.inc |
| 137 |
--enable_reconnect |
| 138 |
--source include/wait_until_connected_again.inc |
| 139 |
SET GLOBAL partial_revokes = ON; |
| 140 |
--let $restart_parameters = restart: |
| 141 |
--source include/restart_mysqld.inc |
| 142 |
--enable_reconnect |
| 143 |
--source include/wait_until_connected_again.inc |
| 144 |
--echo # |
| 145 |
--echo # Make the variable persitent this time |
| 146 |
--echo # |
| 147 |
SET PERSIST partial_revokes = ON; |
| 148 |
--let $restart_parameters = "restart:--skip-grant-tables" |
| 149 |
--let $wait_for_tcpsocket_status = no_wait |
| 150 |
--source include/restart_mysqld.inc |
| 151 |
--enable_reconnect |
| 152 |
--source include/wait_until_connected_again.inc |
| 153 |
RESET PERSIST; |
| 154 |
--let $restart_parameters = restart: |
| 155 |
--source include/restart_mysqld.inc |
| 156 |
--enable_reconnect |
| 157 |
--source include/wait_until_connected_again.inc |
| 158 |
--echo ------------ End test with --skip-grant-tables option ------------------- |
| 159 |
|
| 160 |
--disable_query_log |
| 161 |
--disable_warnings |
| 162 |
eval SET GLOBAL partial_revokes = $saved_partial_revokes; |
| 163 |
--enable_warnings |
| 164 |
--enable_query_log |
| 165 |
# Wait till all disconnects are completed |
| 166 |
--source include/wait_until_count_sessions.inc |
| 167 |
call mtr.add_suppression(".*At least one partial revoke .* Turning ON the system variable .*"); |