GNU Info
(mysql.info)Privileges
How the Privilege System Works
------------------------------
The MySQL privilege system ensures that all users may do exactly the
things that they are supposed to be allowed to do. When you connect to
a MySQL server, your identity is determined by *the host from which you
connect* and *the user name you specify*. The system grants privileges
according to your identity and *what you want to do*.
MySQL considers both your hostname and user name in identifying you
because there is little reason to assume that a given user name belongs
to the same person everywhere on the Internet. For example, the user
`bill' who connects from `whitehouse.gov' need not be the same person
as the user `bill' who connects from `microsoft.com'. MySQL handles
this by allowing you to distinguish users on different hosts that
happen to have the same name: you can grant `bill' one set of
privileges for connections from `whitehouse.gov', and a different set
of privileges for connections from `microsoft.com'.
MySQL access control involves two stages:
* Stage 1: The server checks whether or not you are even allowed to
connect.
* Stage 2: Assuming you can connect, the server checks each request
you issue to see whether or not you have sufficient privileges to
perform it. For example, if you try to select rows from a table
in a database or drop a table from the database, the server makes
sure you have the *select* privilege for the table or the *drop*
privilege for the database.
The server uses the `user', `db', and `host' tables in the `mysql'
database at both stages of access control. The fields in these grant
tables are shown below:
*Table name* `user' `db' `host'
*Scope `Host' `Host' `Host'
fields*
`User' `Db' `Db'
`Password' `User'
*Privilege `Select_priv' `Select_priv' `Select_priv'
fields*
`Insert_priv' `Insert_priv' `Insert_priv'
`Update_priv' `Update_priv' `Update_priv'
`Delete_priv' `Delete_priv' `Delete_priv'
`Index_priv' `Index_priv' `Index_priv'
`Alter_priv' `Alter_priv' `Alter_priv'
`Create_priv' `Create_priv' `Create_priv'
`Drop_priv' `Drop_priv' `Drop_priv'
`Grant_priv' `Grant_priv' `Grant_priv'
`References_priv'
`Reload_priv'
`Shutdown_priv'
`Process_priv'
`File_priv'
For the second stage of access control (request verification), the
server may, if the request involves tables, additionally consult the
`tables_priv' and `columns_priv' tables. The fields in these tables
are shown below:
*Table name* `tables_priv' `columns_priv'
*Scope `Host' `Host'
fields*
`Db' `Db'
`User' `User'
`Table_name' `Table_name'
`Column_name'
*Privilege `Table_priv' `Column_priv'
fields*
`Column_priv'
*Other `Timestamp' `Timestamp'
fields*
`Grantor'
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, that is,
the context in which the entry applies. For example, a `user' table
entry with `Host' and `User' values of `'thomas.loc.gov'' and `'bob''
would be used for authenticating connections made to the server by
`bob' from the host `thomas.loc.gov'. Similarly, a `db' table entry
with `Host', `User', and `Db' fields of `'thomas.loc.gov'', `'bob'' and
`'reports'' would be used when `bob' connects from the host
`thomas.loc.gov' to access the `reports' database. The `tables_priv'
and `columns_priv' tables contain scope fields indicating tables or
table/column combinations to which each entry applies.
For access-checking purposes, comparisons of `Host' values are case
insensitive. `User', `Password', `Db', and `Table_name' values are
case sensitive. `Column_name' values are case insensitive in MySQL
Version 3.22.12 or later.
Privilege fields indicate the privileges granted by a table entry, that
is, what operations can be performed. The server combines the
information in the various grant tables to form a complete description
of a user's privileges. The rules used to do this are described in
Note: Request access.
Scope fields are strings, declared as shown below; the default value for
each is the empty string:
*Field *Type*
name*
`Host' `CHAR(60)'
`User' `CHAR(16)'
`Password' `CHAR(16)'
`Db' `CHAR(64)' (`CHAR(60)' for the `tables_priv' and
`columns_priv' tables)
`Table_name'`CHAR(60)'
`Column_name'`CHAR(60)'
In the `user', `db' and `host' tables, all privilege fields are
declared as `ENUM('N','Y')' -- each can have a value of `'N'' or `'Y'',
and the default value is `'N''.
In the `tables_priv' and `columns_priv' tables, the privilege fields
are declared as `SET' fields:
*Table name* *Field name* *Possible set elements*
`tables_priv' `Table_priv' `'Select', 'Insert', 'Update', 'Delete',
'Create', 'Drop', 'Grant', 'References',
'Index', 'Alter''
`tables_priv' `Column_priv' `'Select', 'Insert', 'Update',
'References''
`columns_priv' `Column_priv' `'Select', 'Insert', 'Update',
'References''
Briefly, the server uses the grant tables like this:
* The `user' table scope fields determine whether to allow or reject
incoming connections. For allowed connections, any privileges
granted in the `user' table indicate the user's global (superuser)
privileges. These privileges apply to *all* databases on the
server.
* The `db' and `host' tables are used together:
- The `db' table scope fields determine which users can access
which databases from which hosts. The privilege fields
determine which operations are allowed.
- The `host' table is used as an extension of the `db' table
when you want a given `db' table entry to apply to several
hosts. For example, if you want a user to be able to use a
database from several hosts in your network, leave the `Host'
value empty in the user's `db' table entry, then populate the
`host' table with an entry for each of those hosts. This
mechanism is described more detail in Note: Request access.
* The `tables_priv' and `columns_priv' tables are similar to the
`db' table, but are more fine-grained: they apply at the table and
column levels rather than at the database level.
Note that administrative privileges (*reload*, *shutdown*, etc.) are
specified only in the `user' table. This is because administrative
operations are operations on the server itself and are not
database-specific, so there is no reason to list such privileges in the
other grant tables. In fact, only the `user' table need be consulted
to determine whether or not you can perform an administrative operation.
The *file* privilege is specified only in the `user' table, too. It is
not an administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you are
accessing.
The `mysqld' server reads the contents of the grant tables once, when it
starts up. Changes to the grant tables take effect as indicated in
Note: Privilege changes.
When you modify the contents of the grant tables, it is a good idea to
make sure that your changes set up privileges the way you want. For
help in diagnosing problems, see Note: Access denied. For advice on
security issues, Note: Security.
A useful diagnostic tool is the `mysqlaccess' script, which Yves
Carlier has provided for the MySQL distribution. Invoke `mysqlaccess'
with the `--help' option to find out how it works. Note that
`mysqlaccess' checks access using only the `user', `db' and `host'
tables. It does not check table- or column-level privileges.
automatically generated by info2www version 1.2.2.9