Mysql 访问控制2 Request Verification

6.2.5 Access Control, Stage 2: Request Verification

After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db,tables_priv, columns_priv, or procs_priv tables. (You may find it helpful to refer to Section 6.2.2, “Grant Tables”, which lists the columns present in each of the grant tables.)    >>>>当完Connection Verification后，我们就成功建立了到数据库实例的连接。这时我们就进入了访问控制的第二个阶段(Request Verification)。连接建立后你提交请求(简单的说就是你要执行的sql语句)，mysql会先判断当前用户是否有权限去执行你提交的语句。这时就是权限表发挥作用的时候了(user,db,tables_priv,columns_priv,procs_priv,proxies_priv,关于这些表的具体信息，你可以查看官方文档中Section 6.2.2, “Grant Tables”部分)

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the default database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! It is wise to grant privileges in the user table only to people who need them, such as database administrators. For other users, you should leave all privileges in the user table set to 'N' and grant privileges at more specific levels only. You can grant privileges for particular databases, tables, columns, or routines.    >>>>mysql.user 表中保存的是用户信息及用户的全局权限信息(该表中记录的权限是针对实例上所有数据库的)。例如用户对应 mysql.user表中Delete_priv列值为Y，那么使用这个用户你就可以删除实例上任何数据库中任何表的记录(当然有些系统表中的数据是任何用户都没有权限删除的)。所以给用户授予相关全局权限的时候得慎重。对于那些非管理用户，我们应该尽可能给他们赋予 数据库级别，甚至是表级别和列级别的权限，而不是给他们全局权限。

The db table grants database-specific privileges. Values in the scope columns of this table can take the following forms:    >>>>mysql.db 表中保存着用户拥有的数据库级别的权限

• A blank User value matches the anonymous user. A nonblank value matches literally; there are no wildcards in user names.    >>>>如果db表中的user 为空白，表示匹配匿名用户(能匹配任何用户名)。user 列非空的话只能进行精确匹配，user 不支持通配符匹配。

• The wildcard characters % and _ can be used in the Host and Db columns. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape 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和db的指定 都是支持使用%和_ 作为通配符使用的，所以如果你的数据库名中包含有_ 下滑线的话，在指定该数据库名时需要在下划线前使用斜杠进行转义，否则mysql会把下划线理解为通配符。(目前还不知道在赋予数据库级别权限时，数据库名怎么使用%作为通配符) 。下面是在给用户赋予db级别权限时候使用通配符的例子  grant select on test.* to 'test_chen'@'172.172.230_' identified by 'root';

• A '%' or blank Host value means “any host.”    >>>>db 表中host列值 为空 或者 %，表示任意地址

• A '%' or blank Db value means “any database.”    >>>>db 表中 db 列为空 或者 %，表示任意数据库(目前不知道怎样赋权)

The server reads the db table into memory and sorts it at the same time that it reads the user table. The server sorts the db table based on the Host, Db, and User scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching rows, it uses the first match that it finds.    >>>>同mysql.user表一样，在mysql实例启动时，db表也会被读到内存中，并且根据host,db,user三个字段进行排序(mysql启动时，会把所有的权限表都读入内存中，并进行排序，当有连接或者sql请求时，mysql

直接调用内存中权限表的数据进行Connection verification和Requst verification)。同mysql.user表的排序规则一样，db 表的排序结果也是具体的排在前面，模糊的排在后面。当调用时，按顺序进行匹配，取第一条匹配上的记录。

The tables_priv, columns_priv, and procs_priv tables grant table-specific, column-specific, and routine-specific privileges. Values in the scope columns of these tables can take the following forms:    >>>>tables_priv，columns_priv，procs_priv 权限表，分别用来保存用户拥有的表级别，列级别以及具体到某个procedure或者function的权限。

• The wildcard characters % and _ can be used in the Host column. These have the same meaning as for pattern-matching operations performed with the LIKE operator.    >>>> 在给用户赋予表级别，列级别，以及某个具体function(或者 procedure)的权限时，host列可以使用%和_通配符。如下是在给用户赋予表级别权限时，host值使用通配符的例子 grant select,insert on test1.test_priv to 'test_chen'@'172.172.230._' identified by 'root';  这时从ip地址为172.172.230.1-9(因为_代表一个任意字符)的服务器上都可以访问使用该用户访问数据库。

