Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

6.3.10 Proxy Users

When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to have the privileges of the second user. In other words, the external user is a proxy user (a user who can impersonate or become known as another user) and the second user is a proxied user (a user whose identity can be taken on by a proxy user).

This section describes how the proxy user capability works. For general information about authentication plugins, see Section 6.3.8, “Pluggable Authentication”. If you are interested in writing your own authentication plugins that support proxy users, see Section, “Implementing Proxy User Support in Authentication Plugins”.

For proxying to occur for a given authentication plugin, these conditions must be satisfied:

  • When a connecting client should be treated as a proxy user, the plugin must return a different name, to indicate the proxied user name.

  • A proxy user account must be set up to be authenticated by the plugin. Use the CREATE USER statement to associate an account with a plugin, or ALTER USER to change its plugin.

  • A proxy user account must have the PROXY privilege for the proxied account. Use the GRANT statement for this.

Consider the following definitions:

CREATE USER 'empl_external'@'localhost'
  IDENTIFIED WITH auth_plugin AS 'auth_string';
CREATE USER 'employee'@'localhost'
  IDENTIFIED BY 'employee_pass';
  ON 'employee'@'localhost'
  TO 'empl_external'@'localhost';

When a client connects as empl_external from the local host, MySQL uses auth_plugin to perform authentication. Suppose that auth_plugin returns the employee user name to the server, based on the content of 'auth_string' and perhaps by consulting some external authentication system. That serves as a request to the server to treat this client, for purposes of privilege checking, as the employee local user.

In this case, empl_external is the proxy user and employee is the proxied user.

The server verifies that proxy authentication for employee is possible for the empl_external user by checking whether empl_external (the proxy user) has the PROXY privilege for employee (the proxied user). If this privilege had not been granted, an error occurs.

When proxying occurs, the USER() and CURRENT_USER() functions can be used to see the difference between the connecting user and the account whose privileges apply during the current session. For the example just described, those functions return these values:

| USER()                  | CURRENT_USER()     |
| empl_external@localhost | employee@localhost |

The IDENTIFIED WITH clause that names the authentication plugin may be followed by an AS clause specifying a string that the server passes to the plugin when the user connects. It is up to each plugin whether the AS clause is required. If it is required, the format of the authentication string depends on how the plugin intends to use it. Consult the documentation for a given plugin for information about the authentication string values it accepts.

Server Support for Proxy User Mapping

Some authentication plugins implement proxy user mapping for themselves. As of MySQL 5.7.7, the MySQL server itself can map proxy users according to granted proxy privileges. If the check_proxy_users system variable is enabled, the server performs proxy user mapping for any authentication plugin that requests it. By default, check_proxy_users is disabled, so the server performs no proxy user mapping even for authentication plugins that request it.

With check_proxy_users enabled, it may also be necessary to enable plugin-specific system variables to take advantage of server proxy user mapping support:

These restrictions apply to proxy user mapping performed by the server:

  • The server will not proxy to or from an anonymous user, even if the associated PROXY privilege is granted.

  • When a single account has been granted proxy privileges to more than one account, server proxy user mapping is nondeterministic. Therefore, granting proxy privileges on multiple accounts to a single account is discouraged.

Granting the Proxy Privilege

A special PROXY privilege is needed to enable an external user to connect as and have the privileges of another user. To grant this privilege, use the GRANT statement. For example:

GRANT PROXY ON 'proxied_user' TO 'proxy_user';

The statement creates a row in the mysql.proxies_priv grant table.

At connection time, proxy_user must represent a valid externally authenticated MySQL user, and proxied_user must represent a valid locally authenticated user. Otherwise, connection attempts fail.

The corresponding REVOKE syntax is:

REVOKE PROXY ON 'proxied_user' FROM 'proxy_user';

MySQL GRANT and REVOKE syntax extensions work as usual. For example:

GRANT PROXY ON 'a' TO 'b', 'c', 'd';
GRANT PROXY ON 'a' TO ''@'';
REVOKE PROXY ON 'a' FROM 'b', 'c', 'd';

In the preceding example, ''@'' is the default proxy user and means any user. The default proxy user is discussed later in this section.

