Emulating roles with expanded proxy user support in 5.7.7

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!

4 thoughts on “Emulating roles with expanded proxy user support in 5.7.7

  1. Hi Todd!

    This really sounds helpful to map many users to (fewer) roles.
    However, I see an inconsistency and a source for trouble:

    1) Your “select” examples show “user()” and “current_user()” to return a result without quotes, but the session variable “proxy_user” to surround the user name and the host name by quotes. I do not yet see a good reason for this inconsistency.

    2) You warn against granting multiple proxy privileges (for different target users) to the same user, because the result is unpredictable. IMNSHO, as long as the server considers the privileges of only one target user, such multiple grants should be prevented, and the second such “grant” statement should return an error.
    As an alternative, IMHO the server might consider the grantee to have the union of the target user privileges, but that would need a change of the “current_user()” concept.

    There is another aspect which I really like:
    The way you describe it, all that privilege handling is controlled by “grant” (and probably “revoke”) statements and by configuration variables, so that the application need not issue a statement to start using the role privileges. Avoiding an application change (and a non-standard SQL statement) is good!
    Locking the proxy account makes sense. Before such a feature was available, I brute-force assigned an arbitrary string to the “password” field, which would hopefully not be the hash result of any password text. For documentation, I borrowed the IBM (AIX) idea to include “DEADBEEF” in that string.

    1. Hello Jörg!

      Thanks for your feedback – most appreciated. Your observation about quoting inconsistency is something to explore further – it’s been like this since 5.5 (when proxy users were first introduced), but I can’t provide a good justification for it. I opened Bug#76641 to get clarity (or consistency).

      Regarding multiple proxy privileges, this limitation only applies to the new server mapping (as opposed to the proxy user mapping that’s historically been done by authentication plugins). The PAM Authentication Plugin documentation has a good example of when this is really useful. Basically, one can create a single anonymous user account and give it PROXY privileges to multiple accounts, then let the authentication plugin figure out which to map during authentication. We wouldn’t want to disable that important functionality, which is why we chose to not restrict granting multiple PROXY privileges to a single account.

      I’m glad that you also highlighted the way this was incorporated using existing SQL syntax, and that it can be adopted without any modification to application code. That was very intentional. 🙂

  2. Hi Todd,
    I am trying to authenticate MySQL users against Active Directory. In my research I found plugins like PAM Authentication / LDAP Authentication for MySQL Enterprise Edison, but noting for Community Edison.Please advice if there is any work around for Community Edition MySQL users to be authenticated against Active Directory.
    Thanks,
    Shiva.

    1. Hi Shiva,

      The MySQL Enterprise Authentication functionality you found is the only LDAP integration option offered by the MySQL team at Oracle. I’d encourage you to consider a MySQL Enterprise subscription – it sounds like you could get significant value from that. That said, if you’re looking for free or pure open source solutions, you may want to look at MariaDB’s solution:

      https://jstaf.github.io/2018/05/17/mariadb-ldap.html

      I hope that helps!

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.