Movatterモバイル変換

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

• Requirements for Proxy User Support

• Simple Proxy User Example

• Preventing Direct Login to Proxied Accounts

• Granting and Revoking the PROXY Privilege

• Default Proxy Users

• Default Proxy User and Anonymous User Conflicts

• Server Support for Proxy User Mapping

• Proxy User System Variables

One administrative benefit to be gained by proxying is that the DBA can set up a single account with a set of privileges and then enable multiple proxy users to have those privileges without having to assign the privileges individually to each of those users. As an alternative to proxy users, DBAs may find that roles provide a suitable way to map users onto specific sets of named privileges. Each user can be granted a given single role to, in effect, be granted the appropriate set of privileges. SeeSection 8.2.10, “Using Roles”.

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

The proxy mechanism permits mapping only the external client user name to the proxied user name. There is no provision for mapping host names:

Consider the following account definitions:

-- create proxy accountCREATE USER 'employee_ext'@'localhost'  IDENTIFIED WITH my_auth_plugin  AS 'my_auth_string';-- create proxied account and grant its privileges;-- use mysql_no_login plugin to prevent direct loginCREATE USER 'employee'@'localhost'  IDENTIFIED WITH mysql_no_login;GRANT ALL  ON employees.*  TO 'employee'@'localhost';-- grant to proxy account the-- PROXY privilege for proxied accountGRANT PROXY  ON 'employee'@'localhost'  TO 'employee_ext'@'localhost';

When a client connects asemployee_ext from the local host, MySQL uses the plugin namedmy_auth_plugin to perform authentication. Suppose thatmy_auth_plugin returns a user name ofemployee to the server, based on the content of'my_auth_string' and perhaps by consulting some external authentication system. The nameemployee differs fromemployee_ext, so returningemployee serves as a request to the server to treat theemployee_ext external user, for purposes of privilege checking, as theemployee local user.

In this case,employee_ext is the proxy user andemployee is the proxied user.

The server verifies that proxy authentication foremployee is possible for theemployee_ext user by checking whetheremployee_ext (the proxy user) has thePROXY privilege foremployee (the proxied user). If this privilege has not been granted, an error occurs. Otherwise,employee_ext assumes the privileges ofemployee. The server checks statements executed during the client session byemployee_ext against the privileges granted toemployee. In this case,employee_ext can access tables in theemployees database.

The proxied account,employee, uses themysql_no_login authentication plugin to prevent clients from using the account to log in directly. (This assumes that the plugin is installed. For instructions, seeSection 8.4.1.8, “No-Login Pluggable Authentication”.) For alternative methods of protecting proxied accounts against direct use, seePreventing Direct Login to Proxied Accounts.

When proxying occurs, theUSER() andCURRENT_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 theCREATE USER statement that creates the proxy user account, theIDENTIFIED WITH clause that names the proxy-supporting authentication plugin is optionally followed by anAS '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 proxy (external) client user name to a proxied user name. It is up to each plugin whether it requires theAS 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.

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.

There are multiple ways to ensure that a proxied account cannot be used directly:

ThePROXY privilege is needed to enable an external user to connect as and have the privileges of another user. To grant this privilege, use theGRANT statement. For example:

GRANT PROXY ON 'proxied_user' TO 'proxy_user';

The statement creates a row in themysql.proxies_priv grant table.

At connect time,proxy_user must represent a valid externally authenticated MySQL user, andproxied_user must represent a valid locally authenticated user. Otherwise, the connection attempt fails.

The correspondingREVOKE syntax is:

REVOKE PROXY ON 'proxied_user' FROM 'proxy_user';

MySQLGRANT andREVOKE syntax extensions work as usual. Examples:

-- grant PROXY to multiple accountsGRANT PROXY ON 'a' TO 'b', 'c', 'd';-- revoke PROXY from multiple accountsREVOKE PROXY ON 'a' FROM 'b', 'c', 'd';-- grant PROXY to an account and enable the account to grant-- PROXY to the proxied accountGRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION;-- grant PROXY to default proxy accountGRANT PROXY ON 'a' TO ''@'';

ThePROXY privilege can be granted in these cases:

The initialroot account created during MySQL installation has thePROXY ... WITH GRANT OPTION privilege for''@'', that is, for all users and all hosts. This enablesroot 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 'admin_password';GRANT PROXY  ON ''@''  TO 'admin'@'localhost'  WITH GRANT OPTION;

