After the server accepts a connection, it enters Stage 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 the user
,
global_grants
, db
,
tables_priv
, columns_priv
,
or procs_priv
tables. (You may find it helpful
to refer to Section 8.2.3, “Grant Tables”, which lists the
columns present in each grant table.)
The user
and global_grants
tables grant global privileges. The rows in these tables for a
given account indicate the account privileges that apply on a
global basis 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, leave all privileges in the user
table
set to 'N'
and grant privileges at more
specific levels only (for particular databases, tables, columns,
or routines). It is also possible to grant database privileges
globally but use partial revokes to restrict them from being
exercised on specific databases (see
Section 8.2.12, “Privilege Restriction Using Partial Revokes”).
The db
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 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.
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:
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 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.
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
and global_privilege
tables because those are the only tables that specify
administrative privileges. The server grants access if a row for
the account in those tables 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. (The latter table contains no
Shutdown_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 the
user
table row (less any privilege restrictions
imposed by partial revokes). 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 from the
db
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 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
.
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 an
INSERT INTO ...
SELECT
statement, you need both the
INSERT
and the
SELECT
privileges. Your privileges
might be such that the user
table row grants
one privilege global and the db
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.