Debian Codegraph @

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

raw
1 #############################################################
2 # Purpose : To check authentication cases for WL#7131
3 # Author : Prabeen Pradhan
4 # Date : 25th Feb 2014
5 #############################################################
6
7
8 --echo ######################################################
9 --echo Authentication cases
10 --echo ######################################################
11 --echo #### Honouring the value of password_lifetime, below test checks if
12 --echo #### password expiry is working when expiry date passed. Use SET PASSWORD
13 --echo #### for an expired user which honours the value of password_lifetime
14 CREATE USER u1 IDENTIFIED by 'u1';
15 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
16 password_lifetime=2 WHERE user='u1';
17 FLUSH PRIVILEGES;
18 --echo #### Connection with u1 will succeed in sand box mode, but no statements
19 --echo #### can be executed except SET PASSWORD
20 connect (conn_u1,localhost,u1,u1,,,,SSL);
21 --echo # Setting variables should work
22 --error 1820
23 SELECT 'Password_Expired_SandBoxMode_Test';
24 SET PASSWORD = 'u1_new';
25 SELECT 'Normal_Statement_Can_Be_Executed';
26 disconnect conn_u1;
27 connect (conn_root,localhost,root,,);
28 SELECT password_expired FROM mysql.user WHERE user='u1';
29 DROP USER u1;
30
31 --echo #### Honouring the value of password_lifetime, check if password expiry
32 --echo #### is working when password is active.
33 CREATE USER u1 IDENTIFIED by 'u1';
34 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 2 DAY),
35 password_lifetime=3 WHERE user='u1';
36 FLUSH PRIVILEGES;
37 --echo #### Connection will succeed, user should be able to execute statements
38 connect (conn_u1,localhost,u1,u1,,,,SSL);
39 --echo # Setting variables should work
40 SELECT 'Normal_Statement_Can_Be_Executed';
41 disconnect conn_u1;
42 connection conn_root;
43 SELECT password_expired FROM mysql.user WHERE user='u1';
44 DROP USER u1;
45
46 --echo #### Set the value of password_lifetime to 0 and check its validity.
47 CREATE USER u1 IDENTIFIED by 'u1';
48 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 2 DAY),
49 password_lifetime=0 WHERE user='u1';
50 FLUSH PRIVILEGES;
51 --echo #### Connection will succeed, user should be able to execute statements
52 connect (conn_u1,localhost,u1,u1,,,,SSL);
53 --echo # Setting variables should work
54 SELECT 'Normal_Statement_Can_Be_Executed';
55 disconnect conn_u1;
56 connection conn_root;
57 SELECT password_expired FROM mysql.user WHERE user='u1';
58 DROP USER u1;
59
60 --echo #### Honouring the value of default_password_lifetime, check if password
61 --echo #### expiry is working when password is expired.
62 CREATE USER u1 IDENTIFIED by 'u1';
63 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
64 password_lifetime=null WHERE user='u1';
65 SET GLOBAL default_password_lifetime = 2;
66 FLUSH PRIVILEGES;
67 --echo #### Connection will succeed, user should be able to execute statements
68 connect (conn_u1,localhost,u1,u1,,,,SSL);
69 --error 1820
70 SELECT 'Password_Expired_SandBoxMode_Test';
71 disconnect conn_u1;
72 connection conn_root;
73 SELECT password_expired FROM mysql.user WHERE user='u1';
74 DROP USER u1;
75
76 --echo #### Honouring the value of default_password_lifetime, check if
77 --echo #### password expiry is working when password is expired.
78 CREATE USER u1 IDENTIFIED by 'u1';
79 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 2 DAY),
80 password_lifetime=null WHERE user='u1';
81 SET GLOBAL default_password_lifetime = 3;
82 FLUSH PRIVILEGES;
83 --echo #### Connection will succeed, user should be able to execute statements
84 connect (conn_u1,localhost,u1,u1,,,,SSL);
85 --echo # Setting variables should work
86 SELECT 'Normal_Statement_Can_Be_Executed';
87 disconnect conn_u1;
88 connection conn_root;
89 SELECT password_expired FROM mysql.user WHERE user='u1';
90 DROP USER u1;
91
92 --echo #### Check if NULL value can be set to default_password_lifetime
93 --error 1232
94 SET GLOBAL default_password_lifetime = null;
95
96 --echo #### Ensure that existing sessions are not disturbed due to change in
97 --echo #### global value of default_password_lifetime.
98 CREATE USER u1 IDENTIFIED by 'u1';
99 --echo #### Connection will succeed, user should be able to execute statements
100 connect (conn_u1,localhost,u1,u1,,,,SSL);
101 --echo # Setting variables should work
102 SELECT 'Normal_Statement_Can_Be_Executed';
103 connection conn_root;
104 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
105 password_lifetime=null WHERE user='u1';
106 SET GLOBAL default_password_lifetime = 2;
107 FLUSH PRIVILEGES;
108 connection conn_u1;
109 --echo # Setting variables should work
110 SELECT 'Normal_Statement_Can_Be_Executed';
111 disconnect conn_u1;
112 connection conn_root;
113 SELECT password_expired FROM mysql.user WHERE user='u1';
114 DROP USER u1;
115
116 --echo #### Use SET PASSWORD for an expired user which honours the value of
117 --echo #### variable default_password_lifetime.
118 CREATE USER u1 IDENTIFIED by 'u1';
119 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
120 password_lifetime=null WHERE user='u1';
121 SET GLOBAL default_password_lifetime = 2;
122 FLUSH PRIVILEGES;
123 --echo #### Connection will succeed in sand box mode,
124 --echo #### but no statements can be executed except SET PASSWORD
125 connect (conn_u1,localhost,u1,u1,,,,SSL);
126 --error 1820
127 SELECT 'Password_Expired_SandBoxMode_Test';
128 SET PASSWORD = 'u1_new';
129 --echo # Setting variables should work
130 SELECT 'Normal_Statement_Can_Be_Executed';
131 disconnect conn_u1;
132 connection conn_root;
133 SELECT password_expired FROM mysql.user WHERE user='u1';
134 DROP USER u1;
135
136 --echo #### For an expired user where password_expired is Y, ensure that it
137 --echo #### does not execute anything except SET PASSWORD.
138 CREATE USER u1 IDENTIFIED by 'u1';
139 UPDATE mysql.user SET password_expired='Y' WHERE user='u1';
140 FLUSH PRIVILEGES;
141 --echo #Below statement should not affect the value of password_expired column
142 ALTER USER 'u1' PASSWORD EXPIRE INTERVAL 5 DAY;
143 SELECT password_expired FROM mysql.user WHERE user='u1';
144 DROP USER u1;
145
146 --echo #### Check if SET PASSWORD resets the value of column
147 --echo #### password_expired to N when it is Y.
148 CREATE USER u1 IDENTIFIED by 'u1';
149 ALTER USER 'u1' PASSWORD EXPIRE;
150 --echo #Expiry status of u1 should be Y
151 SELECT password_expired FROM mysql.user WHERE user='u1';
152 connect (conn_u1,localhost,u1,u1,,,,SSL);
153 SET PASSWORD = 'u1_new';
154 disconnect conn_u1;
155 connection conn_root;
156 --echo #Expiry status of u1 should be N
157 SELECT password_expired FROM mysql.user WHERE user='u1';
158 DROP USER u1;
159
160 --echo #### Try updating the newly introduced columns and try setting the
161 --echo #### global variable default_password_lifetime with an user having
162 --echo #### insufficient privilege.
163 CREATE USER u1 IDENTIFIED by 'u1';
164 connect (conn_u1,localhost,u1,u1,,,,SSL);
165 --error 1227
166 SET GLOBAL default_password_lifetime = 2;
167 --error 1142
168 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 3 DAY),
169 password_lifetime=null WHERE user='u1';
170 disconnect conn_u1;
171 connection conn_root;
172 DROP USER u1;
173
174 --echo #### One year password expiration check
175 CREATE USER u1 IDENTIFIED by 'u1';
176 UPDATE mysql.user SET password_last_changed=DATE_SUB(NOW(), INTERVAL 361 DAY),
177 password_lifetime=null WHERE user='u1';
178 SET GLOBAL default_password_lifetime = 360;
179 FLUSH PRIVILEGES;
180 --echo # Connection will succeed in sand box mode,
181 --echo # but no statements can be executed except SET PASSWORD
182 connect (conn_u1,localhost,u1,u1,,,,SSL);
183 --error 1820
184 SELECT 'Password_Expired_SandBoxMode_Test';
185 SET PASSWORD = 'u1_new';
186 --echo # Setting variables should work
187 SELECT 'Normal_Statement_Can_Be_Executed';
188 disconnect conn_u1;
189 connection conn_root;
190 SELECT password_expired FROM mysql.user WHERE user='u1';
191 DROP USER u1;
192
193 --echo #### Cleanup statements
194 connection conn_root;
195 SET GLOBAL default_password_lifetime = default;
196 disconnect conn_root;