5.7.6. 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 via 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,
host, tables_priv,
columns_priv, or
procs_priv tables. (You may find it helpful
to refer to Section 5.7.2, “How the Privilege System Works”, which lists the
columns present in each of the 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! In other words,
user table privileges are superuser
privileges. It is wise to grant privileges in the
user table only to superusers 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.
The db and host tables
grant database-specific privileges. Values in the scope columns
of these tables can take the following forms:
The wildcard characters ‘%’
and ‘_’ can be used in the
Host and Db columns of
either table. 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.
A '%' Host value in
the db table means “any
host.” A blank Host value in the
db table means “consult the
host table for further
information” (a process that is described later in
this section).
A '%' or blank Host
value in the host table means “any
host.”
A '%' or blank Db
value in either table means “any database.”
A blank User value in either table
matches the anonymous user.
The server reads the db and
host tables into memory and sorts them 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, 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 and least-specific values last, and
when the server looks for matching entries, it uses the first
match that it finds.
The tables_priv
columns_priv, and
proc_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 the
Host column. These have the same meaning
as for pattern-matching operations performed with the
LIKE operator.
A '%' or blank Host
value means “any host.”
The Db, Table_name,
and Column_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 table row because that is the only table
that specifies administrative privileges. The server grants
access if the row allows the requested operation and denies
access otherwise. For example, if you want to execute
mysqladmin shutdown but your
user table row doesn't grant the
SHUTDOWN privilege to you, the server denies
access without even checking the db or
host tables. (They contain no
Shutdown_priv column, so there is no need to
do so.)
For database-related requests (INSERT,
UPDATE, and so on), the server first checks
the user's global (superuser) privileges by looking in the
user table row. If the row allows 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 and host
tables:
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 hostname 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.
If there is a matching db table row and
its Host column is not blank, that row
defines the user's database-specific privileges.
If the matching db table row's
Host column is blank, it signifies that
the host table enumerates which hosts
should be allowed access to the database. In this case, a
further lookup is done in the host table
to find a match on the Host and
Db columns. If no host
table row matches, access is denied. If there is a match,
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 general privileges in the
db table row and then selectively
restrict them on a host-by-host basis using the
host table entries.)
After determining the database-specific privileges granted by
the db and host table
entries, the server adds them to the global privileges granted
by the user table. If the result allows 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 allows 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 AND host privileges)
OR table privileges
OR column privileges
OR 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 the SELECT
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 entries in both tables must be
combined.
The host table is not affected by the
GRANT or REVOKE
statements, so it is unused in most MySQL installations. If you
modify it directly, you can use it for some specialized
purposes, such as to maintain a list of secure servers. For
example, at TcX, the host table contains a
list of all machines on the local network. These are granted all
privileges.
You can also use the host table to indicate
hosts that are not secure. Suppose that you
have a machine public.your.domain that is
located in a public area that you do not consider secure. You
can allow access to all hosts on your network except that
machine by using host table entries like
this:
+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | % | ... (all privileges set to 'N')
| %.your.domain | % | ... (all privileges set to 'Y')
+--------------------+----+-
Naturally, you should always test your changes to the grant
tables (for example, by using SHOW GRANTS) to
make sure that your access privileges are actually set up the
way you think they are.
