Interview Questions - Passwords and Password Policy in MySQL Server

Password Update and Reuse Policy

Is there any feature for keeping track of user password history?

MySQL 8.0 is able to keep track of the password history and to deny if the same password has been used within a given number of changes and days. The global defaults can be specified with the following options:

password_history - The minimum number of password changes that must occur before password can be reused.
password_reuse_interval - The minimum number of days that must pass before a password can be reused.

The settings can also be specified per account using CREATE USER or ALTER USER, for example:

mysql> ALTER USER 'myuser'@'localhost' PASSWORD HISTORY 5;

Is there any option for a minimum period before a password can be reused?

This is supported in MySQL 8.0. See also the previous question and answer.

Can a user password be expired after a certain number of days?

Yes, this is supported in MySQL 5.7 and later. The global default expiration time is specified with the default_password_lifetime option - the value is in days. You can also set the value explicitly for an account using CREATE USER or ALTER USER, for example:

mysql> ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Yes this is possible, and indeed that is what is the default for the root@localhost user when initializing the data directory in MySQL 5.7 (and for some packaging formats in 5.6).

To force a change of password on the first connection, simply create the account with an expired password, for example:

mysql> CREATE USER testuser@localhost IDENTIFIED BY 'initial_password' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

Then connect using the initial password:

shell$ mysql --host=127.0.0.1 --port=3306 --user=testuser --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
...
mysql> SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

So the only query allowed is to change the password. Once that is done, all statements allowed for the user can be executed.

Using Passwords

Is there security masking of passwords?

Yes, in most cases passwords are masked or changed to the hashed version of the password for example when logging to the slow query log, general log, audit log, binary log, etc. However note that if the statement using the password is provided as part of a plugin, then it will in general not be masked.

Additionally the log-raw option can be used to override the masking. However, it can only be changed by updating the MySQL configuration file or specifying the option on the command line.

Passwords specified for a client on the command-line will trigger a warning, and MySQL will mask the password as soon as possible. For example:

shell$ mysql --host=127.0.0.1 --port=3306 --user=testuser --password=password
mysql: [Warning] Using a password on the command line interface can be insecure.
...

Then:

shell$ ps auxfww | grep password
myuser 17406 0.0 0.0 135248 6596 pts/1 S+ 10:59 0:00 | \_ mysql --host=127.0.0.1 --port=3306 --user=testuser --password=x xxxxxx
myuser 17417 0.0 0.0 112668 2184 pts/2 S+ 10:59 0:00 | \_ grep password

Is there encryption transmission of passwords?

Yes, MySQL supports using SSL for encrypting the connections. This is enabled by default for commercial builds (but using self-signed certificates) in MySQL 5.7 and for all Oracle provided builds in MySQL 8.0 (still self-signed certificates).

Other Questions

In MySQL 8.0.19 you can use the FAILED_LOGIN_ATTEMPTS password option when creating or altering users to specify the maximum number of failed login attempts that are allowed before the account is locked. You can additionally use the PASSWORD_LOCK_TIME password option to specify how long time the account will be locked before it can be used again. PASSWORD_LOCK_TIME can be set to the number of days to lock or UNBOUNDED to lock it indefinitely. The default is to lock for 0 days, so you must set both FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME to use the feature. For example:

mysql> CREATE USER myuser@localhost
              IDENTIFIED BY 'password'
              FAILED_LOGIN_ATTEMPTS 3
              PASSWORD_LOCK_TIME UNBOUNDED;
Query OK, 0 rows affected (0.01 sec)

An administrator can unlock a specific account using ALTER USER, like:

mysql> ALTER USER myuser@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

All temporarily locked accounts are unlocked when MySQL is restarted or FLUSH PRIVILEGES is executed. In older releases, the closest built-in feature is the connection-control plugin which supports adding a delay to the response for each failed authentication. The connection-control plugin is available in 5.6.35+, 5.7.17+, and 8.0+.

To lock the account after 3 failed attempts, the best you can do is to install the connection-control plugin. This includes the information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table. This table will include all accounts where the last connection attempt resulted in a failed authentication and it will include the number of consecutive failures. Once a valid connection is made, the account is removed from the table. That makes it easier to write a custom script to automatically block accounts. The plugin can also be used to introduce a delay when the authentication for an account fails.

Using that you can have an event or script periodically check if any accounts have crossed your threshold and issue an ALTER USER to lock the account. There will however be a race condition as - as mentioned - the account is removed from the table as soon there is one valid connection.

Is there encrypted storage of passwords?

The passwords are stored as a hash. The algorithm used to generate the hash depends on the chosen password authentication plugin:

mysql_native_password - uses a sha1 hash. This is the default in MySQL 4.1 through 5.7. Note that sha1 hashes are not salted and are no longer considered secure.
sha256_password - salted sha256 hashes uses 5000 iterations. These are available in MySQL 5.6 and later. It is recommended to use MySQL 5.7 or later with sha256_password as the account management functions are much better suited for handling other authentication plugins than mysql_native_password.
caching_sha2_password - similar to sha256_password hashes but adds a caching feature to improve the performance. This is available starting in MySQL 8.0 and is also the default in MySQL 8.0.

Note: sha256_password and caching_sha2_password requires the password exchange between the connecting client and the server to happen over a secure connection.

Yes, there are several password related feature that has been implemented for MySQL 8.0. An overview includes:

New authentication plugin - caching_sha2_password - that gives a similar security to the sha256_password plugin but with better performance.
Support for password history both based on number of password changed and minimum age between reusing a password.
Both Commercial and Community builds are now using OpenSSL by default for encrypted connections (in 5.7 it was only the Commercial builds). This also means that in both cases SSL is now enabled by default using a self-signed certificate.