MySQL Security – Random Password Generation
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…
![MySQL Security](https://i0.wp.com/dasini.net/blog/wp-content/uploads/MySQL_DB_Lock2.png?ssl=1)
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
1 2 3 4 5 6 |
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 :
1 2 3 4 5 6 7 |
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 :
1 2 3 4 5 6 7 |
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 :
1 2 3 4 5 6 7 |
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
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!
Watch my videos on my YouTube channel and subscribe.
Thanks for using MySQL!
![Olivier DASINI](https://i0.wp.com/dasini.net/blog/wp-content/uploads/daz_presenter_494x429_clean_BW-color.png?resize=100%2C100)
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary […]
[…] like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Verification-Required Policy, Failed-Login Tracking and […]
[…] like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Dual […]
[…] like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Failed-Login Tracking and Temporary Account […]