(mysql.info.gz) Request access

 
 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