GNU Info

(mysql.info)Request access

---

Next: Access denied Prev: Connection access Up: Privilege system

---

Enter node , (file) or (file)node

Access Control, Stage 2: Request Verification
---------------------------------------------

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 `user', `db', `host', `tables_priv', or `columns_priv' tables.  The
grant tables are manipulated with `GRANT' and `REVOKE' commands.  Note:
`GRANT'.  (You may find it helpful to refer to Note:
Privileges, which lists the fields 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 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 in the `user' table set to `'N'' and grant privileges on a
database-specific basis only, using the `db' and `host' tables.

The `db' and `host' tables grant database-specific privileges.  Values
in the scope fields may be specified as follows:

   * The wild-card characters `%' and `_' can be used in the `Host' and
     `Db' fields of either table.

   * 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 `'%'' 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 `db' and `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 `Host', `Db', and `User' scope fields, and the
`host' table is sorted on the `Host' and `Db' 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.

The `tables_priv' and `columns_priv' tables grant table- and
column-specific privileges.  Values in the scope fields may be
specified as follows:

   * The wild-card characters `%' and `_' can be used in the `Host'
     field of either table.

   * A `'%'' or blank `Host' value in either table means "any host."

   * The `Db', `Table_name' and `Column_name' fields cannot contain
     wild cards or be blank in either table.

The `tables_priv' and `columns_priv' tables are sorted on the `Host',
`Db', and `User' fields.  This is similar to `db' table sorting,
although the sorting is simpler because only the `Host' field may
contain wild cards.

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 explanation simpler.)

For administrative requests (*shutdown*, *reload*, etc.), the server
checks only the `user' table entry, because 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 `db' or `host' tables.  (They contain no
`Shutdown_priv' column, so 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 the
`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 `db' and `host' tables:

  1. The server looks in the `db' table for a match on the `Host',
     `Db', and `User' fields.  The `Host' and `User' fields are matched
     to the connecting user's hostname and MySQL user name.  The `Db'
     field is matched to the database the user wants to access.  If
     there is no entry for the `Host' and `User', access is denied.

  2. If there is a matching `db' table entry and its `Host' field is
     not blank, that entry defines the user's database-specific
     privileges.

  3. 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' and `Db'
     fields.  If no `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 `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 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 `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 checks the user's
table and column privileges in the `tables_priv' and `columns_priv'
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:

     global privileges
     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 the entries in both tables must be combined.

The `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' 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, using `mysqlaccess') to make sure your access privileges are
actually set up the way you think they are.