Once you establish a connection, the server enters stage 2. For each request
that comes in on the connection, the server checks whether you have
sufficient privileges to perform it, based on the type of operation you wish
to perform. This is where the privilege fields in the grant tables come into
play. These privileges can come from any of the
columns_priv tables. The grant
tables are manipulated with
GRANT. (You may find it helpful to refer to
6.6 How the privilege system works, which lists the fields present in each of the grant
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
user table grants you the delete privilege, you can
delete rows from 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 server or
database administrators. For other users, you should leave the privileges
user table set to
'N' and grant privileges on a
database-specific basis only, using the
host tables grant database-specific privileges.
Values in the scope fields may be specified as follows:
wildcard characters `%' and `_' can be used in the
Db fields of either table.
Host value in the
db table means ``any host.'' A
Host value in the
db table means ``consult the
host table for further information.''
'%' or blank
Host value in the
host table means ``any
'%' or blank
Db value in either table means ``any database.''
User value in either table matches the anonymous user.
host tables are read in and sorted when the server
starts up (at the same time that it reads the
user table). The
db table is sorted on the
fields, and the
host table is sorted on the
scope fields. 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.
columns_priv tables grant table- and
column-specific privileges. Values in the scope fields may be specified as
The wildcard characters `%' and `_'
can be used in the
Host field of either table.
'%' or blank
Host value in either table means ``any host.''
Column_name fields cannot contain
wildcards or be blank in either table.
columns_priv tables are sorted on
User fields. This is similar to
db table sorting, although since only the
Host field may
contain wildcards, the sorting is simpler.
The request verification process is described below. (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
For administrative requests (shutdown, reload, etc.), the
server checks only the
user table entry, since that is the only table
that specifies administrative privileges. Access is granted if the entry
allows the requested operation and denied otherwise. For example, if you
want to execute
mysqladmin shutdown but your
user table entry
doesn't grant the shutdown privilege to you, access is denied
without even checking the
host tables. (Since they
Shutdown_priv column, there is no need to do so.)
For database-related requests (insert, update, etc.), the
server first checks the user's global (superuser) privileges by looking in
user table entry. If the entry 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
The server looks in the
db table for a match on the
User are matched to
the connecting user's hostname and MySQL user name. The
field is matched to the database the user wants to access. If there is no
entry for the
User, access is denied.
If there is a matching
db table entry and its
Host field is
not blank, that entry defines the user's database-specific privileges.
If the matching
db table entry's
Host field 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 table entry 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
host table entries, i.e., the privileges that are
'Y' in both
entries. (This way you can grant general privileges in the
entry 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
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 checks the user's
table and column privileges in the
tables and adds those to the user's privileges. Access is allowed or denied
based on the result.
Expressed in boolean terms, the preceding description of how a user's
privileges are calculated may be summarized like this:
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
It may not be apparent why, if the global
user entry privileges are
initially found to be insufficient for the requested operation, the server
adds those privileges to the database-, table- and column-specific privileges
later. The reason is that a request might require more than one type of
privilege. For example, if you execute an
INSERT ... SELECT
statement, you need both insert and select privileges.
Your privileges might be such that the
user table entry grants one
privilege and the
db table entry 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 both entries
must be combined.
host table can be used to maintain a list of ``secure'' servers.
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 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 | 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 (e.g.,
mysqlaccess) to make sure your access privileges are actually
set up the way you think they are.