MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The mysql
system database includes several
grant tables that contain information about user accounts and the
privileges held by them. This section describes those tables. For
information about other tables in the system database, see
Section 5.3, “The mysql System Database”.
The discussion here describes the underlying structure of the
grant tables and how the server uses their contents when
interacting with clients. However, normally you do not modify the
grant tables directly. Modifications occur indirectly when you use
account-management statements such as CREATE
USER
, GRANT
, and
REVOKE
to set up accounts and
control the privileges available to each one. See
Section 13.7.1, “Account Management Statements”. When you use such
statements to perform account manipulations, the server modifies
the grant tables on your behalf.
Direct modification of grant tables using statements such as
INSERT
,
UPDATE
, or
DELETE
is discouraged and done at
your own risk. The server is free to ignore rows that become
malformed as a result of such modifications.
As of MySQL 5.6.36, for any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. mysql_upgrade must be run to update the tables to the expected structure.
These mysql
database tables contain grant
information:
user
:
User accounts, global privileges, and other nonprivilege
columns.
db
:
Database-level privileges.
tables_priv
:
Table-level privileges.
columns_priv
:
Column-level privileges.
procs_priv
:
Stored procedure and function privileges.
proxies_priv
:
Proxy-user privileges.
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
Host
and User
values
of 'h1.example.net'
and
'bob'
applies to authenticating
connections made to the server from the host
h1.example.net
by a client that specifies
a user name of bob
. Similarly, a
db
table row with
Host
, User
, and
Db
column values of
'h1.example.net'
,
'bob'
and 'reports'
applies when bob
connects from the host
h1.example.net
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 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 6.2.6, “Access Control, Stage 2: Request Verification”, describes the rules for this.
In addition, a grant table may contain columns used for purposes other than scope or privilege assessment.
The server uses the grant tables in the following manner:
The 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.
Because a 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 INFORMATION_SCHEMA
SCHEMATA
table.
The 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.
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 (stored procedures and functions). A privilege
granted at the routine level applies only to a single
procedure or function.
The proxies_priv
table indicates which
users can act as proxies for other users and whether a user
can grant the PROXY
privilege
to other users.
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 FLUSH PRIVILEGES
statement or
executing a mysqladmin flush-privileges or
mysqladmin reload command. Changes to the
grant tables take effect as indicated in
Section 6.2.8, “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 SHOW
GRANTS
statement. For example, to determine the
privileges that are granted to an account with user name and
host name values of bob
and
pc84.example.com
, use this statement:
SHOW GRANTS FOR 'bob'@'pc84.example.com';
The server uses the user
and
db
tables in the mysql
database at both the first and second stages of access control
(see Section 6.2, “Access Control and Account Management”). The columns in the
user
and db
tables are
shown here.
Table 6.3 user and db Table Columns
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 |
|
Trigger_priv |
Trigger_priv |
|
Event_priv |
Event_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 |
||
Create_user_priv |
||
Create_tablespace_priv |
||
Security columns | ssl_type |
|
ssl_cipher |
||
x509_issuer |
||
x509_subject |
||
plugin |
||
authentication_string |
||
password_expired |
||
Resource control columns | max_questions |
|
max_updates |
||
max_connections |
||
max_user_connections |
The user
table plugin
,
Password
, and
authentication_string
columns store
authentication plugin and credential information.
If an account row names a plugin in the
plugin
column, the server uses it to
authenticate connection attempts for the account. It is up to
the plugin whether it uses the Password
and
authentication_string
column values.
If the plugin
column for an account row is
empty, the server authenticates the account using the
mysql_native_password
or
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 6.1.2.4, “Password Hashing in MySQL”.) Clients must match the
password in the Password
column of the
account row.
The password_expired
column permits DBAs to
expire account passwords and require users to reset their
password. The default password_expired
value
is 'N'
, but can be set to
'Y'
with the 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.
Although it is possible to “reset” an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password.
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
user
and db
grant tables,
the server may also consult the tables_priv
and 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 following table.
Table 6.4 tables_priv and columns_priv Table Columns
Table Name | tables_priv |
columns_priv |
---|---|---|
Scope columns | Host |
Host |
Db |
Db |
|
User |
User |
|
Table_name |
Table_name |
|
Column_name |
||
Privilege columns | Table_priv |
Column_priv |
Column_priv |
||
Other columns | Timestamp |
Timestamp |
Grantor |
The Timestamp
and Grantor
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 6.5 procs_priv Table Columns
Table Name | procs_priv |
---|---|
Scope columns | Host |
Db |
|
User |
|
Routine_name |
|
Routine_type |
|
Privilege columns | Proc_priv |
Other columns | Timestamp |
Grantor |
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
enables privileges to be granted separately for a function and a
procedure with the same name.
The Timestamp
and Grantor
columns are unused.
The proxies_priv
table records information
about proxy accounts. It has these columns:
For an account to be able to grant the
PROXY
privilege to other
accounts, it must have a row in the
proxies_priv
table with
With_grant
set to 1 and
Proxied_host
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
proxies_priv
table that enables granting the
PROXY
privilege for
''@''
, that is, for all users and all hosts.
This enables root
to set up proxy users, as
well as to delegate to other accounts the authority to set up
proxy users. See Section 6.2.12, “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 6.6 Grant Table Scope Column Lengths
Column Name | Maximum Permitted Characters |
---|---|
Host , Proxied_host |
60 |
User , Proxied_user |
16 |
Password |
41 |
Db |
64 |
Table_name |
64 |
Column_name |
64 |
Routine_name |
64 |
Host
and Proxied_host
values are converted to lowercase before being stored in the
grant tables.
For access-checking purposes, comparisons of
User
, Proxied_user
,
Password
, Db
, and
Table_name
values are case-sensitive.
Comparisons of Host
,
Proxied_host
, Column_name
,
and Routine_name
values are not
case-sensitive.
The user
and db
tables
list each privilege 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.
The tables_priv
,
columns_priv
, and
procs_priv
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 enabled.
Table 6.7 Set-Type Privilege Column Values
Table Name | Column Name | Possible Set Elements |
---|---|---|
tables_priv |
Table_priv |
'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop',
'Grant', 'References', 'Index', 'Alter', 'Create View',
'Show view', 'Trigger' |
tables_priv |
Column_priv |
'Select', 'Insert', 'Update', 'References' |
columns_priv |
Column_priv |
'Select', 'Insert', 'Update', 'References' |
procs_priv |
Proc_priv |
'Execute', 'Alter Routine', 'Grant' |
Only the user
table specifies administrative
privileges, such as RELOAD
and
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.
The 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 being accessed.