分类：
2012-04-05 14:07:03
原文地址：MySQL 访问权限 系统 说明 作者：zzjlzx
The primaryfunction of the MySQL privilege system is to authenticate a user who connectsfrom a given host and to associate that user with privileges on a database suchas SELECT, INSERT, UPDATE, and DELETE. Additional functionality includes theability to have anonymous users and to grant privileges for MySQL-specificfunctions such as LOAD DATA INFILE and administrative operations.
--MySQL 的权限系统主要用来验证用户的操作权限。
There are some things that you cannot dowith the MySQL privilege system:
--权限系统不能做的几件事：
（1） You cannot explicitly specify thata given user should be denied access. That is, you cannot explicitly match auser and then refuse the connection.
（2）You cannot specify that a user hasprivileges to create or drop tables in a database but not to create or drop thedatabase itself.
（3） A password applies globally to anaccount. You cannot associate a password with a specific object such as adatabase, table, or routine.
Internally, theserver stores privilege information in the grant tables of the mysql database(that is, in the database named mysql). The MySQL server reads the contents ofthese tables into memory when it starts and bases access-control decisions onthe in-memory copies of the grant tables.
--在MySQL 内部，权限信息存放在mysql 数据库的granttable里。 当mysql启动后，grant table里的信息会写入内存。
The MySQLprivilege system ensures that all users may perform only the operationspermitted to them. As a user, when you connect to a MySQL server, your identityis determined by the host from which you connect and the user name you specify.When you issue requests after connecting, the system grants privilegesaccording to your identity and what you want to do.
MySQL considersboth your host name and user name in identifying you because there is no reasonto assume that a given user name belongs to the same person on all hosts. Forexample, the user joe who connects from office.example.com need not be the sameperson as the user joe who connects from .
--MySQL 使用user name 和 host name 来作为标识符。
MySQL handlesthis by enabling you to distinguish users on different hosts that happen tohave the same name: You can grant one set of privileges for connections by joe fromoffice.example.com, and a different set of privileges for connections by joe from.
--通过这种标识符，可以用来区分不用host 上的相同的user name。
To see whatprivileges a given account has, use the SHOW GRANTS statement. For example:
SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';
MySQL accesscontrol involves two stages when you run a client program that connects to theserver:
--MySQL 权限控制有2种策略：
Stage 1: Theserver accepts or rejects the connection based on your identity and whether youcan verify your identity by supplying the correct password.
--根据密码是否正确来控制客户端的连接。
Stage 2: Assumingthat you can connect, the server checks each statement you issue to determinewhether you have sufficient privileges to perform it. For example, if you tryto select rows from a table in a database or drop a table from the database,the server verifies that you have the SELECT privilege for the table or the DROPprivilege for the database.
--假设可以正常connect，server还可以检查每个satement是否有权限去执行。如果只有某张表的select 权限，就不能进行drop 操作。
If yourprivileges are changed (either by yourself or someone else) while you areconnected, those changes do not necessarily take effect immediately for thenext statement that you issue.
--当用户正在连接时，如果用户的权限改变，那么不会受影响，当下次执行操作时，就会受影响。
一.Privileges Provided by MySQL
MySQL providesprivileges that apply in different contexts and at different levels ofoperation:
（1） Administrativeprivileges enable users to manage operation of the MySQL server. Theseprivileges are global because they are not specific to a particular database.
-- Administrative privileges 管理整个MySQL server. 该权限是个全局权限，不能指定到某一个数据库。
（2） Databaseprivileges apply to a database and to all objects within it. These privilegescan be granted for specific databases, or globally so that they apply to alldatabases.
-- Database privileges 可以对数据库和其所有的对象， 该权限可以指定某个数据库或者所有数据库。
（3） Privilegesfor database objects such as tables, indexes, views, and stored routines can begranted for specific objects within a database, for all objects of a given typewithin a database (for example, all tables in a database), or globally for allobjects of a given type in all databases).
--objects privileges，对象权限，可以是某个对象的，或者所有数据库对象。
Informationabout account privileges is stored in the user, db, host, tables_priv, columns_priv,and procs_priv tables in the mysql database (see Section 5.4.2, “PrivilegeSystem Grant Tables”). The MySQL server reads the contents of these tables intomemory when it starts and reloads them under the circumstances indicated in Section5.4.6, “When Privilege Changes Take Effect”. Access-control decisions are basedon the in-memory copies of the grant tables.
--账户权限信息包含user,db,host,tables_priv,columns_priv 和procs_priv. 这些信息都存放在mysql 数据库的grant table里。 在MySQLserver 启动时，加载到内存里， 当在对用户权限进行判断时，是根据内存中的grant table的信息进行判断。
Some releases ofMySQL introduce changes to the structure of the grant tables to add new accessprivileges or features. Whenever you update to a new version of MySQL, youshould update your grant tables to make sure that they have the currentstructure so that you can take advantage of any new capabilities. See Section4.4.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.
--在一些MySQL 版本里可能对grant table 进行了改变，所有在对MySQL 升级时，需要用对应的脚本更新grant table。
The followingtable shows the privilege names used at the SQL level in the GRANT and REVOKE statements,along with the column name associated with each privilege in the grant tablesand the context in which the privilege applies.
--下面列出了SQL 级别的用户的权限。
关于这块的更多内容，参考官方文档。
二.Privilege System Grant Tables
Normally, youmanipulate the contents of the grant tables in the mysql database indirectly byusing statements such as GRANT and REVOKE to set up accounts and control theprivileges available to each one. See Section 12.7.1, “Account ManagementStatements”.
The discussionhere describes the underlying structure of the grant tables and how the serveruses their contents when interacting with clients.
These mysql database tables contain grantinformation:
--Mysql 数据库中的如下表包含了授权信息：
（1）user: Contains user accounts, globalprivileges, and other non-privilege columns.
（2） db: Contains database-levelprivileges.
（3） host: Obsolete.
（4） tables_priv: Contains table-levelprivileges.
（5） columns_priv: Containscolumn-level privileges.
（6） procs_priv: Contains storedprocedure and function privileges.
（7） proxies_priv: Contains proxy-userprivileges.
Each grant table contains scope columns andprivilege columns:
每个权限表都包含了scope 和 privilege 列：
（1） Scopecolumns determine the scope of each row (entry) in the tables; that is, thecontext in which the row applies. For example, a user table row with Host and Uservalues of 'thomas.loc.gov' and 'bob' would be used for authenticatingconnections made to the server from the host thomas.loc.gov by a client thatspecifies a user name of bob. Similarly, a db table row with Host, User, and Dbcolumn values of 'thomas.loc.gov', 'bob'and 'reports' would be used when bob connects from the host thomas.loc.gov toaccess the reports database. The tables_priv and columns_priv tables containscope columns indicating tables or table/column combinations to which each rowapplies. The procs_priv scope columns indicate the stored routine to which eachrow applies.
（2） Privilegecolumns indicate which privileges are granted by a table row; that is, whatoperations can be performed. The server combines the information in the variousgrant tables to form a complete description of a user's privileges.
The server uses the grant tables in thefollowing manner:
--在以下情况下会使用grant tables:
（1） The user tablescope columns determine whether to reject or permit incoming connections. Forpermitted connections, any privileges granted in the user table indicate theuser's global privileges. Any privilege granted in this table applies to all databaseson the server.
Note:
Because anyglobal privilege is considered a privilege for all databases, any globalprivilege enables a user to see all database names with SHOW DATABASES or byexamining the SCHEMATA table of INFORMATION_SCHEMA.
（2）The db tablescope columns determine which users can access which databases from whichhosts. The privilege columns determine which operations are permitted. Aprivilege granted at the database level applies to the database and to allobjects in the database, such as tables and stored programs.
（3） The host tableis used in conjunction with the db table when you want a given db table row toapply to several hosts. For example,if you want a user to be able to use adatabase from several hosts in your network, leave the Host value empty in theuser's db table row, then populate the host table with a row for each of thosehosts. Note:
The host tablemust be modified directly with statements such as INSERT, UPDATE, and DELETE.It is not affected by statements such as GRANT and REVOKE that modify the granttables indirectly. Most MySQL installations need not use this table at all.
--host table 必须使用DML 语句直接修改。
（4） The tables_privand columns_priv tables are similar to the db table, but are more fine-grained:They apply at the table and column levels rather than at the database level. Aprivilege granted at the table level applies to the table and to all itscolumns. A privilege granted at the column level applies only to a specificcolumn.
（5） The procs_privtable applies to stored routines. A privilege granted at the routine levelapplies only to a single routine.
（6） The proxies_privtable indicates which users can act as proxies for other users and whetherproxy users can grant the PROXY privilege to other users.
As of MySQL5.5.7, the mysql.user table has plugin and authentication_string columns forstoring authentication plugin information.
If the plugin columnfor an account row is empty, the server uses native authentication forconnection attempts for the account: Clients must match the password in the Passwordcolumn of the account row.
--在MySQL 5.5.7中，user 表加入了pluginauthentication_string 列来存储对应的信息。 如果empty 列为空，那server 使用native验证，即client 的密码必须和password 列中的密码匹配。
If an accountrow names a plugin in the plugin column, the server uses it to authenticateconnection attempts for the account. Whether the plugin uses the value in the Passwordcolumn is up to the plugin.
During thesecond stage of access control, the server performs request verification tomake sure that each client has sufficient privileges for each request that itissues. In addition to the user, db, and host grant tables, the server may alsoconsult the tables_priv and columns_priv tables for requests that involvetables. The latter tables provide finer privilege control at the table and columnlevels.
They have the columns shown in thefollowing table.
The Timestamp andGrantor columns currently are unused and are discussed no further here.
For verificationof requests that involve stored routines, the server may consult the procs_privtable, which has the columns shown in the following table.
The Routine_typecolumn is an ENUM column with values of 'FUNCTION' or 'PROCEDURE' to indicatethe type of routine the row refers to. This column enables privileges to begranted separately for a function and a procedure with the same name.
The Timestamp and Grantor columns currentlyare unused and are discussed no further here.
The proxies_privtable was added in MySQL 5.5.7 and records information about proxy users. Ithas these columns:
--proxies_priv 表包含如下列：
（1） Host, User: These columns indicatethe user account that has the PROXY privilege for the proxied account.
（2） Proxied_host, Proxied_user: Thesecolumns indicate the account of the proxied user.
（3） Grantor: Currently unused.
（4） Timestamp: Currently unused.
（5） With_grant: This column indicateswhether the proxy account can grant the PROXY privilege to other accounts.
Scope columns inthe grant tables contain strings. They are declared as shown here; the defaultvalue for each is the empty string.
Foraccess-checking purposes, comparisons of User, Password, Db, and Table_name valuesare case sensitive. Comparisons of Host, Column_name, and Routine_name valuesare not case sensitive.
In the user, db,and host tables, each privilege is listed in a separate column that is declaredas ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled orenabled, with the default being disabled.
--在user，db 和 host 表中，每个权限都用一个单独的列来控制，列的默认值是N。即没有权限。
In the tables_priv,columns_priv, and procs_priv tables, the privilege columns are declared as SET columns.Values in these columns can contain any combination of the privilegescontrolled by the table. Only those privileges listed in the column value are enabled.
Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table.Administrative operations are operations on the server itself and are notdatabase-specific, so there is no reason to list these privileges in the othergrant tables. Consequently, to determine whether you can perform anadministrative operation, the server need consult only the user table.
--administrative privileges 仅保存在user 表里。
The FILE privilegealso is specified only in the user table. It is not an administrative privilegeas such, but your ability to read or write files on the server host isindependent of the database you are accessing.
--File privilege 也存放在user table里.
The mysqld serverreads the contents of the grant tables into memory when it starts. You can tellit to reload the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladminflush-privileges or mysqladmin reload command. Changes to the grant tables takeeffect as indicated in Section 5.4.6, “When Privilege Changes Take Effect”.
--mysqld server 在启动时讲grant tables 的信息读进内存里。 所以，当我们修改权限之后需要执行flush privileges命令来刷新权限信息，才能使修改生效。 或者执行：mysqladminflush-privileges 或mysqladmin reload。
When you modifyan account's privileges, it is a good idea to verify that the changes set upprivileges the way you want. To check the privileges for a given account, usethe SHOW GRANTS statement. For example, to determine the privileges that aregranted to an account with user name and host name values of bob and pc84.example.com,use this statement:
--当我们修改一个账户的权限之后，最好先使用show grant 验证一下。
mysql> show grants for'dave'@'192.168.3.222';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for dave@192.168.3.222 |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO'dave'@'192.168.3.222' IDENTIFIED BY PASSWORD'*C1D2517835013B47DB24695088326012F0EED75B' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
三.Specifying Account Names
MySQL accountnames consist of a user name and a host name. This enables creation of accountsfor users with the same name who can connect from different hosts. This sectiondescribes how to write account names, including special values and wildcardrules.
--MySQL 帐号由user name 和 host name 组成。
In SQLstatements such as CREATE USER, GRANT, and SET PASSWORD, write account namesusing the following rules:
--修改MySQL 帐号的一些rules：
（1）Syntax for account names is 'user_name'@'host_name'.
--帐号格式
（2） An account name consisting only ofa user name is equivalent to 'user_name'@'%'. For example, 'me' is equivalentto 'me'@'%'.
--帐号可以仅由一个用户名组成，host 使用%来代替，表示所有的host都可以。
（3） The user name and host name neednot be quoted if they are legal as unquoted identifiers. Quotes are necessaryto specify a user_name string containing special characters (such as “-”), or ahost_name string containing special characters or wildcard characters (such as“%”); for example, 'test-user'@'%.com'.
--帐号的user 和host 如果是规则的，可以不使用单引号，如果不规则，如使用特殊符号，或者有通配符，就需要用单引号括起来。
（4） Quote user names and host names asidentifiers or as strings, using either backticks (“`”), single quotation marks(“'”), or double quotation marks (“"”).
（5） The user name and host name parts,if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost';the latter is interpreted as 'me@localhost'@'%'.
（6） A reference to the CURRENT_USER() (orCURRENT_USER) function is equivalent to specifying the current user's name andhost name literally.
--可以使用current_user() 函数查看当前用户信息：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
MySQL storesaccount names in grant tables in the mysql database using separate columns forthe user name and host name parts:
--帐号信息中的user 和host 是分开存放在不同列里的。
（1）The user table contains one row foreach account. The User and Host columns store the user name and host name. Thistable also indicates which global privileges the account has.
（2） Other grant tables indicateprivileges an account has for databases and objects within databases. Thesetables have User and Host columns to store the account name. Each row in thesetables associates with the account in the user table that has the same User andHost values.
User names andhost names have certain special values or wildcard conventions, as describedfollowing.
User 和host 可以使用某个确定的值或者通配符。
A user name iseither a nonblank value that literally matches the user name for incomingconnection attempts, or a blank value (empty string) that matches any username. An account with a blank user name is an anonymous user. To specify ananonymous user in SQL statements, use a quoted empty user name part, such as ''@'localhost'.
--user name 可以是一个nonblank值，或者是一个blank。 如果是blank，则代表一个匿名的帐号。 指定匿名帐号需要使用引号将blank 括起来，如：
‘ ‘@’localhost’
The host namepart of an account name can take many forms, and wildcards are permitted:
--帐号的host 部分可以有很多种格式，也可以使用通配符：
（1） A host value can be a host name oran IP address. The name 'localhost' indicates the local host. The IP address '127.0.0.1'indicates the loopback interface. For the local host, the host value can be theIPv6 address '::1', which indicates the IPv6 loopback interface.
--host 的值可以使用hostname 或者IP 地址。
（2） You can use the wildcardcharacters “%” and “_” in host values. These have the same meaning as forpattern-matching operations performed with the LIKE operator. For example, ahost value of '%' matches any host name, whereas a value of '%.mysql.com' matchesany host in the mysql.com domain. '192.168.1.%' matches any host in the192.168.1 class C network.
--也可以使用通配符，如% 代表多个字符，_代表一个。
Because you canuse IP wildcard values in host values (for example, '192.168.1.%' to matchevery host on a subnet), someone could try to exploit this capability by naminga host 192.168.1.somewhere.com. To foil such attempts, MySQL disallows matchingon host names that start with digits and a dot. Thus, if you have a host namedsomething like 1.2.example.com, its name never matches the host part of accountnames. An IP wildcard value can match only IP addresses, not host names.
（3） For a host value specified as anIP address, you can specify a netmask indicating how many address bits to usefor the network number.
--在指定IP时，也可以指定子网掩码。
This applies onlyfor IPv4 addresses, not IPv6 addresses. The syntax is host_ip/netmask. Forexample:
CREATE USER'david'@'192.58.197.0/255.255.255.0';
This enables davidto connect from any client host having an IP address client_ip for which thefollowing condition is true:
client_ip & netmask= host_ip
That is, for the CREATE USER statement justshown:
client_ip & 255.255.255.0 =192.58.197.0
IP addresses that satisfy this conditionand can connect to the MySQL server are those in the range from 192.58.197.0 to192.58.197.255.
The netmask canonly be used to tell the server to use 8, 16, 24, or 32 bits of the address.Examples:
192.0.0.0/255.0.0.0: Any host on the 192 classA network
192.168.0.0/255.255.0.0: Any host on the192.168 class B network
192.168.1.0/255.255.255.0: Any host on the192.168.1 class C network
192.168.1.1: Only the host with this specificIP address
The followingnetmask will not work because it masks 28 bits, and 28 is not a multiple of 8:
192.168.0.1/255.255.255.240
四. Access Control, Stage 1: ConnectionVerification
--访问控制第一步： 连接验证
When you attemptto connect to a MySQL server, the server accepts or rejects the connectionbased on your identity and whether you can verify your identity by supplyingthe correct password. If not, the server denies access to you completely.Otherwise, the server accepts the connection, and then enters Stage 2 and waitsfor requests.
--当我们打算连接到MySQL server 时，如果我们的帐号和密码都正确，则正常连接，如果不能则拒绝连接。 连接成功后就进入AccessControl 的第二部： request 验证。
Your identity is based on two pieces ofinformation:
（1） The client host from which youconnect
（2） Your MySQL user name
Identitychecking is performed using the three user table scope columns (Host, User, andPassword). The server accepts the connection only if the Host and User columnsin some user table row match the client host name and user name and the clientsupplies the password specified in that row.
If the User columnvalue is nonblank, the user name in an incoming connection must match exactly.If the User value is blank, it matches any user name. If the user table rowthat matches an incoming connection has a blank user name, the user isconsidered to be an anonymous user with no name, not a user with the name thatthe client actually specified. This means that a blank user name is used forall further access checking for the duration of the connection (that is, duringStage 2).
--如果user name 是nonblank，那么就使用username进行精确匹配，如果user name 是blank，则匹配任意用户名。 此时user 可以认为是一个匿名用户。
The Password columncan be blank. This is not a wildcard and does not mean that any passwordmatches. It means that the user must connect without specifying a password. Ifthe server authenticates a client using a plugin, the authentication methodthat the plugin implements may or may not use the password in the Password column.In this case, it is possible that an external password is also used toauthenticate to the MySQL server.
--如果Password 是blank，这不代表可以匹配任何密码，而是说不需要指定密码。 如果server 验证使用了plugin，则验证使用plugin来实现。
Nonblank Passwordvalues in the user table represent encrypted passwords. MySQL does not storepasswords in plaintext form for anyone to see. Rather, the password supplied bya user who is attempting to connect is encrypted (using the PASSWORD() function).
--如果密码是nonblank， 那么在user 表里就是用加密的形式来显示密码。 MySQL 不明文显示任何用户的密码。 所以，在用户连接时也是使用PASSWORD()函数进行加密在连接。 也正是这个原因，我们在用set 修改用户的密码的时候也需要手工的调用password()函数。
The encryptedpassword then is used during the connection process when checking whether thepassword is correct. This is done without the encrypted password ever travelingover the connection. See Section 5.5.1, “User Names and Passwords”. FromMySQL's point of view, the encrypted password is the real password, so youshould never give anyone access to it. In particular, do not givenonadministrative users read access to tables in the mysql database.
--mysql 使用加密的密码来进行验证，因此从MySQL的角度来看，这个加密的密码就是真实的密码，所以不要给非管理员访问mysql 数据库的的权限。
The following table shows how various combinations of Host and User values in the user tableapply to incoming connections.
It is possiblefor the client host name and user name of an incoming connection to match morethan one row in the user table. The preceding set of examples demonstratesthis: Several of the entries shown match a connection from thomas.loc.gov by fred.
这里要注意看一下， 如果我们clienthost 和 user name 在user 表里匹配到多个记录（如使用通配附），那么mysql 是如何处理这种情况的？
When multiplematches are possible, the server must determine which of them to use. Itresolves this issue as follows:
--当匹配到多个可能的结果时，mysqld根据一下原则决定使用哪一个：
（1） Whenever the server reads the usertable into memory, it sorts the rows.
（2） When a client attempts to connect,the server looks through the rows in sorted order.
（3） The server uses the first row thatmatches the client host name and user name.
--mysqld 把user table 加载到内存，然后进行排序，使用第一个匹配到的结果。
The server usessorting rules that order rows with the most-specific Host values first. Literalhost names and IP addresses are the most specific. (The specificity of aliteral IP address is not affected by whether it has a netmask, so 192.168.1.13and 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 “anyhost” but sorts after '%'. Rows with the same Host value are ordered with themost-specific User values first (a blank User value means “any user” and isleast specific).
--大多数情况是根据host 进行排序。
To see how this works, suppose that the usertable looks like this:
--为了演示这种机制是如何工作，先假设user table如下：
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
When the serverreads the table into memory, it sorts the rows using the rules just described.The result after sorting looks like this:
--将user table 加载到内存，在排序，结果如下：
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
When a clientattempts to connect, the server looks through the sorted rows and uses thefirst match found. For a connection from localhost by jeffrey, two of the rowsfrom 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 firstin sorted order, so that is the one the server uses.
--使用第一个匹配到的结果。
Here is another example. Suppose that the usertable looks like this:
--另一个示例：
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| % | jeffrey | ...
| thomas.loc.gov | | ...
+----------------+----------+-
The sorted table looks like this:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| thomas.loc.gov | | ...
| % | jeffrey | ...
+----------------+----------+-
A connection by jeffreyfrom thomas.loc.gov is matched by the first row, whereas a connection by jeffreyfrom any host is matched by the second.
Note
It is a commonmisconception to think that, for a given user name, all rows that explicitlyname that user are used first when the server attempts to find a match for theconnection. This is not true. The preceding example illustrates this, where aconnection from thomas.loc.gov by jeffrey is first matched not by the rowcontaining 'jeffrey' as the User column value, but by the row with no username. As a result, jeffrey is authenticated as an anonymous user, even thoughhe specified a user name when connecting.
If you are ableto connect to the server, but your privileges are not what you expect, youprobably are being authenticated as some other account. To find out whataccount the server used to authenticate you, use the CURRENT_USER() function. Itreturns a value in user_name@host_name format that indicates the User and Host valuesfrom the matching user table row.
--如果我们已经成功连接到server，但是此时的权限和我们期望的不一样，那么我们可能用其他的用户通过的验证。 可以通过current_user()函数来查看用户的信息：
Suppose that jeffrey connects and issuesthe following query:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
The result shownhere 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 todiagnose authentication problems is to print out the user table and sort it byhand to see where the first match is being made.
五. Access Control, Stage 2: RequestVerification
After youestablish a connection, the server enters Stage 2 of access control. For eachrequest that you issue through that connection, the server determines whatoperation you want to perform, then checks whether you have sufficientprivileges to do so. This is where the privilege columns in the grant tablescome into play. These privileges can come from any of the user, db, host, tables_priv,columns_priv, or procs_priv tables.
--连接上mysql server 之后，发布的每条权限都需要验证是否有足够的权限来执行。
The user tablegrants privileges that are assigned to you on a global basis and that apply nomatter what the default database is. For example, if the user table grants youthe DELETE privilege, you can delete rows from any table in any database on theserver host! It is wise to grant privileges in the user table only to peoplewho need them, such as database administrators. For other users, you should leaveall privileges in the user table set to 'N' and grant privileges at morespecific levels only. You can grant privileges for particular databases,tables, columns, or routines.
--mysql 数据库下的user table 的权限是global 级别的，会应用到所有的数据库。 比如我们在usertable 赋给了某个用户delete 权限，那么这个用户就可以删除任何数据库里的记录，所以对于user table里的权限，最好仅对管理员开放。 其他用户的权限从指定的级别来指定。
The db and host tablesgrant database-specific privileges. Values in the scope columns of these tablescan take the following forms:
--db 和 host 表的权限都是数据库级别的。可以通过如下方式获取scopecolumns的值：
（1）A blank User value in the db tablematches the anonymous user. A nonblank value matches literally; there are nowildcards in user names.
--blank user 匹配匿名用户，nonblank 匹配具体的用户，用户名不可以使用通配符。
（2） The wildcard characters “%” and “_”can be used in the Host and Db columns of either table. These have the samemeaning as for pattern-matching operations performed with the LIKE operator. Ifyou want to use either character literally when granting privileges, you mustescape it with a backslash. For example, to include the underscore character (“_”)as part of a database name, specify it as “\_” in the GRANT statement.
--host 可以使用通配符。
（3） A '%' Host value in the db tablemeans “any host.” A blank Host value in the db table means “consult the host tablefor further information” (a process that is described later in this section).
（4） A '%' or blank Host value in the hosttable means “any host.”
（5） A '%' or blank Db value in eithertable means “any database.”
The server readsthe db and host tables into memory and sorts them at the same time that itreads the user table. The server sorts the db table based on the Host, Db, and Userscope columns, and sorts the host table based on the Host and Db scope columns.As with the user table, sorting puts the most-specific values first andleast-specific values last, and when the server looks for matching entries, ituses the first match that it finds.
--server从db 和host 表里读取数据，加载到内存之后进行培训。 对于dbtable 根据host，db 和user scope column 进行培训，对于host table，根据host 和dbscope 进行排序。
The tables_priv,columns_priv, and procs_priv tables grant table-specific, column-specific, androutine-specific privileges. Values in the scope columns of these tables cantake the following forms:
--这几张表是具体级别的权限管理。其值可以通过一下几种情况指定：
（1） The wildcard characters “%” and “_”can be used in the Host column. These have the same meaning as forpattern-matching operations performed with the LIKE operator.
（2）A '%' or blank Host value means“any host.”
（3） The Db, Table_name, Column_name,and Routine_name columns cannot contain wildcards or be blank.
The server sortsthe tables_priv, columns_priv, and procs_priv tables based on the Host, Db, andUser columns. This is similar to db table sorting, but simpler because only theHost column can contain wildcards.
--server 对tables_priv, columns_priv,and procs_priv 表的排序根据host，db 和 user columns，但只有host column 可以使用通配符。
The server usesthe sorted tables to verify each request that it receives. For requests thatrequire administrative privileges such as SHUTDOWN or RELOAD, the server checksonly the user table row because that is the only table that specifiesadministrative privileges.
--server 使用排序之后的结果来验证每一个请求。
The servergrants access if the row permits the requested operation and denies accessotherwise. For example, if you want to execute mysqladmin shutdown but your usertable row does not grant the SHUTDOWN privilege to you, the server deniesaccess without even checking the db or host tables. (They contain no Shutdown_privcolumn, so there is no need to do so.)
Fordatabase-related requests (INSERT, UPDATE, and so on), the server first checksthe user's global privileges by looking in the user table row. If the rowpermits the requested operation, access is granted. If the global privileges inthe user table are insufficient, the server determines the user'sdatabase-specific privileges by checking the db and host tables:
--当请求设计到数据库级别时，server 会先根据user table来检查global级别的权限，如果通过，则允许用户的操作。 如果权限不足，则根据db 和 host 表来进行检查。
1. The serverlooks in the db table for a match on the Host, Db, and User columns. The Host andUser columns are matched to the connecting user's host name and MySQL username. The Db column is matched to the database that the user wants to access.If there is no row for the Host and User, access is denied.
--server查看db table。 其中的host 和 user 列匹配用户的hostname 和 user name。 db 表的db 列匹配数据库的权限。 如果没有记录，则拒绝用户的操作。
2. If there is amatching db table row and its Host column is not blank, that row defines theuser's database-specific privileges.
--如果匹配到了记录，并且hosts columsn 非空，则row 被定义为database-specificprivileges。
3. If thematching db table row's Host column is blank, it signifies that the host tableenumerates which hosts should be permitted access to the database. In thiscase, a further lookup is done in the host table to find a match on the Host andDb columns. If no host table row matches, access is denied. If there is amatch, the user's database-specific privileges are computed as the intersection(not the union!) of the privileges in the db and host table entries; that is,the privileges that are 'Y' in both entries. (This way you can grant generalprivileges in the db table row and then selectively restrict them on ahost-by-host basis using the host table entries.)
Afterdetermining the database-specific privileges granted by the db and host tableentries, the server adds them to the global privileges granted by the user table.If the result permits the requested operation, access is granted. Otherwise,the server successively checks the user's table and column privileges in the tables_privand columns_priv tables, adds those to the user's privileges, and permits ordenies access based on the result. For stored-routine operations, the serveruses the procs_priv table rather than tables_priv and columns_priv.
--当确定database-specific当前权限之后，server 添加这些权限到全局的user table里。 如果添加成功，则允许访问。 否则，server 将从tables_priv 和columns_priv 表，将权限添加到user’s privileges. 对于stored-routine， server使用procs_priv表。
Expressed in boolean terms, the precedingdescription of how a user's privileges are calculated may be summarized likethis:
根据一下来计算用户的权限：
global privileges
OR (database privileges AND hostprivileges)
OR table privileges
OR column privileges
OR routine privileges
It may not beapparent why, if the global user row privileges are initially found to beinsufficient for the requested operation, the server adds those privileges tothe database, table, and column privileges later. The reason is that a requestmight require more than one type of privilege. For example, if you execute an INSERTINTO ... SELECT statement, you need both the INSERT and the SELECT privileges.Your privileges might be such that the user table row grants one privilege andthe db table row grants the other. In this case, you have the necessaryprivileges to perform the request, but the server cannot tell that from eithertable by itself; the privileges granted by the entries in both tables must becombined.
--如果globaluser 没有足够的权限去执行操作，那么server 会自动添加这些需要的权限。
The host table isnot affected by the GRANT or REVOKE statements, so it is unused in most MySQLinstallations. If you modify it directly, you can use it for some specializedpurposes, such as to maintain a list of secure servers on the local networkthat are granted all privileges.
--grant 和 revoke操作不影响 host table里的记录。 所以在MySQL安装时不使用hosttable。 但是在一些特殊场合，我们可以直接修改host table。
You can also usethe host table to indicate hosts that are not secure. Suppose that you have amachine public.your.domain that is located in a public area that you do notconsider secure. You can enable access to all hosts on your network except thatmachine by using host table entries like this:
--可以使用host table 来指定一些不安全的hosts，因为host 里的权限不grant和 revoke 的影响。
+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | % | ... (allprivileges set to 'N')
| %.your.domain | % | ... (all privilegesset to 'Y')
+--------------------+----+-
六. When Privilege Changes Take Effect
When mysqld starts,it reads all grant table contents into memory. The in-memory tables becomeeffective for access control at that point.
--当我们启动mysqld 后，server 会读取所有的granttable 到内存。
If you modifythe grant tables indirectly using account-management statements such as GRANT, REVOKE,SET PASSWORD, or RENAME USER, the server notices these changes and loads thegrant tables into memory again immediately.
--当我们通过全新管理命令直接修改grant table， server 接到通知后立即重新装载grant tables。
If you modifythe grant tables directly using statements such as INSERT, UPDATE, or DELETE,your changes have no effect on privilege checking until you either restart theserver or tell it to reload the tables. If you change the grant tables directlybut forget to reload them, your changes have no effect until you restart theserver. This may leave you wondering why your changes seem to make nodifference!
如果我们直接使用SQL语句来修改grant table， 那么就必须手工的flush 或者reloadgrant tables。
To tell theserver to reload the grant tables, perform a flush-privileges operation. Thiscan be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladminflush-privileges or mysqladmin reload command.
--手工刷新的命令是：FLUSH PRIVILEGES 或者执行mysqladminflush-privileges 和mysqladmin reload
A grant table reload affects privileges foreach existing client connection as follows:
--对于已经连接上的session，grantre load 之后也需要在以下操作才能生效：
（1）Table and column privilege changestake effect with the client's next request.
（2） Database privilege changes takeeffect the next time the client executes a USE db_name statement.
Note
Clientapplications may cache the database name; thus, this effect may not be visibleto them without actually changing to a different database or flushing theprivileges.
（3） Global privileges and passwordsare unaffected for a connected client. These changes take effect only forsubsequent connections.
If the server isstarted with the --skip-grant-tables option, it does not read the grant tablesor implement any access control.
--如果启动server 时指定了—skip-grant-tables 参数，那么将不读取grant table或者不实现任何权限管理。
Anyone canconnect and do anything, which is insecure. To cause a server thus started toread the tables and enable access checking, flush the privileges.
--这样，任何人都可以连接并做任何事情，这样非常不安全。
七.Causes of Access-Denied Errors
官方文档上一些相关案例处理的小节，具体参考，这里截取几个案例来说明一下。
7.1 The grant tables must be properly set upso that the server can use them for access control. For some distribution types(such as binary distributions on Windows, or RPM distributions on Linux), theinstallation process initializes the mysql database containing the granttables. For distributions that do not do this, you must initialize the granttables manually by running the mysql_install_db script.
--grant table 必须要安装，如果使用RPM 来安装，默认会安装grant table，如果使用distributions 来安装，则没有安装grant table，那么就必须手工运行mysql_install_db 脚本来安装grant tables。
To determinewhether you need to initialize the grant tables, look for a mysql directoryunder the data directory. (The data directory normally is named data or var andis located under your MySQL installation directory.) Make sure that you have afile named user.MYD in the mysql database directory. If not, execute the mysql_install_dbscript. After running this script and starting the server, test the initialprivileges by executing this command:
shell> mysql -u root test
7.2 Ifyou have updated an existing MySQL installation to a newer version, did you runthe mysql_upgrade script? If not, do so. The structure of the grant tableschanges occasionally when new capabilities are added, so after an upgrade youshould always make sure that your tables have the current structure.
--如果升级了MySQL，那么grant tables的相关结构可能发生改变，需要运行mysql_upgrade 脚本。
7.3 Aftera fresh installation, you should connect to the server and set up your usersand their access permissions:
shell> mysql -u root mysql
The servershould let you connect because the MySQL root user has no password initially.That is also a security risk, so setting the password for the root accounts issomething you should do while you're setting up your other MySQL accounts.
--对于刚安装的MySQL,默认的root 用户没有密码，这样也是不安全的，在登陆以后，我们需要修改root用户的密码。
7.4 If you change a password by using SETPASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD()function. If you do not use PASSWORD() for these statements, the password willnot work.
--如果我们通过set password ，insert 或者update来修改密码，必须使用加密函数。
For example, the following statementassigns a password, but fails to encrypt it, so the user is not able to connectafterward:
SET PASSWORD FOR 'abe'@'host_name' ='eagle';
Instead, set the password like this:
SET PASSWORD FOR 'abe'@'host_name' =PASSWORD('eagle');
--正确的修改密码方式
The PASSWORD() functionis unnecessary when you specify a password using the CREATE USER or GRANT statementsor the mysqladmin password command. Each of those automatically uses PASSWORD()to encrypt the password.
--如果使用create user 或者grant 语句，或者mysqladminpassword 来修改命令，那么不需要使用password() 函数，因为这些命令会自动调用密码函数。
7.5 如果连接远程的MySQL, 那么就需要在mysql 对这个连接进行赋权。 Mysql的连接ID 由username 和 host 组成。 如：
$ mysql -u root -p
Enter password:
mysql> use mysql
mysql> grant all on *.* to'dave'@'192.168.3.222' identified by 'dave';
--grant 赋权不需要指定密码函数。
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
