Access Control, Stage 2: Request Verification
After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user
, db
, host
, tables_priv
, columns_priv
, or procs_priv
tables. (You may find it helpful to refer to , "Privilege System Grant Tables", which lists the columns present in each of the grant tables.)
The user
table grants privileges that are assigned to you on a global basis and that apply 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, you should leave all privileges in the user
table set to 'N'
and grant privileges at more specific levels only. You can grant privileges for particular databases, tables, columns, or routines.
The db
and host
tables grant database-specific privileges. Values in the scope columns of these tables can take the following forms:
- A blank
User
value in thedb
table matches the anonymous user. A nonblank value matches literally; there are no wildcards in user names. - The wildcard characters "
%
" and "_
" can be used in theHost
andDb
columns of either table. These have the same meaning as for pattern-matching operations performed with theLIKE
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 theGRANT
statement. - A
'%'
Host
value in thedb
table means "any host." A blankHost
value in thedb
table means "consult thehost
table for further information" (a process that is described later in this section). - A
'%'
or blankHost
value in thehost
table means "any host." - A
'%'
or blankDb
value in either table means "any database."
The server reads the db
and host
tables into memory and sorts them 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, and sorts the host
table based on the Host
and Db
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 entries, 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 theHost
column. These have the same meaning as for pattern-matching operations performed with theLIKE
operator. - A
'%'
or blankHost
value means "any host." - The
Db
,Table_name
,Column_name
, andRoutine_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
or host
tables. (They contain no Shutdown_priv
column, so there is no need to do so.)
For database-related requests (INSERT
, UPDATE
, and so on), the server first checks the user's global privileges by looking 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 by checking the db
and host
tables:
- The server looks in the
db
table for a match on theHost
,Db
, andUser
columns. TheHost
andUser
columns are matched to the connecting user's host name and MariaDB user name. TheDb
column is matched to the database that the user wants to access. If there is no row for theHost
andUser
, access is denied. - If there is a matching
db
table row and itsHost
column is not blank, that row defines the user's database-specific privileges. - If the matching
db
table row'sHost
column is blank, it signifies that thehost
table enumerates which hosts should be permitted access to the database. In this case, a further lookup is done in thehost
table to find a match on theHost
andDb
columns. If nohost
table row matches, access is denied. If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in thedb
andhost
table entries; that is, the privileges that are'Y'
in both entries. (This way you can grant general privileges in thedb
table row and then selectively restrict them on a host-by-host basis using thehost
table entries.)
After determining the database-specific privileges granted by the db
and host
table entries, 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 AND host privileges) OR table privileges OR column privileges OR routine privileges
It may not be apparent why, if the global user
row 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 and the db
table row grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the entries in both tables must be combined.
The host
table is not affected by the GRANT
or REVOKE
statements, so it is unused in most MariaDB installations. If you modify it directly, you can use it for some specialized purposes, such as to maintain a list of secure servers on the local network that are granted all privileges.
You can also use the host
table to indicate hosts that are not secure. Suppose that you have a machine public.your.domain
that is located in a public area that you do not consider secure. You can enable access to all hosts on your network except that machine by using host
table entries like this: