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
169
170
171
172
173
|
create user user1@localhost;
create user user2@localhost;
#
# Only privileged users should be able to lock/unlock.
#
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;
alter user user2@localhost account lock;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
disconnect con1;
connection default;
#
# ALTER USER USER1 ACCOUNT LOCK should deny the connection of user1,
# but it should allow user2 to connect.
#
alter user user1@localhost account lock;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
connect con2,localhost,user2;
disconnect con2;
connection default;
alter user user1@localhost account unlock;
#
# Passing an incorrect user should return an error unless
# IF EXISTS is used
#
alter user inexistentUser@localhost account lock;
ERROR HY000: Operation ALTER USER failed for 'inexistentUser'@'localhost'
alter user if exists inexistentUser@localhost account lock;
Warnings:
Error 1133 Can't find any matching row in the user table
Note 1396 Operation ALTER USER failed for 'inexistentUser'@'localhost'
#
# Passing an existing user to CREATE should not be allowed
# and it should not change the locking state of the current user
#
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
create user user1@localhost account lock;
ERROR HY000: Operation CREATE USER failed for 'user1'@'localhost'
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
#
# Passing multiple users should lock them all
#
alter user user1@localhost, user2@localhost account lock;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
connect(localhost,user2,,test,MYSQL_PORT,MYSQL_SOCK);
connect con2,localhost,user2;
ERROR HY000: Access denied, this account is locked
alter user user1@localhost, user2@localhost account unlock;
#
# The locking state is preserved after acl reload
#
alter user user1@localhost account lock;
flush privileges;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
alter user user1@localhost account unlock;
#
# JSON functions on global_priv reflect the locking state of an account
#
alter user user1@localhost account lock;
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
host user JSON_VALUE(Priv, '$.account_locked')
localhost user1 1
alter user user1@localhost account unlock;
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
host user JSON_VALUE(Priv, '$.account_locked')
localhost user1 0
#
# SHOW CREATE USER correctly displays the locking state of an user
#
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
alter user user1@localhost account lock;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK
alter user user1@localhost account unlock;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
create user newuser@localhost account lock;
show create user newuser@localhost;
CREATE USER for newuser@localhost
CREATE USER `newuser`@`localhost` ACCOUNT LOCK
drop user newuser@localhost;
#
# Users should be able to lock themselves
#
grant CREATE USER on *.* to user1@localhost;
connect con1,localhost,user1;
connection con1;
alter user user1@localhost account lock;
disconnect con1;
connection default;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
alter user user1@localhost account unlock;
#
# Users should be able to unlock themselves if the connections
# had been established before the accounts were locked
#
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;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
disconnect con1;
connection default;
#
# COM_CHANGE_USER should return error if the destination
# account is locked
#
alter user user1@localhost account lock;
ERROR HY000: Access denied, this account is locked
#
# MDEV-24098 SHOW CREATE USER invalid for both PASSWORD EXPIRE and
# and LOCKED
#
alter user user1@localhost PASSWORD EXPIRE;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
drop user user1@localhost;
#
# MDEV-24098 CREATE USER/ALTER USER PASSWORD EXPIRE/LOCK in
# either order.
#
create user user1@localhost PASSWORD EXPIRE ACCOUNT LOCK;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
drop user user1@localhost;
create user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
alter user user1@localhost PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
alter user user1@localhost PASSWORD EXPIRE INTERVAL 60 DAY ACCOUNT UNLOCK;
select * from mysql.global_priv where user='user1';
Host User Priv
localhost user1 {"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":false,"password_last_changed":0,"password_lifetime":60}
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 60 DAY
drop user user1@localhost;
drop user user2@localhost;
|
