Building a Better CREATE USER Command

Prior to MySQL 5.7, the CREATE USER command had a number of limitations:

  • No way to set both authentication plugin and password
  • No way to disable a user
  • No way to define user resource limitations
  • No way to set a non-default password expiration policy
  • No way to require SSL/x509

All of these things could be done through other means, but typically involved other statements, such as GRANT commands. Starting with MySQL 5.7.6, these can all be done through a new and improved CREATE USER syntax.

Passwords and Authentication Plugins

The most important aspect to me, from a security perspective, is the ability to now create user accounts with non-default authentication plugins (like sha256_password) and a non-blank password:

While passwords could be assigned in subsequent statements, it certainly is bad security practice to force users to create the account without a password in the first place.

Disabled Accounts

I’ve noted previously that there are a number of use cases for accounts which cannot be accessed directly by end users. We even implemented the mysql_no_login authentication plugin in 5.6 to support these use cases. Now there’s an even better way — simply define the account as locked:

Other New Account Options

Another convenient addition is the ability to create a new account and define a non-standard password expiration policy:

Likewise, creating a new account which requires SSL no longer takes multiple statements:

Or you can limit resources for the new account in the same statement as well:

This should greatly simplify user account creation processes and scripts. A big thanks to Satish and everyone else involved in bringing us these much needed improvements! If you have any questions about the new syntax, feel free to post them in a comment here. If you feel that you’ve encountered a related bug, please let us know in a comment here, open a bug report, or if you’re a customer please open a support ticket and let us know.

That’s it for now. As always, THANK YOU for using MySQL!


Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.