服务器接受一个连接后,它进入tage 2 of access control. For each request that you issue through the connection, the server determines what operation you want to perform, then checks whether your privileges are sufficient. This is where the privilege columns in the grant tables come into play. These privileges can come from any of theuser
,db
,tables_priv
,columns_priv
, orprocs_priv
tables. (You may find it helpful to refer toSection 6.2.3, “Grant Tables”, which lists the columns present in each grant table.)
Theuser
table grants global privileges. Theuser
table row for an account indicates the account privileges that apply on a global basis no matter what the default database is. For example, if theuser
table grants you theDELETE
privilege, you can delete rows from any table in any database on the server host. It is wise to grant privileges in theuser
table only to people who need them, such as database administrators. For other users, leave all privileges in theuser
table set to“N”
and grant privileges at more specific levels only (for particular databases, tables, columns, or routines).
Thedb
table grants database-specific privileges. Values in the scope columns of this table can take the following forms:
A blank
User
value matches the anonymous user. A nonblank value matches literally; there are no wildcards in user names.The wildcard characters
%
and_
can be used in theHost
andDb
columns. These have the same meaning as for pattern-matching operations performed with theLIKE
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 theGRANT
statement.A
'%'
or blankHost
value means“any host.”A
'%'
or blankDb
value means“any database.”
The server reads thedb
table into memory and sorts it at the same time that it reads theuser
table. The server sorts thedb
table based on theHost
,Db
, andUser
scope columns. As with theuser
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.
Thetables_priv
,columns_priv
, andprocs_priv
tables grant table-specific, column-specific, and routine-specific privileges. Values in the scope columns of these tables can take the following forms:
The wildcard characters
%
and_
can be used in theHost
column. These have the same meaning as for pattern-matching operations performed with theLIKE
operator.A
'%'
or blankHost
value means“any host.”The
Db
,Table_name
,Column_name
, andRoutine_name
columns cannot contain wildcards or be blank.
The server sorts thetables_priv
,columns_priv
, andprocs_priv
tables based on theHost
,Db
, andUser
columns. This is similar todb
table sorting, but simpler because only theHost
column can contain wildcards.
The server uses the sorted tables to verify each request that it receives. For requests that require administrative privileges such asSHUTDOWN
orRELOAD
, the server checks only theuser
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 executemysqladmin shutdownbut youruser
table row does not grant theSHUTDOWN
privilege to you, the server denies access without even checking thedb
table. (The latter table contains noShutdown_priv
column, so there is no need to check it.)
For database-related requests (INSERT
,UPDATE
, and so on), the server first checks the user's global privileges in theuser
table row. If the row permits the requested operation, access is granted. If the global privileges in theuser
table are insufficient, the server determines the user's database-specific privileges from thedb
table:
The server looks in the
db
table for a match on theHost
,Db
, andUser
columns.The
Host
andUser
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
andUser
, access is denied.
After determining the database-specific privileges granted by thedb
table rows, the server adds them to the global privileges granted by theuser
table. If the result permits the requested operation, access is granted. Otherwise, the server successively checks the user's table and column privileges in thetables_priv
andcolumns_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 theprocs_priv
table rather thantables_priv
andcolumns_priv
.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:
global privileges OR database privileges OR table privileges OR column privileges OR routine privileges
It may not be apparent why, if the global 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 anINSERT INTO ... SELECT
statement, you need both theINSERT
and theSELECT
privileges. Your privileges might be such that theuser
table row grants one privilege global and thedb
table row grants the other specifically for the relevant database. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either your global or database privileges alone. It must make an access-control decision based on the combined privileges.