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 22.214.171.124.4, “Implementing Proxy User Support in Authentication Plugins”.
For proxying to occur, 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.
Consider the following definitions:
CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH auth_plugin AS 'auth_string'; CREATE USER 'employee'@'localhost' IDENTIFIED BY 'employee_pass'; GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost';
When a client connects as
the local host, MySQL uses
perform authentication. Suppose that
auth_plugin returns the
employee user name to the server, based on the
'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
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 would occur.
When proxying occurs, the
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:
SELECT USER(), CURRENT_USER();+-------------------------+--------------------+ | USER() | CURRENT_USER() | +-------------------------+--------------------+ | empl_external@localhost | employee@localhost | +-------------------------+--------------------+
IDENTIFIED WITH clause that names the
authentication plugin may be followed by an
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.
As of MySQL 5.7.7, the
sha256_password built-in authentication
plugins support proxy users. To enable proxy capabilities for
these plugins, enable the
variable. Then, to control which individual plugins support proxy
users, enable either or both of the
system variables. For information about user proxying, see
These restrictions apply to proxy user support with the built-in plugins:
Built-in authentication plugins do not proxy to an anonymous user, even if the associated privileges are granted.
When a single account has been granted proxy privileges on more than one account, the server mapping is nondeterministic. Therefore, granting proxy privileges on multiple accounts to a single account is discouraged.
GRANT PROXY ON '
proxied_user' TO '
The statement creates a row in the
mysql.proxies_priv grant table.
At connection time,
represent a valid externally authenticated MySQL user, and
proxied_user must represent a valid
locally authenticated user. Otherwise, connection attempts fail.
REVOKE syntax is:
REVOKE PROXY ON '
proxied_user' FROM '
GRANT PROXY ON 'a' TO 'b', 'c', 'd'; 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.” The default proxy
user is discussed later in this section.
PROXY privilege can be granted
in these cases:
proxied_user for itself: The
USER() must exactly match
proxied_user, for both the user
name and host name parts of the account name.
By a user that has
GRANT PROXY ... WITH GRANT
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
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;
admin user can manage all the specific
GRANT PROXY mappings. For example,
admin can do this:
GRANT PROXY ON sally TO joe;
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
the user name and password.
ldap_auth plugin finds in the LDAP
myuser_pass is not the correct
myuser, authentication fails and
the server rejects the connection.
If the password is correct and
myuser is a developer, it returns the user
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.)
CURRENT_USER() functions return
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
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
manager are taken into account in the
preceding example. However, they are still used if a client tries
to authenticate directly against the
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
'' 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 WITH some_plugin; 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 will never be
used because the matching rules sort
''@''. For accounts that do not match
any more-specific account, the server will attempt to authenticate
''@'%' 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.
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 default proxy account, this variable
will be set as follows:
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 an 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
read-only session variable. If the plugin does not set this
variable, its value is