5.7.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 Section 5.7.7, “When Privilege Changes Take Effect”, 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
Section 5.7.7, “When Privilege Changes Take Effect”. 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
Section 13.5.1, “Account Management Statements”. 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 Section 5.7.6, “Access Control, Stage 2: Request Verification”.
The Event_priv and
Trigger_priv columns were added in MySQL
5.1.6.
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:
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:
The Routine_type column 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.
Section 5.7.6, “Access Control, Stage 2: Request Verification”, 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:
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:
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.
Note: 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
Section 5.7.6, “Access Control, Stage 2: Request Verification”.
Note: 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
Section 5.7.7, “When Privilege Changes Take Effect”.
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
Section 13.5.4.14, “SHOW GRANTS Syntax”.) 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';
For additional help in diagnosing privilege-related problems,
see Section 5.7.8, “Causes of Access denied Errors”. For general advice on
security issues, see Section 5.6, “General Security Issues”.
