I’ve written previously about use cases where having accounts which cannot be used to establish client connections are useful. There are various hacks to accomplish this with legacy versions (insert invalid password hash into mysql.user table, etc.), and we introduced the mysql_no_login authentication plugin for this very purpose. Now as of MySQL 5.7.6, account locking gets native support through the ACCOUNT LOCK clause of CREATE USER and ALTER USER commands. This post revisits the use cases which drove this feature and the implementation details.

Use Cases

Security best practices dictate giving accounts the minimum privileges required, and in some cases, that means no client connections.  As an example, views or stored programs may be defined to execute with the privileges of an account different than the one invoking them.  My specific use case explored earlier was encountered while creating stored procedures to help enforce a password policy. These scripts needed access to the mysql.user table, as well as privileges to set account flags to require password changes. By creating the stored routines with an explicit DEFINER and associated security context, and ensuring that account cannot be used for client connections, we eliminate the possibility that the account credentials can be compromised and leveraged to perform unexpected actions.

Another use case relates to proxy users.  Especially relevant now that MySQL 5.7 adds support for proxy users with native authentication plugins to emulate SQL roles, it’s a good practice to define the base user that is proxied as an account which cannot be directly accessed.  All access should happen through the accounts which can proxy the base account.

Usage

Creating a user account that is locked is simple:

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

Modifying an existing account to lock it or unlock it is similarly easy:

mysql> ALTER USER locked@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

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

The lock status is reflected in the (new to 5.7) SHOW CREATE USER output:

mysql> SHOW CREATE USER locked@localhost\G
*************************** 1. row ***************************
CREATE USER for locked@localhost: CREATE USER 'locked'@'localhost' IDENTIFIED 
WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK
1 row in set (0.00 sec)

When attempting to connect using an account that is locked, users are given error code 3118 and the following error message:

R:\mysql-5.7.7>bin\mysql -ulocked -P3310
ERROR 3118 (HY000): Access denied for user 'locked'@'localhost'. Account is locked.

Account lock status is checked during the authentication stage (establishing a new connection or COM_CHANGE_USER). That means that if you issue ALTER USER ... ACCOUNT LOCK, it will not impact existing connections for the specified user (notable especially for environments where persistent connections are used). To affect existing connections, you will need to also issue KILL CONNECTION commands.

Examples

The above point is important in that it also enables the use cases described earlier. Here’s how to use it with roles-emulating PROXY USER capabilities:

mysql> ALTER USER locked@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> GRANT SELECT ON mysql.user TO base@localhost;
Query OK, 0 rows affected (0.00 sec)

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

mysql> GRANT PROXY ON base@localhost TO user@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@global.check_proxy_users = ON,
    ->     @@global.mysql_native_password_proxy_users = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

R:\mysql-5.7.7>bin\mysql -uuser -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
...
mysql> SELECT USER(), CURRENT_USER(), @@session.proxy_user;
+----------------+----------------+----------------------+
| USER()         | CURRENT_USER() | @@session.proxy_user |
+----------------+----------------+----------------------+
| user@localhost | base@localhost | 'user'@'localhost'   |
+----------------+----------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT user, host, account_locked FROM mysql.user;
+--------+-----------+----------------+
| user   | host      | account_locked |
+--------+-----------+----------------+
| root   | localhost | N              |
| locked | localhost | Y              |
| base   | localhost | Y              |
| user   | localhost | N              |
+--------+-----------+----------------+
4 rows in set (0.00 sec)

The base@localhost account cannot be directly accessed, yet the privileges associated with that account can be proxied to other accounts and used without those end user accounts being affected by the locked status of the base@localhost account. Also as seen above, the account_locked column of mysql.user reflects whether the account is locked or not.

Conclusion

By introducing the ability to explicitly lock (and unlock) accounts, MySQL 5.7 makes it easier to apply security best practices by restricting accounts from establishing client connections.