The MySQL server authenticates client connections using 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 assume 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 and privileges can be assumed by a proxy user).
This section describes how the proxy user capability works. For general information about authentication plugins, see Section 6.2.10, “Pluggable Authentication”. For information about specific plugins, see Section 6.4.1, “Authentication Plugins”. For information about writing authentication plugins that support proxy users, see Section 18.104.22.168.4, “Implementing Proxy User Support in Authentication Plugins”.
For proxying to occur for a given authentication plugin, these conditions must be satisfied:
The plugin must support proxying.
Normally, the proxied user is configured so that it can be used only in proxying scenaries and not for direct logins.
For a client connecting to the proxy account to be treated as a proxy user, the authentication plugin must return a user name different from the client user name, to indicate the user name of the proxied account that defines the privileges to be assumed by the proxy user.
The proxy mechanism permits mapping only the external client user name to the proxied user name. There is no provision for mapping host names:
When a client connects to the server, the server determines the proper account based on the user name passed by the client program and the host from which the client connects.
If that account is a proxy account, the server attempts to determine the appropriate proxied account by finding a match for a proxied account using the user name returned by the authentication plugin and the host name of the proxy account. The host name in the proxied account is ignored.
Consider the following account definitions:
-- create proxy account CREATE USER 'employee_ext'@'localhost' IDENTIFIED WITH my_auth_plugin AS 'my_auth_string'; -- create proxied account and grant its privileges CREATE USER 'employee'@'localhost' IDENTIFIED BY 'employee_password'; GRANT ALL ON employees.* TO 'employee'@'localhost'; -- grant to proxy account the PROXY privilege for proxied account GRANT PROXY ON 'employee'@'localhost' TO 'employee_ext'@'localhost';
When a client connects as
the local host, MySQL uses the plugin named
my_auth_plugin to perform authentication.
my_auth_plugin returns a user
employee to the server, based on the
and perhaps by consulting some external authentication system.
employee differs from
employee_ext, so returning
employee serves as a request to the server to
employee_ext external user, for
purposes of privilege checking, as the
employee local user.
In this case,
employee_ext is the proxy user
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. Otherwise,
employee_ext assumes the privileges of
employee. The server checks statements
executed during the client session by
employee_ext against the privileges granted
employee. In this case,
employee_ext can access tables in the
To ensure that the proxied account,
cannot be used directly, do not tell anyone else its password.
If you do not let anyone know the password for the account,
clients cannot use it to connect directly to the MySQL server.
When proxying occurs, the
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 | +------------------------+--------------------+
CREATE USER statement that
creates the proxy user account, the
WITH clause that names the proxy-supporting
authentication plugin is optionally followed by an
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 proxy (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
Proxied accounts generally are intended to be used only by means of proxy accounts. That is, clients connect using a proxy account, then are mapped onto and assume the privileges of the appropriate proxied user.
To ensure that a proxied account cannot be used directly, create the account with a password but do not tell anyone else the password. If you do not let anyone know the password for the account, clients cannot use it to connect directly to the MySQL server.
GRANT PROXY ON 'proxied_user' TO 'proxy_user';
The statement creates a row in the
mysql.proxies_priv grant table.
At connect time,
represent a valid externally authenticated MySQL user, and
proxied_user must represent a valid
locally authenticated user. Otherwise, the connection attempt
REVOKE PROXY ON 'proxied_user' FROM 'proxy_user';
-- grant PROXY to multiple accounts GRANT PROXY ON 'a' TO 'b', 'c', 'd'; -- revoke PROXY from multiple accounts REVOKE PROXY ON 'a' FROM 'b', 'c', 'd'; -- grant PROXY to an account and enable the account to grant -- PROXY to the proxied account GRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION; -- grant PROXY to default proxy account GRANT PROXY ON 'a' TO ''@'';
PROXY privilege can be
granted in these cases:
root account created during MySQL
installation has the
PROXY ... WITH GRANT
OPTION privilege for
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.
root can do this:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_password'; GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;
Those statements create an
admin user that
can manage all
GRANT PROXY mappings. For
admin can do this:
GRANT PROXY ON sally TO joe;
To specify that some or all users should connect using a given
authentication plugin, create a “blank” MySQL
account with an empty user name and host name
''@''), associate it with that plugin, and
let the plugin return the real authenticated user name (if
different from the blank user). Suppose that there exists a
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 account CREATE USER ''@'' IDENTIFIED WITH ldap_auth AS 'O=Oracle, OU=MySQL'; -- create proxied accounts CREATE USER 'developer'@'localhost' IDENTIFIED BY 'developer_password'; CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_password'; -- grant to default proxy account the -- PROXY privilege for proxied accounts GRANT PROXY ON 'manager'@'localhost' TO ''@''; GRANT PROXY ON 'developer'@'localhost' TO ''@'';
As with proxied accounts created in previous examples, the password should be kept secret so that clients cannot use the accounts to log in directly to the MySQL server.
Now assume that a client connects as follows:
shell> mysql --user=myuser --password ... Enter password: myuser_password
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
authentication plugin and passes
myuser_password to it as the user
name and password.
ldap_auth plugin finds in the LDAP
myuser_password is not
the correct password for
authentication fails and the server rejects the connection.
If the password is correct and
myuser is a developer, it returns
the user name
developer to the MySQL server,
myuser. Returning a user name
different from the client user name of
signals to the server that it should treat
myuser as a proxy. The server verifies that
''@'' can authenticate as
PROXY privilege to do so)
and accepts the connection. The session proceeds with
myuser having the privileges of the
developer proxied user. (These privileges
should be set up by the DBA using
GRANT statements, not shown.) The
CURRENT_USER() functions return
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
myuser having the privileges of
manager proxied user.
mysql> SELECT USER(), CURRENT_USER(); +------------------+-------------------+ | USER() | CURRENT_USER() | +------------------+-------------------+ | myuser@localhost | manager@localhost | +------------------+-------------------+
For simplicity, external authentication cannot be multilevel:
Neither the credentials 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
account, which is why those proxied accounts should be protected
against direct login (see
Preventing Direct Login to Proxied Accounts).
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 because they can 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
'' by the rules that the server uses to
sort account rows internally (see
Section 6.2.5, “Access Control, Stage 1: Connection Verification”).
Suppose that a MySQL installation includes these two accounts:
-- create default proxy account CREATE USER ''@'' IDENTIFIED WITH some_plugin AS 'some_auth_string'; -- create anonymous account CREATE USER ''@'%' IDENTIFIED BY 'anon_user_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
''@'%') 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). As a result,
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.
Use a more-specific default proxy user that matches ahead of the anonymous user. For example, to permit only
localhostproxy connections, use
CREATE USER ''@'localhost' IDENTIFIED WITH some_plugin AS 'some_auth_string';
In addition, modify any
GRANT PROXYstatements to name
''@''as the proxy user.
Be aware that this strategy prevents anonymous-user connections from
Use a named default account rather than an anonymous default account. For an example of this technique, consult the instructions for using the
authentication_windowsplugin. See Section 22.214.171.124, “Windows Pluggable Authentication”.
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 to each proxy account the
PROXYprivilege for the corresponding proxied account:
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_pluginto 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
Two system variables help trace the proxy login process:
proxy_user: This value is
NULLif 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_userread-only session variable. If the plugin does not set this variable, its value is