mysql的连接名和用户名_MySQL登陆认证用户名先后顺序

下面是从官档中摘抄的关于MySQL在登陆认证时，从user表选择用户验证的先后顺序。

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

-> WITH GRANT OPTION;

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'

-> WITH GRANT OPTION;

Two accounts have a user name of monty and a password of some_pass. Both are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

上面创建的两个monty用户都是具有所有权限的超级用户。其中monty@localhost用户只能从本地主机连接数据库，monty@%用户可以从任意主机连接数据库。

The 'monty'@'localhost' account is necessary if there is an anonymous-user account for localhost. Without the 'monty'@'localhost' account, that anonymous-user account takes precedence when monty connects from the local host and monty is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

如果数据库存在只供本地主机访问的匿名用户(''@'localhost'),那么monty@localhost账户就是必须的。如果没有monty@localhost账户，当monty用户在本地主机建立数据库连接时，匿名账户的优先级更高，这时monty用户会被作为匿名用户处理。原因为：本地匿名账户(''@'localhost')比'monty'@'%'账户具有更具体的主机列值，从而在用户表排序中位置出现靠前。

Your identity is based on two pieces of information:

The client host from which      you connect

Your MySQL user name

It is possible for the client host name and user name of an incoming connection to match more than one row in the user table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from thomas.loc.gov by fred.

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

Whenever the server reads the user table into memory, it sorts the rows.

When a client attempts to connect, the server looks through the rows      in sorted order.

The server uses the first row that matches the client host name and      user name.

MySQL数据库是通过下面两条信息来验证登陆标识的：

连接过来的主机信息

连接过来的MySQL用户名

当连接MySQL时，有多个登陆标识被匹配上，MySQL服务按照下面的方法来决定哪条匹配信息被使用：

无论何时MySQL服务都读取user表到内存中，并对其排序

当一个客户端尝试连接MySQL数据库，MySQL服务浏览排序后的user表数据

MySQL服务使用匹配上host和user的第一行数据

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is indeterminate.

MySQL服务对user表的排序规则为有最具体的host值排到首位。文本形式的主机名和ip地址是最具体的。(文本形式的IP地址不受netmask的影响，因此192.168.1.13和192.168.1.0/255.255.255.0被认为同样具体的。)'%'形式表示“any host”，是最少具体的，因此排序位置靠后。而空字符串('')也表示“any host”，但是排序在'%'之后。如果user表排序后host值一样，那么有最具体的user值的行排到首位(user值为空时表示“any user”并且是最少具体的)。对于同样具体的host和user值，在user表中的排序是不确定的。

To see how this works, suppose that the user table looks like this:

+-----------+----------+-

| Host      | User     | ...

+-----------+----------+-

| %         | root     | ...

| %         | jeffrey  | ...

| localhost | root     | ...

| localhost |          | ...

+-----------+----------+-

When the server reads the table into memory, it sorts the rows using the rules just described. The result after sorting looks like this:

当MySQL服务读取上面的user表到内存中，使用上面描述的排序规则对行进行排序。排序后的结果如下：

+-----------+----------+-

| Host      | User     | ...

+-----------+----------+-

| localhost | root     | ...

| localhost |          | ...

| %         | jeffrey  | ...

| %         | root     | ...

+-----------+----------+-

When a client attempts to connect, the server looks through the sorted rows and uses the first match found. For a connection from localhost by jeffrey, two of the rows from the table match: the one with Host and User values of 'localhost' and '', and the one with values of '%' and 'jeffrey'. The 'localhost' row appears first in sorted order, so that is the one the server uses.

当一个客户端尝试连接MySQL，MySQL服务去过滤排序后的user表，并使用第一个被匹配到的行(通过host和user去匹配)。对于一个来自localhost用户名为jeffrey的连接，user表中两行被匹配到：一行是host和user值分别为'localhost'和''，一行是host和user值分别为'%'和'jeffrey'。'localhost'行出现在排序后的user表的前面位置，因此host和user值为'localhost'和''的用户标识被MySQL服务使用。

Here is another example. Suppose that the user table looks like this:

+----------------+----------+-

| Host           | User     | ...

+----------------+----------+-

| %              | jeffrey  | ...

| thomas.loc.gov |          | ...

+----------------+----------+-

The sorted table looks like this:

+----------------+----------+-

| Host           | User     | ...

+----------------+----------+-

| thomas.loc.gov |          | ...

| %              | jeffrey  | ...

+----------------+----------+-

A connection by jeffrey from thomas.loc.gov is matched by the first row, whereas a connection by jeffrey from any host is matched by the second.

来自thomas.loc.gov主机用户名为jeffrey的连接匹配到上表的第一行数据，反之来自其他主机用户名为jeffrey的连接匹配到第二行数据。

Note

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.

一个常见的误解是,对于一个给定的用户名,当MySQL服务尝试去匹配连接所用的用户标识时，应该优先使用包含明确名称的行。事实并非如此。前面的例子说明了这一点，来自thomas.loc.gov主机用户名为jeffrey的连接第一个匹配的不是user列值包含'jeffrey'的行，而是user列值为空的行。结果，jeffrey被作为匿名用户去认证，即使在连接时指定了明确的user值。

If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER() function. (See Section 12.14, “Information Functions”.) It returns a value in user_name@host_name format that indicates the User and Host values from the matching user table row. Suppose that jeffrey connects and issues the following query:

mysql> SELECT CURRENT_USER();

+----------------+

| CURRENT_USER() |

+----------------+

| @localhost     |

+----------------+

The result shown here indicates that the matching user table row had a blank User column value. In other words, the server is treating jeffrey as an anonymous user.

Another way to diagnose authentication problems is to print out the user table and sort it by hand to see where the first match is being made.