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.7, "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
empl_external from the local host, MySQL uses
auth_plugin to perform authentication. If
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
PROXY privilege for
employee. (If this privilege had not been granted, an error would occur.)
When proxying occurs, the
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
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.
GRANT PROXY ON '
proxied_user' TO '
proxy_user must represent a valid externally authenticated MySQL user
at connection time or connection attempts fail.
represent a valid locally authenticated user at connection time or 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' 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." The default proxy user is discussed later in
PROXY privilege can be
granted in these cases:
proxied_user for itself: The value of
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 OPTION
root account created by default during MySQL installation has the
PROXY ... WITH GRANT OPTION
''@'', that is, for all users. This enables
to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. For
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
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 external 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
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
myuser_pass as the user name and
ldap_auth plugin finds in the LDAP directory that
is not the correct password for
myuser, authentication fails and the server rejects
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
server verifies that
''@'' can authenticate as
developer (because it has the
PROXY privilege to do so) and accepts the connection. The session proceeds
myuser having the privileges of
privileges should be set up by the DBA using
GRANT statements, not shown.) The
functions return these values:
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
manager as the user name and the session proceeds with
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
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
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 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
''@'%' ahead of
''@''. For accounts that do not match
any more-specific account, the server will attempt 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.
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
session variable. If the plugin does not set this variable, its value is