(mysql.info) request-access

 
 5.8.6 Access Control, Stage 2: Request Verification
 ---------------------------------------------------
 
 After you establish a connection, the server enters Stage 2 of access
 control. For each request that you issue via 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  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 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! 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 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:
 
    * 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 the underscore 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 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 `proc_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 the
      `Host' column. These have the same meaning as for pattern-matching
      operations performed with the `LIKE' operator.
 
    * A `'%'' or blank `Host' value means `any host.'
 
    * The `Db', `Table_name', and `Column_name' columns 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 allows the requested
 operation and denies access 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. 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. 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 changes to the grant tables (for
 example, by using `SHOW GRANTS') to make sure that your access
 privileges are actually set up the way you think they are.
Info Catalog
(mysql.info) connection-access
(mysql.info) privilege-system
(mysql.info) privilege-changes
automatically generated byinfo2html