MySQL :: Improved ALTER USER Syntax Support in 5.7

Complimenting the expanded CREATE USER syntax introduced in MySQL Server 5.7.6 is more useful ALTER USER syntax. Before MySQL Server 5.7.6, ALTER USER could only be used to expire a user’s password. That’s pretty limited. With changes made in MySQL Server 5.7.6, a better distinction is made between privilege-level attributes (those which are managed via GRANT and REVOKE statements) and account-level attributes (those managed using CREATE USER and ALTER USER statements). MySQL has a long history of confusing these – for example, requiring a GRANT command to set account resource limits or require SSL. This all changes for the better in MySQL Server 5.7 – here’s how:

Account attributes

These are consistent with CREATE USER – the same attributes which can be defined with a CREATE USER command can now be modified using an ALTER USER command. These attributes are:

Password/credentials or auth plugin mapping
Authentication plugin type
SSL/X509 requirements
Resource constraints
Password expiration status and policy
Account locking

These attributes, and the syntax by which they are modified, is now consistent across CREATE USER and ALTER USER commands. Here’s a couple of important use cases the new syntax covers:

Changing authentication plugin

In earlier versions, there was no way to change the authentication plugin for an existing user account without directly manipulating the mysql.user table (discouraged). The new ALTER USER syntax supports this use case:

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

mysql> ALTER USER t@localhost
-> IDENTIFIED WITH sha256_password
-> BY 'pwd';
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE USER t@localhost

-> IDENTIFIED WITH mysql_native_password

-> BY 'pwd';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER t@localhost

-> IDENTIFIED WITH sha256_password

-> BY 'pwd';

Query OK, 0 rows affected (0.03 sec)

Yay!

For users who have deployed the mysql_no_login plugin to lock down system accounts, that also means you can convert to the new “locked account” introduced in MySQL Server 5.7.7, without affecting users or directly modifying mysql.user.

Updating proxy user mapping

Another use case which required direct updates of the mysql.user table in the past is modification of the proxy user mapping for authentication plugins like PAM or Windows Native authentication. As an example, let’s say you are using the PAM authentication plugin, and you’ve created accounts as follows to support this:

CREATE USER ''@''
  IDENTIFIED WITH authentication_pam
  AS 'mysql, dba=root, users=data_entry';
CREATE USER data_entry@'%'
  ACCOUNT LOCK;
GRANT SELECT, INSERT, UPDATE
  ON db.*
  TO data_entry@'%';
GRANT PROXY ON root@'%'
  TO ''@'';
GRANT PROXY ON data_entry@'%'
  TO ''@'';

CREATE USER ''@''

IDENTIFIED WITH authentication_pam

AS 'mysql, dba=root, users=data_entry';

CREATE USER data_entry@'%'

ACCOUNT LOCK;

GRANT SELECT, INSERT, UPDATE

ON db.*

TO data_entry@'%';

GRANT PROXY ON root@'%'

TO ''@'';

GRANT PROXY ON data_entry@'%'

TO ''@'';

Now you want to add another group of users with different privileges. You can create the additional user account:

CREATE USER accounting@'%'
  ACCOUNT LOCK;
GRANT SELECT
  ON accounting.*
  TO accounting@'%'

CREATE USER accounting@'%'

ACCOUNT LOCK;

GRANT SELECT

ON accounting.*

TO accounting@'%'

Now you need to update the mapping for the anonymous user. In 5.6, this requires an update of mysql.user, and FLUSH PRIVILEGES:

UPDATE mysql.user
  SET authentication_string = 'mysql, dba=root, users=data_entry, finance=accounting'
  WHERE user = ''
    AND host = '%';
FLUSH PRIVILEGES;

UPDATE mysql.user

SET authentication_string = 'mysql, dba=root, users=data_entry, finance=accounting'

WHERE user = ''

AND host = '%';

FLUSH PRIVILEGES;

As of 5.7.6, this can be done with the ALTER USER command, instead:

ALTER USER ''@'%'
  IDENTIFIED BY 'mysql, dba=root, users=data_entry, finance=accounting';

1 2	ALTER USER ''@'%' IDENTIFIED BY 'mysql, dba=root, users=data_entry, finance=accounting';

Separating account and privilege attributes

The new syntax allows for a clean delineation between commands which modify account attributes and those which modify privileges. No longer do users have to resort to GRANT commands to modify account attributes, such as SSL. For backwards compatibility, such commands are still supported, but deprecated:

mysql> GRANT USAGE ON *.*
-> TO g@localhost
-> REQUIRE SSL;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> GRANT USAGE ON *.*

-> TO g@localhost

-> REQUIRE SSL;

Query OK, 0 rows affected, 1 warning (0.07 sec)

Here’s the warning text: Using GRANT statement to modify existing user’s properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

Notice how I had to specify USAGE above, even though it had nothing to do with what I was actually trying to do (modify SSL requirement). That’s because the GRANT syntax requires at least one privilege for valid syntax – so I have to include USAGE.

This stronger distinction between privilege and account management commands allows us to also move towards eliminating GRANT statements which create user accounts. This can be a problem when a DBA inadvertently enters the wrong user or host value while trying to modify privileges – unless NO_AUTO_CREATE_USER is specified in the SQL mode (which it is in 5.7 by default now, but was not earlier), a new account with no password is created and granted the requested privilege.

Conclusion

The expanded ALTER USER syntax introduced in MySQL Server 5.7.6 makes management of existing users easier, and eliminates the need to resort to direct updates of the mysql.user system tables. Like the improved CREATE USER command, this work was done by Satish — further thanks to him and all who assisted in improving the account management related commands in MySQL 5.7.

THANK YOU for using MySQL!