Movatterモバイル変換

Applications can use the following guidelines to perform SQL-based auditing that ties database activity to MySQL accounts.

MySQL accounts correspond to rows in themysql.user system table. When a client connects successfully, the server authenticates the client to a particular row in this table. TheUser andHost column values in this row uniquely identify the account and correspond to the'user_name'@'host_name' format in which account names are written in SQL statements.

The account used to authenticate a client determines which privileges the client has. Normally, theCURRENT_USER() function can be invoked to determine which account this is for the client user. Its value is constructed from theUser andHost columns of theuser table row for the account.

However, there are circumstances under which theCURRENT_USER() value corresponds not to the client user but to a different account. This occurs in contexts when privilege checking is not based the client's account:

In those contexts, privilege checking is done against theDEFINER account andCURRENT_USER() refers to that account, not to the account for the client who invoked the stored routine or view or who caused the trigger to activate. To determine the invoking user, you can call theUSER() function, which returns a value indicating the actual user name provided by the client and the host from which the client connected. However, this value does not necessarily correspond directly to an account in theuser table, because theUSER() value never contains wildcards, whereas account values (as returned byCURRENT_USER()) may contain user name and host name wildcards.

For example, a blank user name matches any user, so an account of''@'localhost' enables clients to connect as an anonymous user from the local host with any user name. In this case, if a client connects asuser1 from the local host,USER() andCURRENT_USER() return different values:

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

The host name part of an account can also contain wildcards. If the host name contains a'%' or'_' pattern character or uses netmask notation, the account can be used for clients connecting from multiple hosts and theCURRENT_USER() value does not indicate which one. For example, the account'user2'@'%.example.com' can be used byuser2 to connect from any host in theexample.com domain. Ifuser2 connects fromremote.example.com,USER() andCURRENT_USER() return different values:

mysql> SELECT USER(), CURRENT_USER();+--------------------------+---------------------+| USER()                   | CURRENT_USER()      |+--------------------------+---------------------+| user2@remote.example.com | user2@%.example.com |+--------------------------+---------------------+

If an application must invokeUSER() for user auditing (for example, if it does auditing from within triggers) but must also be able to associate theUSER() value with an account in theuser table, it is necessary to avoid accounts that contain wildcards in theUser orHost column. Specifically, do not permitUser to be empty (which creates an anonymous-user account), and do not permit pattern characters or netmask notation inHost values. All accounts must have a nonemptyUser value and literalHost value.

With respect to the previous examples, the''@'localhost' and'user2'@'%.example.com' accounts should be changed not to use wildcards:

RENAME USER ''@'localhost' TO 'user1'@'localhost';RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';

Ifuser2 must be able to connect from several hosts in theexample.com domain, there should be a separate account for each host.

To extract the user name or host name part from aCURRENT_USER() orUSER() value, use theSUBSTRING_INDEX() function:

mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);+---------------------------------------+| SUBSTRING_INDEX(CURRENT_USER(),'@',1) |+---------------------------------------+| user1                                 |+---------------------------------------+mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);+----------------------------------------+| SUBSTRING_INDEX(CURRENT_USER(),'@',-1) |+----------------------------------------+| localhost                              |+----------------------------------------+