4

MySQL 8: Random Password Generator

 3 years ago
source link: https://www.percona.com/blog/mysql-8-random-password-generator/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
MySQL 8: Random Password Generator

MySQL 8 Random Password GeneratorAs 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.

Shell
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:

Shell
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:

Shell
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.

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!

Related

Enhanced Password Management Systems in MySQL 8: Part 1September 22, 2021In "Insight for DBAs"

Using MySQL 8 Dual PasswordsJuly 1, 2021In "Insight for DBAs"

MySQL 8: Account LockingOctober 5, 2021In "Insight for DBAs"


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK