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
174
175
176
177
178
179
180
181
|
######################################################
Authentication cases
######################################################
#### Honouring the value of password_lifetime, below test checks if
#### password expiry is working when expiry date passed. Use SET PASSWORD
#### for an expired user which honours the value of password_lifetime
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
password_lifetime=2 WHERE user='u1';
FLUSH PRIVILEGES;
#### Connection with u1 will succeed in sand box mode, but no statements
#### can be executed except SET PASSWORD
# Setting variables should work
SELECT 'Password_Expired_SandBoxMode_Test';
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SET PASSWORD = 'u1_new';
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Honouring the value of password_lifetime, check if password expiry
#### is working when password is active.
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 2 DAY),
password_lifetime=3 WHERE user='u1';
FLUSH PRIVILEGES;
#### Connection will succeed, user should be able to execute statements
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Set the value of password_lifetime to 0 and check its validity.
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 2 DAY),
password_lifetime=0 WHERE user='u1';
FLUSH PRIVILEGES;
#### Connection will succeed, user should be able to execute statements
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Honouring the value of default_password_lifetime, check if password
#### expiry is working when password is expired.
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
password_lifetime=null WHERE user='u1';
SET GLOBAL default_password_lifetime = 2;
FLUSH PRIVILEGES;
#### Connection will succeed, user should be able to execute statements
SELECT 'Password_Expired_SandBoxMode_Test';
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Honouring the value of default_password_lifetime, check if
#### password expiry is working when password is expired.
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 2 DAY),
password_lifetime=null WHERE user='u1';
SET GLOBAL default_password_lifetime = 3;
FLUSH PRIVILEGES;
#### Connection will succeed, user should be able to execute statements
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Check if NULL value can be set to default_password_lifetime
SET GLOBAL default_password_lifetime = null;
ERROR 42000: Incorrect argument type to variable 'default_password_lifetime'
#### Ensure that existing sessions are not disturbed due to change in
#### global value of default_password_lifetime.
CREATE USER u1 IDENTIFIED by 'u1';
#### Connection will succeed, user should be able to execute statements
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
password_lifetime=null WHERE user='u1';
SET GLOBAL default_password_lifetime = 2;
FLUSH PRIVILEGES;
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Use SET PASSWORD for an expired user which honours the value of
#### variable default_password_lifetime.
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
password_lifetime=null WHERE user='u1';
SET GLOBAL default_password_lifetime = 2;
FLUSH PRIVILEGES;
#### Connection will succeed in sand box mode,
#### but no statements can be executed except SET PASSWORD
SELECT 'Password_Expired_SandBoxMode_Test';
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SET PASSWORD = 'u1_new';
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### For an expired user where password_expired is Y, ensure that it
#### does not execute anything except SET PASSWORD.
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_expired='Y' WHERE user='u1';
FLUSH PRIVILEGES;
#Below statement should not affect the value of password_expired column
ALTER USER 'u1' PASSWORD EXPIRE INTERVAL 5 DAY;
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
Y
DROP USER u1;
#### Check if SET PASSWORD resets the value of column
#### password_expired to N when it is Y.
CREATE USER u1 IDENTIFIED by 'u1';
ALTER USER 'u1' PASSWORD EXPIRE;
#Expiry status of u1 should be Y
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
Y
SET PASSWORD = 'u1_new';
#Expiry status of u1 should be N
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Try updating the newly introduced columns and try setting the
#### global variable default_password_lifetime with an user having
#### insufficient privilege.
CREATE USER u1 IDENTIFIED by 'u1';
SET GLOBAL default_password_lifetime = 2;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
password_lifetime=null WHERE user='u1';
ERROR 42000: UPDATE command denied to user 'u1'@'localhost' for table 'user'
DROP USER u1;
#### One year password expiration check
CREATE USER u1 IDENTIFIED by 'u1';
UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 361 DAY),
password_lifetime=null WHERE user='u1';
SET GLOBAL default_password_lifetime = 360;
FLUSH PRIVILEGES;
# Connection will succeed in sand box mode,
# but no statements can be executed except SET PASSWORD
SELECT 'Password_Expired_SandBoxMode_Test';
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SET PASSWORD = 'u1_new';
# Setting variables should work
SELECT 'Normal_Statement_Can_Be_Executed';
Normal_Statement_Can_Be_Executed
Normal_Statement_Can_Be_Executed
SELECT password_expired FROM mysql.user WHERE user='u1';
password_expired
N
DROP USER u1;
#### Cleanup statements
SET GLOBAL default_password_lifetime = default;
|
