Debian Codegraph @

mysql-test/suite/auth_sec/t/partial_revokes_add_remove.test

raw
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 .*");