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 plugin
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)
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 – 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.
Good stuff.
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)
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)
Or you can limit resources for the new account:
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 all others involved in bringing us these needed improvements!
Hi!
What I missed dearly is a “CREATE USER LIKE”:
As a user account is specified by both user name and (client) host name, the addition of further application machines and especially a change in the network (sub)domains may easily require further user accounts. Typically, this cannot be handled by using wildcards, and the DBA often does not want to specify the host as ‘%’.
When I needed it, I solved my need by “INSERT … SELECT …” in the various privilege tables in database “mysql”. (Yes, I saw the announcement this may become impossible.)
IMO, a “CREATE USER LIKE” should create a new account with the same privileges and authorization settings (password, plugin, …) as the old one has, just introduce a new user and/or host name. This would allow a really clean handling of (client) host and network changes.
Regards,
Jörg
Hi Jörg,
Thanks for the feedback! I can think of a couple ways this use case might be solved in 5.7 (even though CREATE USER LIKE syntax is not there):
By leveraging proxy users, you could store common privileges in a single user account, then create appropriate accounts for each host. These wouldn’t require copying of privileges, as these are all managed in the one common account that’s being proxied. You still have to do some work to issue
CREATE USERfor each user@host combination, but even that’s a lot easier in 5.7 now that we’ve introducedSHOW CREATE USER. The output ofSHOW CREATE USERcontains onlyCREATE USERcommand (including the password hash, and all other account-level modifiers). It does not include privileges.The MySQL Utility mysqluserclone could be useful, here – but I believe it needs an enhancement to be able to copy the password hash from the source user account to the target user account. I’ll check with that team before opening a feature request.
Hi Todd,
is there any plan on CREATE USER IF NOT EXISTS and DROP USER IF EXISTS ?
Thanks,
JFG
Hi Jean-François,
I know this is a long-standing request. I don’t know of specific plans that might result in this being added in 5.7 before GA, but I’ll run it past the team.
Todd