Debian Codegraph @

mysql-test/suite/auth_sec/r/password_expiry.result

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