As part of my ongoing focus on MySQL 8 user and password management, I’ve covered how using the new dual passwords feature can reduce the overall DBA workload and streamline the management process. I’ve also covered how the new password failure tracking features can enable the locking of an account with too many failed password attempts (see MySQL 8: Account Locking).
There are other new and useful features that have been added to the user management capabilities in MySQL 8 however, and an often overlooked change was the implementation of a random password generator. First introduced in MySQL 8.0.18, with this feature, CREATE USER, ALTER USER, and SET PASSWORD statements have the capability of generating random passwords for user accounts as an alternative to explicit administrator specified passwords.
Usage of MySQL 8 Random Password Generator
By default, all MySQL-generated random user/account passwords have a length of 20 characters. This can be changed, however, using the ‘generated_random_password_length’ system variable. With a valid range of 5 to 255, this dynamic variable can be assigned on a global or session-level and determines the overall password length of the randomly generated password.
1 2 3 4 5 6 7 | mysql> SHOW variables LIKE 'generated_random_password_length'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | generated_random_password_length | 20 | +----------------------------------+-------+ 1 row in set (0.01 sec) |
Once a random password has been generated for a given user account, the statement stores the password in the ‘mysql.user’ system table, hashed appropriately for the authentication plugin. The cleartext ‘generated password’ is returned in the result set along with the ‘user’ and ‘host’ so that information is available to the user or application. See below examples:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> CREATE USER 'percona'@'localhost' IDENTIFIED BY RANDOM PASSWORD; +---------+-----------+----------------------+ | user | host | generated password | +---------+-----------+----------------------+ | percona | localhost | k%RJ51/kA>,B(74;DBq2 | +---------+-----------+----------------------+ 1 row in set (0.02 sec) mysql> ALTER USER 'percona'@'localhost' IDENTIFIED BY RANDOM PASSWORD; +---------+-----------+----------------------+ | user | host | generated password | +---------+-----------+----------------------+ | percona | localhost | eX!EOssQ,(Hn4dOdw6Om | +---------+-----------+----------------------+ 1 row in set (0.01 sec) mysql> SET PASSWORD FOR 'percona'@'localhost' TO RANDOM; +---------+-----------+----------------------+ | user | host | generated password | +---------+-----------+----------------------+ | percona | localhost | 5ohXP2LBTTPzJ+7oEDL4 | +---------+-----------+----------------------+ 1 row in set (0.00 sec) |
Logging
The clear text generated password is logged only in hashed form, so it is never available in plain text anywhere other than the initial result set from the user statement (as above). The authentication plugin is also named in the binlog alongside the hashed password value. Below are a couple of examples that have been extracted from the MySQL binlog from the ‘percona’@’localhost’ user that we created and altered earlier:
1 2 | CREATE USER 'percona'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*5978ACEA46C1B81C7BEE2D1470ED1B002FE6840B' ALTER USER 'percona'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*2994ECB14E21A8333C8C2DEDF38311EB714D500C' |
In Closing
Human imagination is often a limiting factor in choosing secure passwords. The random password capability introduced in MySQL 8.0.18 ensures that there is a standardized method for truly random and secure passwords in your database environment.
