1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
#
# Test user account locking
#
--source include/not_embedded.inc
create user user1@localhost;
create user user2@localhost;
--echo #
--echo # Only privileged users should be able to lock/unlock.
--echo #
alter user user1@localhost account lock;
alter user user1@localhost account unlock;
create user user3@localhost account lock;
drop user user3@localhost;
connect(con1,localhost,user1);
connection con1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
alter user user2@localhost account lock;
disconnect con1;
connection default;
--echo #
--echo # ALTER USER USER1 ACCOUNT LOCK should deny the connection of user1,
--echo # but it should allow user2 to connect.
--echo #
alter user user1@localhost account lock;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_ACCOUNT_HAS_BEEN_LOCKED
connect(con1,localhost,user1);
connect(con2,localhost,user2);
disconnect con2;
connection default;
alter user user1@localhost account unlock;
--echo #
--echo # Passing an incorrect user should return an error unless
--echo # IF EXISTS is used
--echo #
--error ER_CANNOT_USER
alter user inexistentUser@localhost account lock;
alter user if exists inexistentUser@localhost account lock;
--echo #
--echo # Passing an existing user to CREATE should not be allowed
--echo # and it should not change the locking state of the current user
--echo #
show create user user1@localhost;
--error ER_CANNOT_USER
create user user1@localhost account lock;
show create user user1@localhost;
--echo #
--echo # Passing multiple users should lock them all
--echo #
alter user user1@localhost, user2@localhost account lock;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_ACCOUNT_HAS_BEEN_LOCKED
connect(con1,localhost,user1);
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_ACCOUNT_HAS_BEEN_LOCKED
connect(con2,localhost,user2);
alter user user1@localhost, user2@localhost account unlock;
--echo #
--echo # The locking state is preserved after acl reload
--echo #
alter user user1@localhost account lock;
flush privileges;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_ACCOUNT_HAS_BEEN_LOCKED
connect(con1,localhost,user1);
alter user user1@localhost account unlock;
--echo #
--echo # JSON functions on global_priv reflect the locking state of an account
--echo #
alter user user1@localhost account lock;
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
alter user user1@localhost account unlock;
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
--echo #
--echo # SHOW CREATE USER correctly displays the locking state of an user
--echo #
show create user user1@localhost;
alter user user1@localhost account lock;
show create user user1@localhost;
alter user user1@localhost account unlock;
show create user user1@localhost;
create user newuser@localhost account lock;
show create user newuser@localhost;
drop user newuser@localhost;
--echo #
--echo # Users should be able to lock themselves
--echo #
grant CREATE USER on *.* to user1@localhost;
connect(con1,localhost,user1);
connection con1;
alter user user1@localhost account lock;
disconnect con1;
connection default;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_ACCOUNT_HAS_BEEN_LOCKED
connect(con1,localhost,user1);
alter user user1@localhost account unlock;
--echo #
--echo # Users should be able to unlock themselves if the connections
--echo # had been established before the accounts were locked
--echo #
grant CREATE USER on *.* to user1@localhost;
connect(con1,localhost,user1);
alter user user1@localhost account lock;
connection con1;
alter user user1@localhost account unlock;
show create user user1@localhost;
disconnect con1;
connection default;
--echo #
--echo # COM_CHANGE_USER should return error if the destination
--echo # account is locked
--echo #
alter user user1@localhost account lock;
--error ER_ACCOUNT_HAS_BEEN_LOCKED
--change_user user1
--echo #
--echo # MDEV-24098 SHOW CREATE USER invalid for both PASSWORD EXPIRE and
--echo # and LOCKED
--echo #
alter user user1@localhost PASSWORD EXPIRE;
show create user user1@localhost;
drop user user1@localhost;
--echo #
--echo # MDEV-24098 CREATE USER/ALTER USER PASSWORD EXPIRE/LOCK in
--echo # either order.
--echo #
create user user1@localhost PASSWORD EXPIRE ACCOUNT LOCK;
show create user user1@localhost;
drop user user1@localhost;
create user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE;
show create user user1@localhost;
alter user user1@localhost PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ;
show create user user1@localhost;
alter user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
show create user user1@localhost;
# note output needs to be corrected by MDEV-24114: password expire users cannot be unexpired
alter user user1@localhost PASSWORD EXPIRE INTERVAL 60 DAY ACCOUNT UNLOCK;
select * from mysql.global_priv where user='user1';
show create user user1@localhost;
drop user user1@localhost;
drop user user2@localhost;
|
