MySQL has provided support for proxy users since version 5.5, but the roles-like capabilities offered have been largely unnoticed until recently.  Part of that has been due to limitations on which types of accounts could leverage proxy user capabilities.  This changes with the release of MySQL Server 5.7.7 (Release Candidate), which includes support for proxy user mapping for the standard mysql_native_password and sha256_password authentication plugins.  This post will introduce the new functionality and explain how to leverage it to emulate certain features of roles without any need for PAM or external authentication systems.

Proxy user basics

One key attribute of SQL roles is the ability to manage common sets of privileges independent of user credentials.  Typically, DBAs create user accounts and assign privileges directly to those accounts.  Given five admins needing identical access, DBAs were faced with the options of creating a single shared account (bad for auditing and password control) or five individual accounts (bad for privilege maintenance/updates).  Proxy user functionality – like roles – breaks the coupling between authentication (credentials) and authorization (privileges).  With proxy users, it is easy to manage privileges for an arbitrary group of users – simply modify the privileges of the proxied user account, and all users which proxy to that are affected.  For example:

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

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

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

mysql> GRANT PROXY ON proxy_base@localhost
    -> TO admin_1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT PROXY ON proxy_base@localhost
    -> TO admin_2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON app.*
    -> TO proxy_base@localhost;
Query OK, 0 rows affected (0.00 sec)

Now if you want to add privileges for these admin_1 and admin_2 accounts, you can do it like so:

mysql> GRANT SELECT ON archive.*
    -> TO proxy_base@localhost;
Query OK, 0 rows affected (0.00 sec)

After the above statement, both admin_1 and admin_2 accounts will have SELECT access to the archive database when they are mapped to proxy users.

Mapping Proxy Users

You may have noticed that I said, “when they are mapped to proxy users” in the statement above.  The account management commands shown above aren’t new to MySQL 5.7 – they will work in MySQL 5.5 and MySQL 5.6 just the same.  In these earlier versions – as well as in MySQL 5.7 with default configuration – the default mysql_native_password plugin does not map users to proxy accounts.  The above GRANT PROXY commands are meaningless – the admin_1@localhost user will always be mapped to the admin_1@localhost account privileges.

In MySQL 5.5 and 5.6, mapping of users to proxy users was done exclusively by the authentication plugin.  That means that the various authentication plugins such as PAM or Windows Native would do authentication and map the user to the appropriate privilege account.  The server would check to make sure the actual user has the PROXY privilege for the intended account.

Starting in MySQL Server 5.7.7, the server can now leverage PROXY privilege definitions to do the proxy user mapping directly, rather than relying on the authentication plugin to do it.  This must be explicitly enabled using the new --check_proxy_users configuration option.  Setting this to ON will cause the server to reference the PROXY privilege definitions to seek a user to proxy, when the authentication plugin requests this be done.

Authentication plugin changes

Again, there’s a caveat buried in the last sentence of the previous section – authentication plugins have to request that the server do this proxy user mapping for them.  They do this by returning a special flag value.  This behavior is controlled for mysql_native_password and sha256_password with the --mysql_native_password_proxy_users and --sha256_password_proxy_users variables, respectively.  As with –check_proxy_users, the default value is OFF, so each must be explicitly enabled to allow proxy user mapping of accounts which use that authentication plugin.

Putting it all together

Starting right where we left off, let’s enable proxy user mapping and the special flag for mysql_native_password:

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

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

Now we are be able to log in using admin_1@localhost and have privileges of proxy_base@localhost:

D:\mysql-5.7.7-rc-winx64>bin\mysql -uadmin_1 -P3309
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.7-rc MySQL Community Server (GPL)
...
mysql> SELECT USER(), CURRENT_USER(), @@session.proxy_user;
+-------------------+----------------------+-----------------------+
| USER()            | CURRENT_USER()       | @@session.proxy_user  |
+-------------------+----------------------+-----------------------+
| admin_1@localhost | proxy_base@localhost | 'admin_1'@'localhost' |
+-------------------+----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+---------------------------------------------------------+
| Grants for proxy_base@localhost                         |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'proxy_base'@'localhost'          |
| GRANT SELECT ON `app`.* TO 'proxy_base'@'localhost'     |
| GRANT SELECT ON `archive`.* TO 'proxy_base'@'localhost' |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

Now the role-like privilege management capabilities of proxy user no longer require external authentication systems or complicated setup.

Other considerations and notes

With PAM and Windows Native authentication plugins, proxy user mapping was explicit and deterministic.  There’s nothing preventing DBAs from defining a single account which can proxy to multiple base accounts.  However, the server mapping using these PROXY privileges is non-deterministic when mutliple proxy privileges exist for a given user account.  Do not do the following with server proxy user mapping:

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

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

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

mysql> GRANT PROXY ON proxy_b1@localhost
-> TO bad@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT PROXY ON proxy_b2@localhost
-> TO bad@localhost;
Query OK, 0 rows affected (0.00 sec)

MySQL Server will do proxy user mapping, but there is no guarantee which account will be selected to proxy.

mysql> SELECT USER(), CURRENT_USER(), @@session.proxy_user;
+---------------+--------------------+----------------------+
| USER()        | CURRENT_USER()     | @@session.proxy_user |
+---------------+--------------------+----------------------+
| bad@localhost | proxy_b1@localhost | 'bad'@'localhost'    |
+---------------+--------------------+----------------------+
1 row in set (0.00 sec)

Anonymous users are also problematic for server-side proxy mapping. You cannot map a user to an anonymous proxied user. The following is a legal GRANT command, but will be ignored when the server tries to map proxy users from the PROXY privileges:

mysql> GRANT PROXY ON ''@localhost
    -> TO bad@localhost;
Query OK, 0 rows affected (0.00 sec)

You also cannot map from an anonymous user account using the new server-based mapping. This is an important difference from external authentication plugins, which work great with anonymous user accounts, as the PAM or Windows Native authentication modules identify the user based on other factors.

It’s important to note that user accounts which have no matching PROXY privilege authenticate and have their normal assigned privileges regardless of the configuration options described above.  This allows mixing and matching of use cases – there’s no need to make all mysql_native_password users leverage proxy  mapping.

A best practice for any proxy user deployment is to make the proxied (base) account inaccessible.  You can do this with the mysql_no_login plugin, or with the new ALTER USER … ACCOUNT LOCK syntax in MySQL 5.7.

Conclusion

With the expanded support for proxy users now including native authentication plugins in MySQL 5.7, hopefully account privilege maintenance will be simplified for many deployments, and shared user accounts will be eliminated.

Please let us know your thoughts!