dasini.net – Diary of a MySQL expert

When thinking about security within a MySQL installation, you can 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 or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

Basic password policy practices teaches us :

• Each user must have a password
• A user’s password should be changed periodically

Indeed this is a good start !

What if MySQL make your life easier by helping you to create user with strong secure password?
Well it is now possible in MySQL 8.0 🙂

---

TL;DR

MySQL has the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords.

---

A DBA can use CREATE USER, ALTER USER or SET PASSWORD for generate random passwords for user accounts.

Let’s have a quick look using MySQL 8.0

MySQL SQL> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19    |
+-----------+

Create a user account

To create a new MySQL user account with a random password use the statement CREATE USER with the clause IDENTIFIED BY RANDOM PASSWORD :

MySQL SQL> 
CREATE USER aUser@localhost IDENTIFIED BY RANDOM PASSWORD;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | XtByqo%asj81CJmM[dfC |
+-------+-----------+----------------------+

Modify a user account

To assign a new random password to a MySQL user account use the statement ALTER USER with the clause IDENTIFIED BY RANDOM PASSWORD :

MySQL SQL> 
ALTER USER aUser@localhost IDENTIFIED BY RANDOM PASSWORD;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | Sn!b%6(Iu>{34GIOj>vS |
+-------+-----------+----------------------+

Assign a password

Another way to assign a new random password to a MySQL user account is to use the statement SET PASSWORD with the clause TO RANDOM :

MySQL SQL> 
SET PASSWORD FOR aUser@localhost TO RANDOM;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | 7,ln_3HXG<b8Kvw!o&u, |
+-------+-----------+----------------------+

Please note that by default, generated random passwords have a length of 20 characters.
This length is controlled by the generated_random_password_length system variable, which has a range from 5 to 255.

To Go Further

Reference Manual

• Random Password Generation
• generated_random_password_length

MySQL Security Serie (1st edition)

• 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

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI