Rechercher dans le manuel MySQL
6.3.11 Proxy Users
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.3.10, “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 29.2.4.9.4, “Implementing Proxy User Support in Authentication Plugins”.
As an alternative to the use of proxy users, role support may provide a suitable way to map users onto specific sets of named privileges. See Section 6.3.4, “Using Roles”.
Requirements for Proxy User Support
For proxying to occur for a given authentication plugin, these conditions must be satisfied:
Proxying must be supported, either by the plugin itself, or by the MySQL server on behalf of the plugin. In the latter case, server support may need to be enabled explicitly; see Server Support for Proxy User Mapping.
The proxy user account must be set up to be authenticated by the plugin. Use the
CREATE USER
statement to associate an account with an authentication plugin, orALTER USER
to change its plugin.The proxied user account must be created and granted the privileges to be assumed by the proxy user. Use the
CREATE USER
andGRANT
statements for this.The proxy user account must have the
PROXY
privilege for the proxied account. Use theGRANT
statement for this.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.
Alternatively, for plugins that are provided proxy mapping by the server, the proxied user is determined from the
PROXY
privilege held by the proxy user.
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 account definitions:
- -- create proxy account
- -- create proxied account and grant its privileges
- IDENTIFIED BY 'employee_pass';
- -- grant PROXY privilege to proxy account for proxied account
- GRANT PROXY
When a client connects as employee_ext
from
the local host, MySQL uses the plugin named
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. Otherwise,
employee_ext
assumes the privileges of
employee
. The server checks statements
executed during the client session by
employee_ext
against the privileges granted
to employee
. In this case,
employee_ext
can access tables in the
employees
database.
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:
- +------------------------+--------------------+
- +------------------------+--------------------+
- | 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
'
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 auth_string
'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.
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:
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:
MySQL GRANT
and
REVOKE
syntax extensions work as
usual. For example:
The PROXY
privilege can be
granted in these cases:
By a user that has
GRANT PROXY ... WITH GRANT OPTION
forproxied_user
.By
proxied_user
for itself: The value ofUSER()
must exactly matchCURRENT_USER()
andproxied_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:
Those statements create an admin
user that
can manage all GRANT PROXY
mappings. For
example, admin
can do this:
To specify that some or all users should connect using a given
authentication plugin, create a “blank” MySQL
account (''@''
), associate it with that
plugin, 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 account
- -- create proxied accounts
- -- grant PROXY privilege to default proxy account for proxied accounts
Now assume that a client connects as follows:
shell> mysql --user=myuser --password ...
Enter 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 that account 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:
- +------------------+---------------------+
- +------------------+---------------------+
- | 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
.
- +------------------+-------------------+
- +------------------+-------------------+
- | 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.
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
over ''
by the rules that the server uses to
sort account rows internally (see
Section 6.2.6, “Access Control, Stage 1: Connection Verification”).
Suppose that a MySQL installation includes these two accounts:
- -- create default proxy account
- -- create anonymous account
- 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'
: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 proxy user for remote connections
Create the proxied users:
- -- create proxied user for local connections
- IDENTIFIED BY 'some_password';
- -- create proxied user for remote connections
- IDENTIFIED BY 'some_password';
Grant the proxy privilege to each proxy user for the corresponding proxied user:
Finally, grant appropriate privileges to the local and remote proxied users (not shown).
Assume that the
some_plugin
/'some_auth_string'
combination causessome_plugin
to map the client user name todeveloper
. 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.
Some authentication plugins implement proxy user mapping for
themselves (for example, the PAM and Windows authentication
plugins). Other authentication plugins do not support proxy
users by default. Of these, some can request that the MySQL
server itself map proxy users according to granted proxy
privileges: mysql_native_password
,
sha256_password
. If the
check_proxy_users
system
variable is enabled, the server performs proxy user mapping for
any authentication plugins that make such a request:
By default,
check_proxy_users
is disabled, so the server performs no proxy user mapping even for authentication plugins that request server support for proxy users.If
check_proxy_users
is enabled, it may also be necessary to enable plugin-specific system variables to take advantage of server proxy user mapping support:For the
mysql_native_password
plugin, enablemysql_native_password_proxy_users
.For the
sha256_password
plugin, enablesha256_password_proxy_users
.
Proxy user mapping performed by the server is subject to these restrictions:
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 for more than one proxied account, server proxy user mapping is nondeterministic. Therefore, granting to a single account proxy privileges for multiple proxied accounts is discouraged.
Two system variables help trace the proxy login process:
proxy_user
: This value isNULL
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:- +--------------+
- | @@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 theexternal_user
read-only session variable. If the plugin does not set this variable, its value isNULL
.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-proxy-users.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.