mysql system database includes several
grant tables that contain information about user accounts and the
privileges held by them.
Normally, to manipulate the contents of grant tables, you modify
them indirectly by using account-management statements such as
REVOKE to set up accounts and
control the privileges available to each one. See
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.
mysql database tables contain grant
Other tables in the
mysql database do not hold
grant information and are discussed elsewhere:
event: Information about Event Scheduler
events. See Using the Event Scheduler.
func: Information about user-defined
functions. See Adding New Functions to MySQL.
Used for logging. See MySQL Server Logs.
for server-side help. See
ndb_binlog_index: Used for MySQL Cluster
MySQL Cluster Replication Schema and Tables.
proc: Information about stored procedures
and functions. See Using Stored Routines (Procedures and Functions).
servers: Used by the
FEDERATED storage engine. See
Creating a FEDERATED Table Using CREATE SERVER.
Used for time zone information. See
MySQL Server Time Zone Support.
Each grant table contains scope columns and privilege columns:
Scope columns determine the scope of each row in the tables;
that is, the context in which the row applies. For example, a
user table row with
User values of
'bob' applies to authenticating connections
made to the server from the host
thomas.loc.gov by a client that specifies a
user name of
bob. Similarly, a
db table row with
bob connects from the host
thomas.loc.gov to access the
reports database. The
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 a table row grants; that is, which operations it permits to be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 4.5, “Access Control, Stage 2: Request Verification”, describes the rules for this.
The server uses the grant tables in the following manner:
user table scope columns determine
whether to reject or permit incoming connections. For
permitted connections, any privileges granted in the
user table indicate the user's global
privileges. Any privileges granted in this table apply to
all databases on the server.
db table scope columns determine which
users can access which databases from which hosts. The
privilege columns determine the permitted operations. A
privilege granted at the database level applies to the
database and to all objects in the database, such as tables
and stored programs.
host table is used in conjunction with
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 4.5, “Access Control, Stage 2: Request Verification”.
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.
procs_priv table applies to stored
routines (procedures and functions). A privilege granted at
the routine level applies only to a single procedure or
proxies_priv table indicates which
users can act as proxies for other users and whether a user
can grant the
to other users.
The server uses the
host tables in the
mysql database at both the first and second
stages of access control (see Chapter 4, The MySQL Access Privilege System).
The columns in the
db tables are shown here. The
host table is similar to the
db table but has a specialized use as described
in Section 4.5, “Access Control, Stage 2: Request Verification”.
Table 4.2 user and db Table Columns
|Resource control columns|
user table has a
Password column for storing credential
information. As of MySQL 5.5.7, the
authentication_string columns for storing
authentication plugin and credential information.
If an account row names a plugin in the
column, the server uses it to authenticate connection attempts for
the account. It is up to the plugin whether it uses the
authentication_string column values.
plugin column for an account row is
empty, the server authenticates the account using either the
mysql_old_password plugin, depending on whether
the password hash value in the
used native hashing or the older pre-4.1 hashing method. Clients
must match the password in the
of the account row.
Prior to MySQL 5.5.11, the length of the
column was 60 characters. This was increased to 64 characters in
MySQL 5.5.11 for compatibility with the
name column. (Bug #11766610, Bug #59752)
During the second stage of access control, the server performs
request verification to ensure that each client has sufficient
privileges for each request that it issues. In addition to the
host grant tables, the server may also consult
columns_priv tables for requests that involve
tables. The latter tables provide finer privilege control at the
table and column levels. They have the columns shown in the
Table 4.3 tables_priv and columns_priv Table Columns
columns are set to the current timestamp and the
CURRENT_USER value, respectively,
but are otherwise unused.
For verification of requests that involve stored routines, the
server may consult the
procs_priv table, which
has the columns shown in the following table.
Table 4.4 procs_priv Table Columns
Routine_type column is an
ENUM column with values of
indicate the type of routine the row refers to. This column
enables privileges to be granted separately for a function and a
procedure with the same name.
columns are unused.
proxies_priv table was added in MySQL 5.5.7
and records information about proxy accounts. It has these
For an account to be able to grant the
PROXY privilege to other accounts,
it must have a row in the
With_grant set to 1 and
Proxied_user set to indicate the account or
accounts for which the privilege can be granted. For example, the
'root'@'localhost' account created during MySQL
installation has a row in the
table that enables granting the
PROXY privilege for
''@'', that is, for all users and all hosts.
root to set up proxy users, as
well as to delegate to other accounts the authority to set up
proxy users. See Section 5.8, “Proxy Users”.
Scope columns in the grant tables contain strings. The default value for each is the empty string. The following table shows the number of characters permitted in each column.
Table 4.5 Grant Table Scope Column Lengths
|Column Name||Maximum Permitted Characters|
For access-checking purposes, comparisons of
Table_name values are case sensitive.
Routine_name values are not case sensitive.
host tables list each privilege in a separate
column that is declared as
'N'. In other words, each privilege can be disabled or
enabled, with the default being disabled.
tables declare the privilege columns as
SET columns. Values in these
columns can contain any combination of the privileges controlled
by the table. Only those privileges listed in the column value are
Table 4.6 Set-Type Privilege Column Values
|Table Name||Column Name||Possible Set Elements|
user table specifies administrative
privileges, such as
SHUTDOWN. 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. Consequently, the server need consult only the
user table to determine whether a user can
perform an administrative operation.
FILE privilege also is
specified only in the
user table. It is not an
administrative privilege as such, but a user's ability to read or
write files on the server host is independent of the database
The server reads the contents of the grant tables into memory when
it starts. You can tell it to reload the tables by issuing a
statement or executing a mysqladmin
flush-privileges or mysqladmin reload
command. Changes to the grant tables take effect as indicated in
Section 4.6, “When Privilege Changes Take Effect”.
When you modify an account, it is a good idea to verify that your
changes have the intended effect. To check the privileges for a
given account, use the
statement. For example, to determine the privileges that are
granted to an account with user name and host name values of
use this statement:
SHOW GRANTS FOR 'bob'@'pc84.example.com';