(mysql.info) privileges
Info Catalog
(mysql.info) what-privileges
(mysql.info) privilege-system
(mysql.info) privileges-provided
5.8.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.example.com' need not be the same
person as the user `joe' who connects from `home.example.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.example.com', and a different set
of privileges for connections by `joe' from `home.example.com'.
MySQL access control involves two stages when you run a client program
that connects to the server:
* 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 determine 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 do not necessarily take effect
immediately for the next statement that 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 statements such as `GRANT' and `REVOKE' to set up accounts and
control the privileges available to each one. See
account-management-sql. 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 the `user' and
`db' tables are shown here. The `host' table is similar to the `db'
table but has a specialized use as described in request-access.
*Table Name* *user* *db*
*Scope columns* `Host' `Host'
`User' `Db'
`Password' `User'
*Privilege columns* `Select_priv' `Select_priv'
`Insert_priv' `Insert_priv'
`Update_priv' `Update_priv'
`Delete_priv' `Delete_priv'
`Index_priv' `Index_priv'
`Alter_priv' `Alter_priv'
`Create_priv' `Create_priv'
`Drop_priv' `Drop_priv'
`Grant_priv' `Grant_priv'
`Create_view_priv' `Create_view_priv'
`Show_view_priv' `Show_view_priv'
`Create_routine_priv' `Create_routine_priv'
`Alter_routine_priv' `Alter_routine_priv'
`Execute_priv' `Execute_priv'
`Create_tmp_table_priv'`Create_tmp_table_priv'
`Lock_tables_priv' `Lock_tables_priv'
`References_priv' `References_priv'
`Reload_priv'
`Shutdown_priv'
`Process_priv'
`File_priv'
`Show_db_priv'
`Super_priv'
`Repl_slave_priv'
`Repl_client_priv'
*Security columns* `ssl_type'
`ssl_cipher'
`x509_issuer'
`x509_subject'
*Resource control columns* `max_questions'
`max_updates'
`max_connections'
`max_user_connections'
`Execute_priv' was present in MySQL 5.0.0, but did not become
operational until MySQL 5.0.3.
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 has the following
columns:
*Table Name* *procs_priv*
*Scope `Host'
columns*
`Db'
`User'
`Routine_name'
`Routine_type'
*Privilege `Proc_priv'
columns*
*Other `Timestamp'
columns*
`Grantor'
The `procs_priv' table exists as of MySQL 5.0.3. The `Routine_type'
column was added in MySQL 5.0.6. It is an `ENUM' column with values of
`'FUNCTION'' or `'PROCEDURE'' to indicate the type of routine the row
refers to. This column allows privileges to be granted separately for a
function and a procedure with the same name.
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 row (entry) 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 stored 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.
request-access, describes the rules that are rules used to do
this.
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)'
For access-checking purposes, comparisons of `Host' values are
case-insensitive. `User', `Password', `Db', and `Table_name' values
are case sensitive. `Column_name' and `Routine_name' values are case
insensitive.
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 Name* *Column *Possible Set Elements*
Name*
`tables_priv' `Table_priv'`'Select', 'Insert', 'Update',
'Delete', 'Create', 'Drop', 'Grant',
'References', 'Index', 'Alter', 'Create
View', 'Show view''
`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 in the following manner:
* 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. Any privilege granted in this table
applies to _all_ databases on the server.
* Because any global privilege is considered a privilege for
all databases, any global privilege enables a user to see all
database names with `SHOW DATABASES' or by examining the
`SCHEMATA' table of `INFORMATION_SCHEMA'.
* 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 must be modified directly with statements
such as `INSERT', `UPDATE', and `DELETE'. It is not affected by
statements such as `GRANT' and `REVOKE' that modify the grant
tables indirectly. 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. The reason for this is that
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. (See show-grants.) 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:
SHOW GRANTS FOR 'bob'@'pc84.example.com';
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) what-privileges
(mysql.info) privilege-system
(mysql.info) privileges-provided
automatically generated byinfo2html