(mysql.info.gz) Privileges
Info Catalog
(mysql.info.gz) What Privileges
(mysql.info.gz) Privilege system
(mysql.info.gz) Privileges provided
5.5.2 How the Privilege System Works
------------------------------------
The MySQL privilege system ensures that all users may perform only the
operations allowed to them. As a user, when you connect to a MySQL
server, your identity is determined by _the host from which you
connect_ and _the username you specify_. When you issue requests after
connecting, the system grants privileges according to your identity and
_what you want to do_.
MySQL considers both your hostname and username in identifying you
because there is little reason to assume that a given username belongs
to the same person everywhere on the Internet. For example, the user
`joe' who connects from `office.com' need not be the same person as the
user `joe' who connects from `elsewhere.com'. MySQL handles this by
allowing you to distinguish users on different hosts that happen to
have the same name: You can grant one set of privileges for
connections by `joe' from `office.com', and a different set of
privileges for connections by `joe' from `elsewhere.com'.
MySQL access control involves two stages:
* Stage 1: The server checks whether it should allow you to connect.
* Stage 2: Assuming that you can connect, the server checks each
statement you issue to see whether 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
verifies that you have the `SELECT' privilege for the table or the
`DROP' privilege for the database.
If your privileges are changed (either by yourself or someone else)
while you are connected, those changes will not necessarily take effect
immediately for the next statement you issue. See Privilege
changes for details.
The server stores privilege information in the grant tables of the
`mysql' database (that is, in the database named `mysql'). The MySQL
server reads the contents of these tables into memory when it starts
and re-reads them under the circumstances indicated in Privilege
changes. Access-control decisions are based on the in-memory copies
of the grant tables.
Normally, you manipulate the contents of the grant tables indirectly by
using the `GRANT' and `REVOKE' statements to set up accounts and control
the privileges available to each one. `GRANT' GRANT. The
discussion here describes the underlying structure of the grant tables
and how the server uses their contents when interacting with clients.
The server uses the `user', `db', and `host' tables in the `mysql'
database at both stages of access control. The columns in these grant
tables are shown here:
*Table Name* *user* *db* *host*
*Scope columns* `Host' `Host' `Host'
`User' `Db' `Db'
`Password' `User'
*Privilege `Select_priv' `Select_priv' `Select_priv'
columns*
`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'
`Create_view_priv' `Create_view_priv' `Create_view_priv'
`Show_view_priv' `Show_view_priv' `Show_view_priv'
`Create_routine_priv'`Create_routine_priv'
`Alter_routine_priv'`Alter_routine_priv'
`References_priv' `References_priv' `References_priv'
`Reload_priv'
`Shutdown_priv'
`Process_priv'
`File_priv'
`Show_db_priv'
`Super_priv'
`Create_tmp_table_priv'`Create_tmp_table_priv'`Create_tmp_table_priv'
`Lock_tables_priv' `Lock_tables_priv' `Lock_tables_priv'
`Execute_priv'
`Repl_slave_priv'
`Repl_client_priv'
*Security columns* `ssl_type'
`ssl_cipher'
`x509_issuer'
`x509_subject'
*Resource control `max_questions'
columns*
`max_updates'
`max_connections'
`max_user_connections'
The `ssl_type', `ssl_cipher', `x509_issuer', and `x509_subject' columns
were added in MySQL 4.0.0.
The `Create_tmp_table_priv', `Execute_priv', `Lock_tables_priv',
`Repl_client_priv', `Repl_slave_priv', `Show_db_priv', `Super_priv',
`max_questions', `max_updates', and `max_connections' columns were
added in MySQL 4.0.2. `Execute_priv' is not operational until MySQL
5.0.3, however.
The `Create_view_priv' and `Show_view_priv' columns were added in MySQL
5.0.1.
The `Create_routine_priv', `Alter_routine_priv', and
`max_user_connections' columns were added in MySQL 5.0.3.
During the second stage of access control, the server performs request
verification to make sure that each client has sufficient privileges for
each request that it issues. In addition to the `user', `db', and
`host' grant tables, the server may also consult the `tables_priv' and
`columns_priv' tables for requests that involve tables. The
`tables_priv' and `columns_priv' tables provide finer privilege control
at the table and column levels. They have the following columns:
*Table Name* *tables_priv* *columns_priv*
*Scope `Host' `Host'
columns*
`Db' `Db'
`User' `User'
`Table_name' `Table_name'
`Column_name'
*Privilege `Table_priv' `Column_priv'
columns*
`Column_priv'
*Other `Timestamp' `Timestamp'
columns*
`Grantor'
The `Timestamp' and `Grantor' columns currently are unused and are
discussed no further here.
For verification of requests that involve stored routines, the server
may consult the `procs_priv' table. This table exists as of MySQL 5.0.3
and has the following columns:
*Table Name* *procs_priv*
*Scope `Host'
columns*
`Db'
`User'
`Routine_name'
*Privilege `Proc_priv'
columns*
*Other `Timestamp'
columns*
`Grantor'
The `Timestamp' and `Grantor' columns currently are unused and are
discussed no further here.
Each grant table contains scope columns and privilege columns:
* Scope columns determine the scope of each entry (row) in the
tables; that is, the context in which the row applies. For
example, a `user' table row with `Host' and `User' values of
`'thomas.loc.gov'' and `'bob'' would be used for authenticating
connections made to the server from the host `thomas.loc.gov' by a
client that specifies a username of `bob'. Similarly, a `db'
table row with `Host', `User', and `Db' column values 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 columns indicating tables or table/column
combinations to which each row applies. The `procs_priv' scope
columns indicate the store routine to which each row applies.
* Privilege columns indicate which privileges are granted by a table
row; 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 Request access.
Scope columns contain strings. They are declared as shown here; the
default value for each is the empty string:
*Column Name* *Type*
`Host' `CHAR(60)'
`User' `CHAR(16)'
`Password' `CHAR(16)'
`Db' `CHAR(64)'
`Table_name' `CHAR(64)'
`Column_name' `CHAR(64)'
`Routine_name' `CHAR(64)'
Before MySQL 3.23, the `Db' column is `CHAR(32)' in some tables and
`CHAR(60)' in others.
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
3.22.12 or later.
In the `user', `db', and `host' tables, each privilege is listed in a
separate column that is declared as `ENUM('N','Y') DEFAULT 'N''. In
other words, each privilege can be disabled or enabled, with the
default being disabled.
In the `tables_priv', `columns_priv', and `procs_priv' tables, the
privilege columns are declared as `SET' columns. Values in these
columns can contain any combination of the privileges controlled by the
table:
*Table *Column *Possible Set Elements*
Name* Name*
`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''
`procs_priv'`Proc_priv' `'Execute', 'Alter Routine', 'Grant''
Briefly, the server uses the grant tables as follows:
* The `user' table scope columns determine whether to reject or allow
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' table scope columns determine which users can access which
databases from which hosts. The privilege columns determine which
operations are allowed. A privilege granted at the database level
applies to the database and to all its tables.
* The `host' table is used in conjunction with the `db' table when
you want a given `db' table row 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 row, then populate the `host' table with a row
for each of those hosts. This mechanism is described more detail
in Request access.
* The `host' table is not affected by the `GRANT' and
`REVOKE' statements. Most MySQL installations need not use this
table at all.
* 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. A privilege
granted at the table level applies to the table and to all its
columns. A privilege granted at the column level applies only to
a specific column.
* The `procs_priv' table applies to stored routines. A privilege
granted at the routine level applies only to a single routine.
Administrative privileges (such as `RELOAD' or `SHUTDOWN') 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 these privileges in the
other grant tables. In fact, to determine whether you can perform an
administrative operation, the server need consult only the `user' table.
The `FILE' privilege also is specified only in the `user' table. 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 into memory
when it starts. You can tell it to re-read the tables by issuing a
`FLUSH PRIVILEGES' statement or executing a `mysqladmin
flush-privileges' or `mysqladmin reload' command. Changes to the grant
tables take effect as indicated in 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. To check
the privileges for a given account, use the `SHOW GRANTS' statement.
For example, to determine the privileges that are granted to an account
with `Host' and `User' values of `pc84.example.com' and `bob', issue
this statement:
mysql> SHOW GRANTS FOR 'bob'@'pc84.example.com';
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, column, or routine privileges
specified in the `tables_priv', `columns_priv', or `procs_priv' tables.
DONTPRINTYET For additional help in diagnosing privilege-related problems, see
Access denied. For general advice on security issues, see *Note
DONTPRINTYET For additional help in diagnosing privilege-related problems, see
Access denied. For general advice on security issues, see
Security.
Info Catalog
(mysql.info.gz) What Privileges
(mysql.info.gz) Privilege system
(mysql.info.gz) Privileges provided
automatically generated byinfo2html