The PROXY privilege can be granted in these cases:

  • By proxied_user for itself: The value of USER() must exactly match CURRENT_USER() and proxied_user, for both the user name and host name parts of the account name.

  • By a user that has GRANT PROXY ... WITH GRANT OPTION for proxied_user.

The root account created by default during MySQL installation has the PROXY ... WITH GRANT OPTION privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. For example, root can do this:

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'test';
GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;

Now the admin user can manage all the specific GRANT PROXY mappings. For example, admin can do this:

GRANT PROXY ON sally TO joe;

Default Proxy Users

To specify that some or all users should connect using a given plugin, create a blank MySQL user, set it up to use that plugin for authentication, and let the plugin return the real authenticated user name (if different from the blank user). For example, suppose that there exists a hypothetical plugin named ldap_auth that implements LDAP authentication:

CREATE USER ''@'' IDENTIFIED WITH ldap_auth AS 'O=Oracle, OU=MySQL';
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'developer_pass';
CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_pass';
GRANT PROXY ON 'manager'@'localhost' TO ''@'';
GRANT PROXY ON 'developer'@'localhost' TO ''@'';

Now assume that a client tries to connect as follows:

mysql --user=myuser --password='myuser_pass' ...

The server will not find myuser defined as a MySQL user. But because there is a blank user account (''@''), that matches the client user name and host name, the server authenticates the client against that account: The server invokes ldap_auth, passing it myuser and myuser_pass as the user name and password.

If the ldap_auth plugin finds in the LDAP directory that myuser_pass is not the correct password for myuser, authentication fails and the server rejects the connection.

If the password is correct and ldap_auth finds that myuser is a developer, it returns the user name developer to the MySQL server, rather than myuser. The server verifies that ''@'' can authenticate as developer (because it has the PROXY privilege to do so) and accepts the connection. The session proceeds with myuser having the privileges of developer. (These privileges should be set up by the DBA using GRANT statements, not shown.) The USER() and CURRENT_USER() functions return these values:

| USER()           | CURRENT_USER()      |
| myuser@localhost | developer@localhost |

If the plugin instead finds in the LDAP directory that myuser is a manager, it returns manager as the user name and the session proceeds with myuser having the privileges of manager.

| USER()           | CURRENT_USER()    |
| myuser@localhost | manager@localhost |

For simplicity, external authentication cannot be multilevel: Neither the credentials for developer nor those for manager are taken into account in the preceding example. However, they are still used if a client tries to authenticate directly against the developer or manager account, which is why those accounts should be assigned passwords.

The default proxy account uses '' in the host part, which matches any host. If you set up a default proxy user, take care to also check for accounts with '%' in the host part, because that also matches any host, but has precedence over '' by the rules that the server uses to sort account rows internally (see Section 6.2.4, “Access Control, Stage 1: Connection Verification”).

Suppose that a MySQL installation includes these two accounts:

CREATE USER ''@'%' IDENTIFIED BY 'some_password';

The intent of the first account is to serve as the default proxy user, to be used to authenticate connections for users who do not otherwise match a more-specific account. The second account might have been created, for example, to enable users without their own account as the anonymous user.

However, in this configuration, the first account is never used because the matching rules sort ''@'%' ahead of ''@''. For accounts that do not match any more-specific account, the server attempts to authenticate them against ''@'%' rather than ''@''.

If you intend to create a default proxy user, check for other existing match any user accounts that will take precedence over the default proxy user and thus prevent that user from working as intended. It may be necessary to remove any such accounts.

Proxy User System Variables

Two system variables help trace the proxy login process:

  • proxy_user: This value is NULL if proxying is not used. Otherwise, it indicates the proxy user account. For example, if a client authenticates through the ''@'' proxy account, this variable is set as follows:

    mysql> SELECT @@proxy_user;
    | @@proxy_user |
    | ''@''        |
  • external_user: Sometimes the authentication plugin may use an external user to authenticate to the MySQL server. For example, when using Windows native authentication, a plugin that authenticates using the windows API does not need the login ID passed to it. However, it still uses a Windows user ID to authenticate. The plugin may return this external user ID (or the first 512 UTF-8 bytes of it) to the server using the external_user read-only session variable. If the plugin does not set this variable, its value is NULL.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.