Skip navigation links

Additional languages
5.4.3 Privileges Provided by MySQL »
Section Navigation      [Toggle]

5.4.2. How the Privilege System Works

The MySQL privilege system ensures that all users may perform only the operations allowed to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and username in identifying you because there is little reason to assume that a given username belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com.

MySQL access control involves two stages when you run a client program that connects to the server:

  • Stage 1: The server checks whether it should allow you to connect.

  • Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. See Section 5.4.6, “When Privilege Changes Take Effect”, for details.

The server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 5.4.6, “When Privilege Changes Take Effect”. Access-control decisions are based on the in-memory copies of the grant tables.

Normally, you manipulate the contents of the grant tables indirectly by using statements such as GRANT and REVOKE to set up accounts and control the privileges available to each one. See Section 12.5.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.

The server uses the user, db, and host tables in the mysql database at both stages of access control. The columns in the user and db tables are shown here. The host table is similar to the db table but has a specialized use as described in Section 5.4.5, “Access Control, Stage 2: Request Verification”.

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  
Security columns ssl_type  
  ssl_cipher  
  x509_issuer  
  x509_subject  
Resource control columns max_questions  
  max_updates  
  max_connections  
  max_user_connections  

The Event_priv and Trigger_priv columns were added in MySQL 5.1.6.

During the second stage of access control, the server performs request verification to make sure 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 tables_priv and columns_priv tables provide finer privilege control at the table and column levels. They have the following 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 currently are unused and are discussed no further here.

For verification of requests that involve stored routines, the server may consult the procs_priv table. This table has the following 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 allows privileges to be granted separately for a function and a procedure with the same name.

The Timestamp and Grantor columns currently are unused and are discussed no further here.

Each grant table contains scope columns and privilege columns:

  • Scope columns determine the scope of each row (entry) 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' would be used for authenticating connections made to the server from the host thomas.loc.gov by a client that specifies a username of bob. Similarly, a db table row with Host, User, and Db column values of 'thomas.loc.gov', 'bob' and 'reports' would be used 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 are granted by a table row; that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 5.4.5, “Access Control, Stage 2: Request Verification”, describes the rules that are used to do this.

Scope columns contain strings. They are declared as shown here; the default value for each is the empty string:

Column Name Type
Host CHAR(60)
User CHAR(16)
Password CHAR(16)
Db CHAR(64)
Table_name CHAR(64)
Column_name CHAR(64)
Routine_name CHAR(64)

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

In the user, db, and host tables, each privilege is listed 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.

In the tables_priv, columns_priv, and procs_priv tables, the privilege columns are declared as SET columns. Values in these columns can contain any combination of the privileges controlled by the table:

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'

Briefly, the server uses the grant tables in the following manner:

  • The user table scope columns determine whether to reject or allow incoming connections. For allowed connections, any privileges granted in the user table indicate the user's global (superuser) privileges. Any privilege granted in this table applies to all databases on the server.

    Note

    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 which operations are allowed. A privilege granted at the database level applies to the database and to all its tables.

  • 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 5.4.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. A privilege granted at the routine level applies only to a single routine.

Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. The reason for this is that 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. In fact, to determine whether you can perform an administrative operation, the server need consult only the user table.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld server reads the contents of the grant tables into memory when it starts. You can tell it to re-read 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 5.4.6, “When Privilege Changes Take Effect”.

When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want. To check the privileges for a given account, use the SHOW GRANTS statement. (See Section 12.5.5.22, “SHOW GRANTS Syntax”.) For example, to determine the privileges that are granted to an account with Host and User values of pc84.example.com and bob, issue this statement: 

SHOW GRANTS FOR 'bob'@'pc84.example.com';

For additional help in diagnosing privilege-related problems, see Section 5.4.7, “Causes of Access denied Errors”. For general advice on security issues, see Section 5.3, “General Security Issues”.


User Comments

Posted by Graham Wideman on July 6 2008 11:58pm[Delete] [Edit]

Mystery solved: tables_priv.Table_priv vs Column_priv

Normally, if you're just using GRANT to grant privileges, this is not something you need to know. But in case you're looking to the priv tables for definitive info on the full range of privileges, then you might be as puzzled as I was.

What does it mean for tables_priv to contain both a Table_priv field AND a Column_priv field? For example, how would Select/Insert/Update in each of these columns differ in effect?

Turns out that the Table_priv field controls row-wise privs as expected, while the Column_priv field controls no privs by itself. Instead, settings in the Column_priv field tell MySQL whether there are corresponding column-wise settings in the columns_priv table that should be looked for.

So,
GRANT SELECT (mycol, othercol) ON mydb.mytable TO fred
... causes MySQL to place two records in columns_priv (one for each col) , AND adds/modifies a single record in tables_priv to add "Select" to tables_priv.Column_priv.

Indeed, manually *removing* "Select" from tables_priv.Column_priv causes MySQL to no longer see any column-level select privs (at least, as reported by SHOW GRANTS).

Correspondingly, if you do decide to bypass the GRANT statement and instead set up privileges by directly accessing the priv tables, then for column level privs you have to remember to set corresponding privs in the tables_priv.Column_priv.

Note: If trying this out at home, be sure to perform the reload privs procedure each time after manually modifying the priv tables, in order to assess the effects properly. (phpmyadmin has a link to do this on various pages, or use FLUSH PRIVILEGES command in SQL.

Add your own comment.