Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 30.4Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 182.5Kb
Man Pages (Zip) - 293.9Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

6.3.8 Proxy Users

Authentication to the MySQL server occurs by means of authentication plugins. The plugin that authenticates a given connection 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:

  • The external user is a proxy user (a user who can impersonate or become known as another user).

  • 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.7, “Pluggable Authentication”. For information about specific plugins, see Section 6.5.1, “Authentication Plugins”. For information about writing authentication plugins that support proxy users, see Section 24.2.4.9.4, “Implementing Proxy User Support in Authentication Plugins”.

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

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

  • For a client connecting to the proxy account to be treated as a proxy user, the plugin must return a user name different from the client user name, to indicate the user name for the proxied account.

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

The proxy mechanism permits mapping only the client user name to the proxied user name. There is no provision for mapping host names. When a connecting client matches a proxy account, the server attempts to find a match for a proxied account using the user name returned by the authentication plugin and the host name of the proxy account.

Consider the following definitions:

-- create proxy user
CREATE USER 'employee_ext'@'localhost'
  IDENTIFIED WITH my_auth_plugin AS 'my_auth_string';

-- create proxied user
CREATE USER 'employee'@'localhost'
  IDENTIFIED BY 'employee_pass';

-- grant PROXY privilege for proxy user to proxied user
GRANT PROXY
  ON 'employee'@'localhost'
  TO 'employee_ext'@'localhost';

When a client connects as employee_ext from the local host, MySQL uses my_auth_plugin to perform authentication. Suppose that my_auth_plugin returns a user name of employee to the server, based on the content of 'my_auth_string' and perhaps by consulting some external authentication system. The name employee differs from employee_ext, so returning employee serves as a request to the server to treat the employee_ext client, for purposes of privilege checking, as the employee local user.

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

The server verifies that proxy authentication for employee is possible for the employee_ext user by checking whether employee_ext (the proxy user) has the PROXY privilege for employee (the proxied user). If this privilege has 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 (the proxy user) and the account whose privileges apply during the current session (the proxied user). For the example just described, those functions return these values:

mysql> SELECT USER(), CURRENT_USER();
+------------------------+--------------------+
| USER()                 | CURRENT_USER()     |
+------------------------+--------------------+
| employee_ext@localhost | employee@localhost |
+------------------------+--------------------+

In the CREATE USER statement that creates the proxy user account, the IDENTIFIED WITH clause that names the authentication plugin is optionally followed by an AS 'auth_string' clause specifying a string that the server passes to the plugin when the user connects. If present, the string provides information that helps the plugin determine how to map the external client user name to a proxied user name. It is up to each plugin whether it requires the AS clause. If so, 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.

Granting the Proxy Privilege

The 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, the connection attempt fails.

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 'd' IDENTIFIED BY ...;
GRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION;
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. Default proxy user are discussed in Default Proxy Users.

The PROXY privilege can be granted in these cases:

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

  • 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.

The initial root account created 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;

Those statements create an admin user that can manage all 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 authentication plugin, create a blank MySQL user, associate it with 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 plugin named ldap_auth that implements LDAP authentication and maps connecting users onto either a developer or manager account. To set up proxying of users onto these accounts, use the following statements:

-- create default proxy user
CREATE USER ''@'' IDENTIFIED WITH ldap_auth AS 'O=Oracle, OU=MySQL';

-- create proxied users
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'developer_pass';
CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_pass';

-- grant PROXY privilege for default proxy user to proxied users
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 the ldap_auth authentication plugin and passes myuser and myuser_pass to it 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. Returning a user name different from the client user name of myuser signals to the server that it should treat myuser as a proxy. 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, the proxied user. (These privileges should be set up by the DBA using GRANT statements, not shown.) The USER() and CURRENT_USER() functions return these values:

mysql> SELECT USER(), CURRENT_USER();
+------------------+---------------------+
| 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.

mysql> SELECT USER(), CURRENT_USER();
+------------------+-------------------+
| 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 connect and authenticate directly as the developer or manager account, which is why those accounts should be assigned passwords.

Default Proxy User and Anonymous User Conflicts

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

In the preceding discussion, the default proxy user account has '' in the host part, which matches any host. If you set up a default proxy user, take care to also check whether nonproxy accounts exist with the same user part and '%' in the host part, because '%' 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 default proxy user
CREATE USER ''@''
  IDENTIFIED WITH some_plugin AS 'some_auth_string';
-- create anonymous user
CREATE USER ''@'%'
  IDENTIFIED BY 'some_password';

The first account (''@'') is intended as the default proxy user, used to authenticate connections for users who do not otherwise match a more-specific account. The second account (''@'%') is an anonymous-user account, which might have been created, for example, to enable users without their own account to connect anonymously.

Both accounts have the same user part (''), which matches any user. And each account has a host part that matches any host. Nevertheless, there is a priority in account matching for connection attempts because the matching rules sort a host of '%' ahead of ''. For accounts that do not match any more-specific account, the server attempts to authenticate them against ''@'%' (the anonymous user) rather than ''@'' (the default proxy user). The result is that the default proxy account is never used.

To avoid this problem, use one of the following strategies:

  • Remove the anonymous account so that it does not conflict with the default proxy user. This might be a good idea anyway if you want to associate every connection with a named user.

  • Use a more-specific default proxy user that matches ahead of the anonymous user. For example, to permit only localhost proxy connections, use ''@'localhost':

    CREATE USER ''@'localhost'
      IDENTIFIED WITH some_plugin AS 'some_auth_string';
    

    In addition, modify any GRANT PROXY statements to name ''@'localhost' rather than ''@'' as the proxy user.

    Be aware that this strategy prevents anonymous-user connections from localhost.

  • Create multiple proxy users, one for local connections and one for everything else (remote connections). This can be useful particularly when local users should have different privileges from remote users.

    Create the proxy users:

    -- create proxy user for local connections
    CREATE USER ''@'localhost'
      IDENTIFIED WITH some_plugin AS 'some_auth_string';
    -- create proxy user for remote connections
    CREATE USER ''@'%'
      IDENTIFIED WITH some_plugin AS 'some_auth_string';
    

    Create the proxied users:

    -- create proxied user for local connections
    CREATE USER 'developer'@'localhost'
      IDENTIFIED BY 'some_password';
    -- create proxied user for remote connections
    CREATE USER 'developer'@'%'
      IDENTIFIED BY 'some_password';
    

    Grant the proxy privilege to each proxy user for the corresponding proxied user:

    GRANT PROXY ON 'developer'@'localhost' TO ''@'localhost';
    GRANT PROXY ON 'developer'@'%' TO ''@'%';
    

    Finally, grant appropriate privileges to the local and remote proxied users (not shown).

    Assume that the some_plugin/'some_auth_string' combination causes some_plugin to map the client user name to developer. Local connections match the ''@'localhost' proxy user, which maps to the 'developer'@'localhost' proxied user. Remote connections match the ''@'%' proxy user, which maps to the 'developer'@'%' proxied user.

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.


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