Debian Codegraph @

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

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6
7 CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS');
8
9 --echo # -----------------------------------------------------------------------
10
11 --echo # Begin : Tests for ALTER USER
12
13 CREATE USER userX, userY, userZ;
14 --source include/save_binlog_position.inc
15
16 SELECT user, authentication_string FROM mysql.user;
17
18 --echo # Case 1 : Modify multiple users
19 ALTER USER userX IDENTIFIED BY 'abcd',
20 userY IDENTIFIED BY 'efgh',
21 userZ IDENTIFIED BY 'ijkl';
22
23 --let $event= !Q(ALTER USER.*userX.*userY.*userZ.*)
24 --source ../include/auth_sec_assert_binlog_events.inc
25 --source include/save_binlog_position.inc
26
27 --exec $MYSQL -uuserX -pabcd -e "SELECT CURRENT_USER()" 2>&1
28 --exec $MYSQL -uuserY -pefgh -e "SELECT CURRENT_USER()" 2>&1
29 --exec $MYSQL -uuserZ -pijkl -e "SELECT CURRENT_USER()" 2>&1
30
31 --echo # Case 2 : Try to modify property of an non-existing user
32
33 --error ER_CANNOT_USER
34 ALTER USER userW IDENTIFIED BY 'haha',
35 userX IDENTIFIED BY '',
36 userV IDENTIFIED BY 'haha';
37
38 --let $invert= 1
39 --let $event= !Q(ALTER USER.*userW.*userX.*userV.*)
40 --source ../include/auth_sec_assert_binlog_events.inc
41 --let $invert= 0
42
43 --exec $MYSQL -uuserX -pabcd -e "SELECT CURRENT_USER()" 2>&1
44 --exec $MYSQL -uuserY -pefgh -e "SELECT CURRENT_USER()" 2>&1
45 --exec $MYSQL -uuserZ -pijkl -e "SELECT CURRENT_USER()" 2>&1
46
47 --echo # Case 3 : Remove one of the entries from mysql.user but don't flush privileges.
48 DELETE FROM mysql.user WHERE user LIKE 'userX';
49
50 --source include/save_binlog_position.inc
51
52 --error ER_CANNOT_USER
53 ALTER USER userY IDENTIFIED BY '',
54 userX IDENTIFIED BY 'haha';
55
56 --echo # Check binlog : Must not have any entry
57 --let $invert= 1
58 --let $event= !Q(ALTER USER.*userY.*userX.*)
59 --source ../include/auth_sec_assert_binlog_events.inc
60 --let $invert= 0
61
62 # reload ACL caches
63 FLUSH PRIVILEGES;
64 --source include/save_binlog_position.inc
65
66 --exec $MYSQL -uuserY -pefgh -e "SELECT CURRENT_USER()" 2>&1
67 --exec $MYSQL -uuserZ -pijkl -e "SELECT CURRENT_USER()" 2>&1
68
69 --echo # Case 4 : Try to expire own password in a failing DDL. We update
70 --echo # current session's state as well. However, since DDL is going fail,
71 --echo # session's state must not be altered and user should be able to
72 --echo # execute other statements without any need to change password.
73
74 --error ER_CANNOT_USER
75 ALTER USER root@localhost, userW PASSWORD EXPIRE;
76
77 --echo # Must succeed
78 SELECT user FROM mysql.user;
79
80 --let $invert= 1
81 --let $event= !Q(ALTER USER.*root.*userW.*)
82 --source ../include/auth_sec_assert_binlog_events.inc
83 --let $invert= 0
84
85 DROP USER userY, userZ;
86
87 --source include/save_binlog_position.inc
88
89 --echo # End : Tests for ALTER USER
90
91 --echo # -----------------------------------------------------------------------
92
93 --echo # Begin : Tests for ALTER USER IF EXISTS
94
95 CALL mtr.add_suppression("Following users were specified in ALTER USER IF NOT EXISTS but they do not exist.");
96
97 CREATE USER userX, userY;
98 --source include/save_binlog_position.inc
99
100 --echo # Case 1 : Must throw warning for userW and userZ
101 ALTER USER IF EXISTS userW IDENTIFIED BY 'abcd',
102 userX IDENTIFIED BY 'efgh',
103 userY IDENTIFIED BY 'ijkl',
104 userZ IDENTIFIED BY 'mnop';
105
106 --let $event= !Q(ALTER USER.*userW.*userX.*userY.*userZ.*)
107 --source ../include/auth_sec_assert_binlog_events.inc
108 --source include/save_binlog_position.inc
109
110 --exec $MYSQL -uuserX -pefgh -e "SELECT CURRENT_USER()" 2>&1
111 --exec $MYSQL -uuserY -pijkl -e "SELECT CURRENT_USER()" 2>&1
112
113 --echo # Case 2 : Remove one of the user from mysql.user table
114 DELETE FROM mysql.user WHERE user LIKE 'userX';
115 --source include/save_binlog_position.inc
116
117 --echo # Must throw warning for userX
118 ALTER USER IF EXISTS userX IDENTIFIED BY 'abcd',
119 userY IDENTIFIED BY '';
120
121 --let $event= !Q(ALTER USER.*userX.*userY.*)
122 --source ../include/auth_sec_assert_binlog_events.inc
123
124 FLUSH PRIVILEGES;
125 --source include/save_binlog_position.inc
126
127 --echo # Case 3 : Current session's password must be expired
128 GRANT ALL ON *.* TO userY;
129 connect(conn_userY, localhost, userY,,,,,);
130 connection conn_userY;
131 --disable_warnings
132 ALTER USER IF EXISTS userY, userW PASSWORD EXPIRE;
133 --enable_warnings
134
135 --error ER_MUST_CHANGE_PASSWORD
136 SELECT user FROM mysql.user;
137
138 connection default;
139 --source include/save_binlog_position.inc
140
141 connection conn_userY;
142 --echo # Must throw warning
143 ALTER USER IF EXISTS userY IDENTIFIED BY 'abcd', userW IDENTIFIED BY 'haha';
144
145 SELECT user FROM mysql.user;
146 --let $event= !Q(ALTER USER.*userY.*userW.*)
147 --source ../include/auth_sec_assert_binlog_events.inc
148
149 connection default;
150 disconnect conn_userY;
151 DROP USER userY;
152 --source include/save_binlog_position.inc
153
154 --echo # End : Tests for ALTER USER IF EXISTS
155
156 --echo # -----------------------------------------------------------------------
157
158 # Wait till we reached the initial number of concurrent sessions
159 --source include/wait_until_count_sessions.inc