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,
host, tables_priv,
columns_priv, or procs_priv
tables. (You may find it helpful to refer to
Section 5.4.2, “Privilege System Grant Tables”, 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! 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.
The db and host tables grant
database-specific privileges. Values in the scope columns of these
tables can take the following forms:
A blank
Uservalue in thedbtable matches the anonymous user. A nonblank value matches literally; there are no wildcards in user names.The wildcard characters “
%” and “_” can be used in theHostandDbcolumns of either table. These have the same meaning as for pattern-matching operations performed with theLIKEoperator. 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 theGRANTstatement.A
'%'Hostvalue in thedbtable means “any host.” A blankHostvalue in thedbtable means “consult thehosttable for further information” (a process that is described later in this section).A
'%'or blankHostvalue in thehosttable means “any host.”A
'%'or blankDbvalue in either table means “any database.”
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 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 theHostcolumn. These have the same meaning as for pattern-matching operations performed with theLIKEoperator.A
'%'or blankHostvalue means “any host.”The
Db,Table_name,Column_name, andRoutine_namecolumns 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 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 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 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 and host tables:
The server looks in the
dbtable for a match on theHost,Db, andUsercolumns. TheHostandUsercolumns are matched to the connecting user's host name and MySQL user name. TheDbcolumn is matched to the database that the user wants to access. If there is no row for theHostandUser, access is denied.If there is a matching
dbtable row and itsHostcolumn is not blank, that row defines the user's database-specific privileges.If the matching
dbtable row'sHostcolumn is blank, it signifies that thehosttable enumerates which hosts should be permitted access to the database. In this case, a further lookup is done in thehosttable to find a match on theHostandDbcolumns. If nohosttable 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 thedbandhosttable entries; that is, the privileges that are'Y'in both entries. (This way you can grant general privileges in thedbtable row and then selectively restrict them on a host-by-host basis using thehosttable 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 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 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 on the local network that 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
enable 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') +--------------------+----+-