应用程序可以使用以下指南来执行基于 SQL 的审计，将数据库活动与 MySQL 帐户关联起来。Applications can use the following guidelines to perform SQL-based auditing that ties database activity to MySQL accounts.

MySQL 账户对应于 mysql.user 系统表中的行。当客户端成功连接时，服务器会将客户端认证到该表中的特定行。该行中的 User 和 Host 列的值唯一标识该账户，并对应于 SQL 语句中编写账户名称的 'user_name'@'host_name' 格式。MySQL accounts correspond to rows in the mysql.user system table. When a client connects successfully, the server authenticates the client to a particular row in this table. The User and Host 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.

用于验证客户端的账户决定了客户端拥有哪些权限。通常，可以调用 CURRENT_USER() 函数来确定这个客户端用户的账户。这一值是由该账户在 user 表中的 User 和 Host 列构成的。The account used to authenticate a client determines which privileges the client has. Normally, the CURRENT_USER() function can be invoked to determine which account this is for the client user. Its value is constructed from the User and Host columns of the user table row for the account.

然而，在某些情况下，CURRENT_USER() 的值对应的并不是客户端用户，而是其他账户。这种情况发生在权限检查不是基于客户端账户的上下文中：However, there are circumstances under which the CURRENT_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:

在这些情况下，权限检查是针对 DEFINER 账户进行的，而 CURRENT_USER() 指的也是该账户，而不是调用存储例程或视图，或触发触发器的客户端账户。要确定调用用户，可以调用 USER() 函数，该函数返回一个值，指示客户端提供的实际用户名以及客户端连接的主机。然而，这个值不一定直接对应 user 表中的账户，因为 USER() 值从不包含通配符，而账户值（由 CURRENT_USER() 返回）可能包含用户名和主机名通配符。In those contexts, privilege checking is done against the DEFINER account and CURRENT_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 the USER() 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 the user table, because the USER() value never contains wildcards, whereas account values (as returned by CURRENT_USER()) may contain user name and host name wildcards.

例如，一个空的用户名可以匹配任何用户，所以一个''@'localhost'的账户允许客户端以本地主机上的匿名用户身份连接，并使用任意用户名。在这种情况下，如果客户端以user1从本地主机连接，USER()和CURRENT_USER()返回不同的值：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 as user1 from the local host, USER() and CURRENT_USER() return different values:

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

账户的主机名部分也可以包含通配符。如果主机名包含 '%' 或 '_' 模式字符，或使用子网掩码表示法，则该账户可以用于从多个主机连接的客户端，并且 CURRENT_USER() 的值不会指明具体是哪一个。例如，账户 'user2'@'%.example.com' 可以让 user2 从 example.com 域中的任何主机连接。如果 user2 从 remote.example.com 连接，USER() 和 CURRENT_USER() 返回不同的值：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 the CURRENT_USER() value does not indicate which one. For example, the account 'user2'@'%.example.com' can be used by user2 to connect from any host in the example.com domain. If user2 connects from remote.example.com, USER() and CURRENT_USER() return different values:

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

如果一个应用程序必须调用 USER() 进行用户审计（例如，如果它从触发器内部进行审计），但也必须能够将 USER() 的值与 user 表中的账号关联，则需要避免 User 或 Host 列中包含通配符的账号。具体来说，不允许 User 为空（这会创建匿名用户账号），也不允许 Host 值中含有模式字符或子网掩码表示法。所有账号必须具有非空的 User 值和字面的 Host 值。If an application must invoke USER() for user auditing (for example, if it does auditing from within triggers) but must also be able to associate the USER() value with an account in the user table, it is necessary to avoid accounts that contain wildcards in the User or Host column. Specifically, do not permit User to be empty (which creates an anonymous-user account), and do not permit pattern characters or netmask notation in Host values. All accounts must have a nonempty User value and literal Host value.

关于之前的示例，''@'localhost' 和 'user2'@'%.example.com' 账户应该修改为不使用通配符：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';

如果 user2 必须能够从 example.com 域中的多个主机连接，则每个主机应该有一个单独的账户。If user2 must be able to connect from several hosts in the example.com domain, there should be a separate account for each host.

要从 CURRENT_USER() 或 USER() 的值中提取用户名或主机名部分，请使用 SUBSTRING_INDEX() 函数：To extract the user name or host name part from a CURRENT_USER() or USER() value, use the SUBSTRING_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                              |
+----------------------------------------+