Password expire never not working as expected in mysql 5.6.33
Posted by: Rijo Roy
Date: April 21, 2017 05:29AM

Hello All,

One of my production environment running in Red Hat Enterprise Linux Server release 5.9 64 bit is in MySQL Community version 5.6 server

mysql> select version();
+------------+
| version() |
+------------+
| 5.6.28-log |
+------------+
1 row in set (0.00 sec)

I have got 2 users which is used by the application Team. Recently , I received a request from the application team to make the users password never expire.

While checking , I observed that the password_expired value for both the users is set as "N". I am not able to find what is the value of the system variable for default_password_lifetime in my.cnf file as well as when I check SHOW VARIABLES;

mysql> SELECT USER,HOST,PASSWORD_EXPIRED FROM MYSQL.USER where user in ('user1','user2');
+-------------+-----------+------------------+
| USER | HOST | PASSWORD_EXPIRED |
+-------------+-----------+------------------+
| user1 | localhost | N |
| user1 | % | N |
| user2 | localhost | N |
| user2 | % | N |
+-------------+-----------+------------------+
4 rows in set (0.00 sec)

When I try to alter the password expiration policy to expire never it fails throwing a syntax error like this but I am really clueless where I went wrong here or Mysql not allowing me to change it as the value for password_expired is N :

mysql> ALTER USER 'user1'@'localhost' PASSWORD EXPIRE NEVER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NEVER' at line 1

Also, it fails with syntax error for this as well:

mysql> SHOW CREATE USER FOR 'user1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER FOR 'user1'' at line 1
mysql> SHOW CREATE USER FOR 'user1'@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER FOR 'user1'@'localhost'' at line 1

I would like to get a confirmation on 2 points:

1. what is the interpretation of the value password_expired='N' for mysql version 5.6.33 i.e, whether it means the password for the respective users will never expire as the value for password_expire is N?

2. Why would MySQL throw a syntax error for ALTER USER 'user1'@'localhost' PASSWORD EXPIRE NEVER;?

3. why SHOW CREATE USER FOR 'user1'; failed to give me the DDL of the user1

FYI, I am connected as the root user.

requesting your attention and help in the said points.

Thanks
Rijo

Options: ReplyQuote


Subject
Written By
Posted
Password expire never not working as expected in mysql 5.6.33
April 21, 2017 05:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.