Normally, you manipulate the contents of the grant tables in the
mysql database indirectly by using statements
REVOKE to set up accounts and
control the privileges available to each one. See
Section 13.7.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.
mysql database tables contain grant
user: Contains user accounts, global
privileges, and other non-privilege columns.
db: Contains database-level privileges.
host: Obsolete. New MySQL installations no
longer create this table as of MySQL 5.6.7.
tables_priv: Contains table-level
columns_priv: Contains column-level
procs_priv: Contains stored procedure and
proxies_priv: Contains proxy-user
Other tables in the
mysql database do not hold
grant information and are discussed elsewhere:
event: Contains information about Event
Scheduler events: See Section 20.4, “Using the Event Scheduler”.
func: Contains information about
user-defined functions: See
Section 24.3, “Adding New Functions to MySQL”.
tables are used for server-side help: See
Section 5.1.10, “Server-Side Help”.
plugin: Contains information about server
plugins: See Section 126.96.36.199, “Installing and Uninstalling Plugins”, and
Section 24.2, “The MySQL Plugin API”.
proc: Contains information about stored
procedures and functions: See
Section 20.2, “Using Stored Routines (Procedures and Functions)”.
servers: Used by the
FEDERATED storage engine: See
Section 188.8.131.52, “Creating a FEDERATED Table Using CREATE SERVER”.
These tables contain time zone information: See
Section 10.6, “MySQL Server Time Zone Support”.
_log in their name are used for
logging: See Section 5.2, “MySQL Server Logs”.
Modifications to tables in the
normally are made by the server in response to statements such
CREATE PROCEDURE. Direct
modification of these tables using statements such as
DELETE is not encouraged. The
server is free to ignore rows that become malformed as a result
of such modifications.
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
user table row with
User values of
'bob' would be used for 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
would be used when
bob connects from the
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 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 6.2.5, “Access Control, Stage 2: Request Verification”, describes the rules that are used to do 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 privilege granted in this table applies to
all databases on the server.
db table scope columns determine which
users can access which databases from which hosts. The
privilege columns determine which operations are permitted. A
privilege granted at the database level applies to the
database and to all objects in the database, such as tables
and stored programs.
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. A privilege granted at the routine level applies
only to a single routine.
proxies_priv table indicates which
users can act as proxies for other users and whether proxy
users can grant the
privilege to other users.
The server uses the
db tables in the
database at both the first and second stages of access control
(see Section 6.2, “The MySQL Access Privilege System”). The columns in the
db tables are shown
Table 6.3 user and db Table Columns
|Resource control columns|
authentication_string columns store
authentication plugin information.
plugin column for an account row is
empty, the server authenticates the account using the
mysql_old_password plugin implicitly, depending
on the format of the password hash in the
Password column. If the
Password value is empty or a 4.1 password hash
(41 characters), the server uses
mysql_native_password. If the password value is
a pre-4.1 password hash (16 characters), the server uses
mysql_old_password. (For additional information
about these hash formats, see Section 184.108.40.206, “Password Hashing in MySQL”.)
Clients must match the password in the
column of the account row.
If an account row names a plugin in the
column, the server uses it to authenticate connection attempts for
the account. Whether the plugin uses the value in the
Password column is up to the plugin.
password_expired column was added in MySQL
5.6.6 to permit DBAs to expire account passwords and require users
to reset their password. The default
password_expired value is
'N', but can be set to
ALTER USER statement.
After an account's password has been expired, all operations
performed by the account in subsequent connections to the server
result in an error until the user issues a
SET PASSWORD statement to establish
a new account password. See Section 220.127.116.11, “ALTER USER Syntax”.
It is possible after password expiration to “reset” a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password.
In MySQL 5.6.6,
ALTER USER also
Password column to the empty string,
so do not use this statement until 5.6.7.
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
db grant tables,
the server may also consult the
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 6.4 tables_priv and columns_priv Table Columns
columns are set to the current timestamp and the
CURRENT_USER value, respectively.
However, they are unused and are discussed no further here.
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 6.5 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 currently are unused and are discussed no further here.
proxies_priv table records information
about proxy users. It has these columns:
columns indicate the user account that has the
PROXY privilege for the proxied
Proxied_user: These columns indicate the
account of the proxied user.
Grantor: Currently unused.
Timestamp: Currently unused.
With_grant: This column indicates whether
the proxy account can grant the
PROXY privilege to other
Scope columns in the grant tables contain strings. They are declared as shown here; the default value for each is the empty string.
Table 6.6 Grant Table Scope Column Types
For access-checking purposes, comparisons of
Table_name values are case sensitive.
Routine_name values are not case sensitive.
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
tables, the privilege columns are declared 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 6.7 Set-Type Privilege Column Values
|Table Name||Column Name||Possible Set Elements|
Administrative privileges (such as
SHUTDOWN) are specified only in the
user table. 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, to determine whether you can perform an
administrative operation, the server need consult only 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
The mysqld 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 6.2.6, “When Privilege Changes Take Effect”.
When you modify an account's privileges, it is a good idea to
verify that the changes set up privileges the way you want. To
check the privileges for a given account, use the
SHOW GRANTS statement (see
Section 18.104.22.168, “SHOW GRANTS Syntax”). For example, to determine the
privileges that are granted to an account with user name and host
name values of
pc84.example.com, use this statement:
SHOW GRANTS FOR 'bob'@'pc84.example.com';