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

mysql> CREATE USER new@localhost
-> IDENTIFIED WITH sha256_password
-> BY 'pwd';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER new@localhost

-> IDENTIFIED WITH sha256_password

-> BY 'pwd';

Query OK, 0 rows affected (0.00 sec)

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:

mysql> CREATE USER d@localhost ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
D:\mysql-5.7.7-rc-winx64>bin\mysql -ud -P3309
ERROR 3118 (HY000): Access denied for user 'd'@'localhost'. Account is locked.

mysql> CREATE USER d@localhost ACCOUNT LOCK;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

D:\mysql-5.7.7-rc-winx64>bin\mysql -ud -P3309

ERROR 3118 (HY000): Access denied for user 'd'@'localhost'. Account is locked.

Other New Account Options

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

mysql> CREATE USER p@localhost
-> IDENTIFIED BY 'pwd'
-> PASSWORD EXPIRE INTERVAL 1 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER p@localhost

-> IDENTIFIED BY 'pwd'

-> PASSWORD EXPIRE INTERVAL 1 DAY;

Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE USER s@localhost
-> REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER s@localhost

-> REQUIRE SSL;

Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE USER r@localhost
-> WITH MAX_QUERIES_PER_HOUR 5;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER r@localhost

-> WITH MAX_QUERIES_PER_HOUR 5;

Query OK, 0 rows affected (0.00 sec)

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!