• A '%' or blank Host value means “any host.”    >>>>当 上面三张权限表的 host 列为空或者 %时，表示匹配任意地址

• The Db, Table_name, Column_name, and Routine_name columns cannot contain wildcards or be blank.     >>>>对用户赋予表级别，列级别以及具体function(或者procedure)权限时，数据库名，表名，列名，程序名不能使用通配符。

The server sorts the tables_priv, columns_priv, and procs_priv tables based on the Host, Db, and User columns. This is similar to db table sorting, but simpler because only the Host column can contain wildcards.    >>>>同之前说的 user和db表一样，tables_priv，columns_priv，procs_priv表也会在实例启动时被读入内存中，并在内存中根据host,db,user三个列进行排序(desc)。但是不同的是，这三个表中只有host列能包含通配符。其他的列都不支持通配符。

The server uses the sorted tables to verify each request that it receives. For requests that require administrative privileges such as SHUTDOWN or RELOAD, the server checks only the user table row because that is the only table that specifies administrative privileges. The server grants access if the row permits the requested operation and denies access otherwise. For example, if you want to execute mysqladmin shutdown but your user table row does not grant the SHUTDOWN privilege to you, the server denies access without even checking the db table. (It contains no Shutdown_priv column, so there is no need to do so.)    >>>>mysql 通过内存中保存的这些权限表的数据，完成对用户请求的验证。如果用户要执行的是如shutdown或者reload等管理命令，那么mysql只需要检查内存中的user表，查看用户是否有对应的权限(因为这类管理权限只保存在user表中)。例如用户执行 mysqladmin shutdown命令，但是user表中该用户没有shutdown权限，那么mysql就会返回用户没有权限执行该命令，而不会去检查mysql.db 等其他权限表(因为其他权限表中根本不会有shutdown 权限)

For database-related requests (INSERT, UPDATE, and so on), the server first checks the user's global privileges by looking in the user table row. If the row permits the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db table:    >>>>对于 insert，update之类的操作请求，mysql首先会检查内存中排序好的 user表，查看当前用户是否具有该全局权限(mysql.user表中保存的都是全局权限)，如果有，那么命令可以执行。如果没有，那么mysql会继续检查内存中排序好的mysql.db表，查看用户是否有权限进行操作。

The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user's host name and MySQL user name. 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.    >>>>通过当前用户的user和host以及需要访问的database 来匹配db表中的记录，查看用户对某个db的权限

After determining the database-specific privileges granted by the db table rows, 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_priv and columns_priv tables, adds those to the user's privileges, and permits or denies access based on the result. For stored-routine operations, the server uses the procs_priv table rather than tables_priv and columns_priv.    >>>>当mysql检匹配到用户db级别的权限后，会把它同前面从user表中检查到的全局权限进行整合(简单的说就是把全局权限和db级别的权限加在一起，不论此时权限是否满足当前操作需要)，如果这时权限满足当前操作的需要，操作被执行，否则mysql 会继续检查 内存中的 tables_priv和 columns_priv权限表，并且把匹配到的权限加到之前整合的全局权限和 db级别权限一起，此时判断如果能够满足操作权限需要，操作被执行，否则给用户返回没有权限的提示。(对于程序操作的话，那么相应的这里应该匹配的就是proce_priv表而不是tables_priv和columns_priv表)

Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:

global privilegesOR (database privileges AND host privileges)OR table privilegesOR column privilegesOR routine privileges

It may not be apparent why, if the global user row privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database, table, and column privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT INTO ... SELECT statement, you need both the INSERT and theSELECT privileges. Your privileges might be such that the user table row grants one privilege and the db table row grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the rows in both tables must be combined.    >>>>前面我们提到，mysql 匹配 db级别的权限后会把db级别的权限同之前匹配user表得到的全局权限做整合(如果还需要匹配 tables_priv和columns_priv表，那么匹配到的表级别和列级别的权限也会同前面的权限进行整合)，这是为什么呢？这是因为我们的请求可能需要的是多个不同的权限，例如 执行 insert into ... select 语句，我们需要insert 和 select 两种权限。也许在 user 表中有你需要的 select 权限，而insert 权限确保存在 db 表或者 tables_priv表中。所以我们需要把匹配到的所有级别的权限都整合到一起。(注意如果mysql 检查mysql.user表后发现用户有权限执行请求时，就不会再继续去匹配db等其他权限表，只有权限前面权限检查不满足时，才会一级级的去检查各个权限表)。