Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
EPUB - 264.7Kb
HTML Download (TGZ) - 228.7Kb
HTML Download (Zip) - 238.2Kb


Security in MySQL  /  The MySQL Access Privilege System  /  Access Control, Stage 2: Request Verification

4.5 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, tables_priv, columns_priv, or procs_priv tables. (You may find it helpful to refer to Section 4.2, “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 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. (It contains 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 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 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 rows in both tables must be combined.


User Comments
  Posted by Wagner Bianchi on June 9, 2016
Below, that could be a good query to get a map out the users you have on your MySQL instance and relate that with the privilege's levels you're seeing on the above text:

mysql> mysql> SELECT
A.User Username
, CONCAT(A.User,'@',A.Host) USER
, IF(A.super_priv='Y','Yes','No') `Super Privs`
, IF(B.Db IS NULL,CONCAT(D.Dql.columns_priv'),CONCAT(B.Db,' mysql.db')) Db,C.Table_name,D.Column_name,E.Routine_name,E.Routine_type FROM mysql.user A LEFT JOIN mysql.db B ON (A.User=B.User AND A.Host=B.Host) LEFT JOIN mysql.tables_priv C ON (A.User=C.User AND A.Host=C.Host) LEFT JOIN mysql.columns_priv D ON (A.User=D.User AND A.Host=D.Host) LEFT JOIN mysql.procs_priv E ON (A.User=E.User AND A.Host=E.Host) ORDER BY A.User;
+----------+-------------------+-------------+----------------------+------------+-------------+--------------+--------------+
| Username | User | Super Privs | Db | Table_name | Column_name | Routine_name | Routine_type |
+----------+-------------------+-------------+----------------------+------------+-------------+--------------+--------------+
| dil | dil@localhost | No | NULL | NULL | NULL | NULL | NULL |
| dimo | dimo@localhost | No | NULL | NULL | NULL | NULL | NULL |
| din | din@localhost | No | a mysql.columns_priv | obj001 | i | NULL | NULL |
| dom | dom@localhost | No | a mysql.db | obj001 | NULL | obj001 | PROCEDURE |
| fio | fio@localhost | No | test mysql.db | NULL | NULL | NULL | NULL |
| jam | jam@localhost | No | NULL | NULL | NULL | NULL | NULL |
| jim | jim@localhost | No | NULL | NULL | NULL | NULL | NULL |
| jom | jom@localhost | No | NULL | NULL | NULL | NULL | NULL |
| repl | repl@192.168.50.% | Yes | NULL | NULL | NULL | NULL | NULL |
| rie | rie@localhost | No | a mysql.db | NULL | NULL | obj001 | PROCEDURE |
| root | root@127.0.0.1 | Yes | NULL | NULL | NULL | NULL | NULL |
| root | root@localhost | Yes | NULL | NULL | NULL | NULL | NULL |
| root | root@::1 | Yes | NULL | NULL | NULL | NULL | NULL |
| root | root@box01 | Yes | NULL | NULL | NULL | NULL | NULL |
| test | test@localhost | No | wb mysql.db | NULL | NULL | NULL | NULL |
| tim | tim@localhost | No | NULL | NULL | NULL | NULL | NULL |
| yum | yum@localhost | Yes | NULL | NULL | NULL | NULL | NULL |
+----------+-------------------+-------------+----------------------+------------+-------------+--------------+--------------+
17 rows in set (0.00 sec)
Sign Up Login You must be logged in to post a comment.