Those statements create anadmin user that can manage allGRANT 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 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 plugin namedldap_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 accountCREATE USER ''@''  IDENTIFIED WITH ldap_auth  AS 'O=Oracle, OU=MySQL';-- create proxied accounts; use-- mysql_no_login plugin to prevent direct loginCREATE USER 'developer'@'localhost'  IDENTIFIED WITH mysql_no_login;CREATE USER 'manager'@'localhost'  IDENTIFIED WITH mysql_no_login;-- grant to default proxy account the-- PROXY privilege for proxied accountsGRANT PROXY  ON 'manager'@'localhost'  TO ''@'';GRANT PROXY  ON 'developer'@'localhost'  TO ''@'';

Now assume that a client connects as follows:

$> mysql --user=myuser --password ...Enter password:myuser_password

The server does not findmyuser 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 theldap_auth authentication plugin and passesmyuser andmyuser_password to it as the user name and password.

If theldap_auth plugin finds in the LDAP directory thatmyuser_password is not the correct password formyuser, authentication fails and the server rejects the connection.

If the password is correct andldap_auth finds thatmyuser is a developer, it returns the user namedeveloper to the MySQL server, rather thanmyuser. Returning a user name different from the client user name ofmyuser signals to the server that it should treatmyuser as a proxy. The server verifies that''@'' can authenticate asdeveloper (because''@'' has thePROXY privilege to do so) and accepts the connection. The session proceeds withmyuser having the privileges of thedeveloper proxied user. (These privileges should be set up by the DBA usingGRANT statements, not shown.) TheUSER() andCURRENT_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 thatmyuser is a manager, it returnsmanager as the user name and the session proceeds withmyuser having the privileges of themanager proxied user.

mysql> SELECT USER(), CURRENT_USER();+------------------+-------------------+| USER()           | CURRENT_USER()    |+------------------+-------------------+| myuser@localhost | manager@localhost |+------------------+-------------------+

For simplicity, external authentication cannot be multilevel: Neither the credentials fordeveloper nor those formanager are taken into account in the preceding example. However, they are still used if a client tries to connect and authenticate directly as thedeveloper ormanager account, which is why those proxied accounts should be protected against direct login (seePreventing 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 over'' by the rules that the server uses to sort account rows internally (seeSection 8.2.6, “Access Control, Stage 1: Connection Verification”).

Suppose that a MySQL installation includes these two accounts:

-- create default proxy accountCREATE USER ''@''  IDENTIFIED WITH some_plugin  AS 'some_auth_string';-- create anonymous accountCREATE 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 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). As a result, the default proxy account is never used.

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

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.sha256_password can request that the MySQL server itself map proxy users according to granted proxy privileges. If thecheck_proxy_users system variable is enabled, the server performs proxy user mapping for any authentication plugins that make such a request:

For example, to enable all the preceding capabilities, start the server with these lines in themy.cnf file:

[mysqld]check_proxy_users=ONsha256_password_proxy_users=ON

Assuming that the relevant system variables have been enabled, create the proxy user as usual usingCREATE USER, then grant it thePROXY privilege to a single other account to be treated as the proxied user. When the server receives a successful connection request for the proxy user, it finds that the user has thePROXY privilege and uses it to determine the proper proxied user.

-- create proxy accountCREATE USER 'proxy_user'@'localhost'  IDENTIFIED WITH sha256_password  BY 'password';-- create proxied account and grant its privileges;-- use mysql_no_login plugin to prevent direct loginCREATE USER 'proxied_user'@'localhost'  IDENTIFIED WITH mysql_no_login;-- grant privileges to proxied accountGRANT ...  ON ...  TO 'proxied_user'@'localhost';-- grant to proxy account the-- PROXY privilege for proxied accountGRANT PROXY  ON 'proxied_user'@'localhost'  TO 'proxy_user'@'localhost';

To use the proxy account, connect to the server using its name and password:

$> mysql -u proxy_user -pEnter password:(enter proxy_user password here)

Authentication succeeds, the server finds thatproxy_user has thePROXY privilege forproxied_user, and the session proceeds withproxy_user having the privileges ofproxied_user.

Proxy user mapping performed by the server is subject to these restrictions:

Two system variables help trace the proxy login process: