6.2.2 Privilege System Grant Tables

The 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
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”. The discussion here
describes the underlying structure of the grant tables and how the
server uses their contents when interacting with clients.

Note

Direct modification of grant tables using statements such as
INSERT,
UPDATE, or
DELETE is discouraged. The server
is free to ignore rows that become malformed as a result of such
modifications.

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
'thomas.loc.gov' and
'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 Host,
User, and Db column
values of 'thomas.loc.gov',
'bob' and 'reports'
applies 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 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.5, “Access Control, Stage 2: Request Verification”, describes the rules for
this.

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.

Caution

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 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 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 6.2.5, “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 (procedures and functions). A privilege granted at
the routine level applies only to a single procedure or
function.

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, db, and
host 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 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 set to the current timestamp and the
CURRENT_USER value, respectively,
but are otherwise unused.

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

60

User

16

Password

41

Db

64

Table_name

64

Column_name

64

Routine_name

64

For access-checking purposes, comparisons of
User, Password,
Db, and Table_name values
are case sensitive. Comparisons of Host,
Column_name, and
Routine_name values are not case sensitive.

The user, db, and
host 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.

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.

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: