(mysql.info.gz) Request access
Info Catalog
(mysql.info.gz) Connection access
(mysql.info.gz) Privilege system
(mysql.info.gz) Privilege changes
5.5.6 Access Control, Stage 2: Request Verification
---------------------------------------------------
Once you establish a connection, the server enters Stage 2 of access
control. For each request that comes in on the 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', or `columns_priv'
tables. (You may find it helpful to refer to Privileges, 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 current 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 the
privileges in the `user' table set to `'N'' and grant privileges at more
specific levels only. You can grant privileges for particular databases,
tables, or columns.
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 `_' 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 in and sorts the `db' and `host' tables 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' and `columns_priv' tables grant table-specific and
column-specific privileges. Values in the scope columns of these
tables can take the following form:
* The wildcard characters `%' and `_' can be used in the `Host'
column of either table. These have the same meaning as for
pattern-matching operations performed with the `LIKE' operator.
* A `'%'' or blank `Host' value in either table means "any host."
* The `Db', `Table_name', and `Column_name' columns cannot contain
wildcards or be blank in either table.
The server sorts the `tables_priv' and `columns_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 request verification process is described here. (If you are
familiar with the access-checking source code, you will notice that the
description here differs slightly from the algorithm used in the code.
The description is equivalent to what the code actually does; it
differs only to make the explanation simpler.)
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. Access is
granted if the row allows the requested operation and denied 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:
1. 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.
2. If there is a matching `db' table row and its `Host' column is not
blank, that row defines the user's database-specific privileges.
3. 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.
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
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 entries in the grant tables (for
example, by using `SHOW GRANTS' or `mysqlaccess') to make sure that
your access privileges are actually set up the way you think they are.
Info Catalog
(mysql.info.gz) Connection access
(mysql.info.gz) Privilege system
(mysql.info.gz) Privilege changes
automatically generated byinfo2html