MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
After the server accepts a connection, it enters Stage 2 of access
control. For each request that you issue through the connection,
the server determines what operation you want to perform, then
checks whether your privileges are sufficient. This is where the
privilege columns in the grant tables come into play. These
privileges can come from any of the user,
db, tables_priv,
columns_priv, or procs_priv
tables. (You may find it helpful to refer to
Section 6.2.3, “Grant Tables”, which lists the columns present in
each grant table.)
The user table grants global privileges. The
user table row for an account indicates the
account privileges that apply on a global basis no matter what the
default database is. For example, if the user
table grants you the DELETE
privilege, you can delete rows from any table in any database on
the server host. It is wise to grant privileges in the
user table only to people who need them, such
as database administrators. For other users, leave all privileges
in the user table set to 'N'
and grant privileges at more specific levels only (for particular
databases, tables, columns, or routines).
The db table grants database-specific
privileges. Values in the scope columns of this table can take the
following forms:
A blank User value matches the anonymous
user. A nonblank value matches literally; there are no
wildcards in user names.
The wildcard characters % and
_ can be used in the
Host and Db columns.
These have the same meaning as for pattern-matching operations
performed with the LIKE operator.
If you want to use either character literally when granting
privileges, you must escape it with a backslash. For example,
to include the underscore character (_) as
part of a database name, specify it as \_
in the GRANT statement.
A '%' or blank Host
value means “any host.”
A '%' or blank Db value
means “any database.”
The server reads the db table into memory and
sorts it at the same time that it reads the
user table. The server sorts the
db table based on the Host,
Db, and User scope columns.
As with the user table, sorting puts the
most-specific values first and least-specific values last, and
when the server looks for matching rows, it uses the first match
that it finds.
The tables_priv,
columns_priv, and procs_priv
tables grant table-specific, column-specific, and routine-specific
privileges. Values in the scope columns of these tables can take
the following forms:
The wildcard characters % and
_ can be used in the
Host column. These have the same meaning as
for pattern-matching operations performed with the
LIKE operator.
A '%' or blank Host
value means “any host.”
The Db, Table_name,
Column_name, and
Routine_name columns cannot contain
wildcards or be blank.
The server sorts the tables_priv,
columns_priv, and procs_priv
tables based on the Host,
Db, and User columns. This
is similar to db table sorting, but simpler
because only the Host column can contain
wildcards.
The server uses the sorted tables to verify each request that it
receives. For requests that require administrative privileges such
as SHUTDOWN or
RELOAD, the server checks only the
user table row because that is the only table
that specifies administrative privileges. The server grants access
if the row permits the requested operation and denies access
otherwise. For example, if you want to execute mysqladmin
shutdown but your user table row does
not grant the SHUTDOWN privilege to
you, the server denies access without even checking the
db table. (The latter table contains no
Shutdown_priv column, so there is no need to
check it.)
For database-related requests
(INSERT,
UPDATE, and so on), the server
first checks the user's global privileges in the
user table row. If the row permits the
requested operation, access is granted. If the global privileges
in the user table are insufficient, the server
determines the user's database-specific privileges from the
db table:
The server looks in the db table for a
match on the Host, Db,
and User columns.
The Host and User
columns are matched to the connecting user's host name and
MySQL user name.
The Db column is matched to the database
that the user wants to access.
If there is no row for the Host and
User, access is denied.
After determining the database-specific privileges granted by the
db table rows, the server adds them to the
global privileges granted by the user table. If
the result permits the requested operation, access is granted.
Otherwise, the server successively checks the user's table and
column privileges in the tables_priv and
columns_priv tables, adds those to the user's
privileges, and permits or denies access based on the result. For
stored-routine operations, the server uses the
procs_priv table rather than
tables_priv and
columns_priv.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:
global privileges OR database privileges OR table privileges OR column privileges OR routine privileges
It may not be apparent why, if the global privileges are initially
found to be insufficient for the requested operation, the server
adds those privileges to the database, table, and column
privileges later. The reason is that a request might require more
than one type of privilege. For example, if you execute an
INSERT INTO ...
SELECT statement, you need both the
INSERT and the
SELECT privileges. Your privileges
might be such that the user table row grants
one privilege global and the db table row
grants the other specifically for the relevant database. In this
case, you have the necessary privileges to perform the request,
but the server cannot tell that from either your global or
database privileges alone. It must make an access-control decision
based on the combined privileges.