MySQL Security – Password Management
When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Data Masking & De-Identification, Firewall, Password Validation Plugin, User Account Locking, The Connection-Control Plugins, etc…
Some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL Security series, we will see how to establish a policy for password expiration with MySQL 5.7 Password Management.
Password Management
Basic password policy practices teaches us :
- Each user must have a password
- A user’s password should be changed periodically
MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.
Let’s have a closer look!
|
1 |
$ mysql -u root -p |
|
1 2 3 4 5 6 7 |
mysql {root}> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.7.21-log | +------------+ |
Basically there are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password expiration policy.
Let’s play with some of them.
PASSWORD EXPIRE
Force user to change its password at the first connection.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Mark the password expired so that the user must choose a new one at the first connection to the server mysql {root}> CREATE USER 'aUser'@'localhost' IDENTIFIED BY 'AutoGeneratedPwd' PASSWORD EXPIRE; SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser'\G *************************** 1. row *************************** user: aUser host: localhost password_lifetime: NULL password_expired: Y password_last_changed: 2018-02-08 14:22:24 |
Note that password_expired column is Y.
This new MySQL user will be able to connect to the server but he must reset its password.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ mysql -u aUser -p ... mysql {aUser}> ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ALTER USER 'aUser'@'localhost' IDENTIFIED BY 'new_passw0rd'; Query OK, 0 rows affected (0.00 sec) SELECT USER(); +-----------------+ | USER() | +-----------------+ | aUser@localhost | +-----------------+ |
Column password_expired is now N.
|
1 2 3 4 5 6 7 8 |
mysql {root}> SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser'\G *************************** 1. row *************************** user: aUser host: localhost password_lifetime: NULL password_expired: N password_last_changed: 2018-02-08 14:23:50 |
PASSWORD EXPIRE INTERVAL N DAY
Force user to change its password every N days.
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql {root}> -- Require that a new password be chosen every 90 days: CREATE USER 'aNewUser'@'localhost' IDENTIFIED BY 'n3w_password' PASSWORD EXPIRE INTERVAL 90 DAY; SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aNewUser'\G *************************** 1. row *************************** user: aNewUser host: localhost password_lifetime: 90 password_expired: N password_last_changed: 2018-02-08 14:24:53 |
Note that password_lifetime column is 90.
After 90 days any statement will generate error 1820 :
|
1 2 3 4 |
$ mysql -u aNewUser -p mysql {aNewUser}> SELECT USER(); ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. |
Password could be reset with ALTER USER command :
|
1 2 |
mysql {root}> ALTER USER 'aNewUser'@'localhost' IDENTIFIED BY '4noth3r_password' PASSWORD EXPIRE INTERVAL 90 DAY; |
PASSWORD EXPIRE NEVER
It’s also possible to disable password expiration for an account
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql {root}> -- Disables password expiration for the account so that its password never expires. CREATE USER 'pingDB'@'localhost' IDENTIFIED BY 'new_p4ssword' PASSWORD EXPIRE NEVER; SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'pingDB'\G *************************** 1. row *************************** user: pingDB host: localhost password_lifetime: 0 password_expired: N password_last_changed: 2018-02-08 14:29:43 |
Note that password_lifetime column is 0.
PASSWORD EXPIRE DEFAULT
This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.
In MySQL 5.7 it applies to accounts that use MySQL built-in authentication methods (accounts that use an authentication plugin of mysql_native_password or sha256_password).
The default default_password_lifetime value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.
default_password_lifetime can be changed at runtime with SET GLOBAL command. However it must be set in the MySQL configuration file for persistence.
e.g.
|
1 2 3 4 5 6 |
$ cat my.cnf [mysqld] # global policy that passwords have a lifetime of approximately 3 months default_password_lifetime=90 ... |
e.g. Create a user account with the default global password policy (90 days)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> -- default_password_lifetime set to 90 days SET GLOBAL default_password_lifetime = 90; SHOW VARIABLES LIKE 'default_password_lifetime'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | default_password_lifetime | 90 | +---------------------------+-------+ -- Sets the account so that the global expiration policy applies, as specified by the default_password_lifetime system variable. CREATE USER 'dba'@'localhost' IDENTIFIED BY 'new_pas5word' PASSWORD EXPIRE DEFAULT; |
In order to go further
MySQL Security Series
- Password Validation Plugin
- Password Management
- User Account Locking
- The Connection-Control Plugins
- Enterprise Audit
- Enterprise Transparent Data Encryption (TDE)
- Enterprise Firewall
- Enterprise Data Masking and De-Identification
Reference Manual
MySQL Security
Thanks for using MySQL!
Watch my videos on my YouTube channel and subscribe.
Thanks for using MySQL!
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] Password Management […]
[…] data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Management, Password Validation Plugin, […]
[…] data including some advanced features like Transparent Data Encryption aka TDE, Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]
[…] your data including some advanced features like Transparent Data Encryption aka TDE, Firewall, Password Management, Password Validation Plugin, […]
[…] / plugins in order to protect your data including some advanced features like Audit, TDE, Password Management, Password Validation Plugin, User Account Locking, […]
[…] / plugins in order to protect your data including some advanced features like Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]
[…] Password Management : Gérer la politique de renouvellement des mots de passe. […]
[…] like Transparent Data Encryption aka TDE, Audit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, […]
[…] MySQL Security – Password Management […]
[…] Password Management […]
[…] Password Management […]
[…] Password Management […]
[…] Password Management […]
[…] Password Management […]