MySQL Security – Password Management

March 7, 2018

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 TDEAudit, Firewall, Password Validation PluginUser Account Locking, The Connection-Control Plugins, etc…

MySQL Security

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 5.7 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!

 

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.

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.

 

Column password_expired is now N.

 

 

PASSWORD EXPIRE INTERVAL N DAY

Force user to change its password every N days.

Note that password_lifetime column is 90.

 

After 90 days any statement will generate error 1820 :

 

Password could be reset with ALTER USER command :

 

 

PASSWORD EXPIRE NEVER

It’s also possible to disable password expiration for an account

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.

e.g. Create a user account with the default global password policy (90 days)

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

6 Responses to “MySQL Security – Password Management”

  1. […] Password Management […]

  2. […] data including some advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, […]

  3. […] data including some advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

  4. […] your data including some advanced features like Transparent Data Encryption aka TDE,  Firewall, Password Management, Password Validation Plugin, […]

  5. […] / plugins in order to protect your data including some advanced features like Audit, TDE, Password Management, Password Validation Plugin, User Account Locking, […]

  6. […] / plugins in order to protect your data including some advanced features like  Audit,  Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

Leave a Reply