Introduction to MySQL's Privilege System

The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as select, insert, update and delete. Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify . The system grants privileges according to your identity and what you want to do.

MySQL access control involves two stages:

The server checks whether or not you are even allowed to connect.
Assuming you can connect, the server checks each request you issue to see whether or not 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 makes sure you have the select privilege for the table or the drop privilege for the database.

The server uses the user, db , and host tables in the mysql database at both stages of access control. The user table contains security information that applies to the server as whole. The host table gives entire machines rights to the server. The db, table, and column tables control access to individual databases, tables, and columns.
The fields in these grant tables are shown below:


Table name	`user`	`db`	`host`
Scope fields	`Host`	`Host`	`Host`
	`User`	`Db`	`Db`
	`Password`	`User`
Privilege fields	`Select_priv`	`Select_priv`	`Select_priv`
	`Insert_priv`	`Insert_priv`	`Insert_priv`
	`Update_priv`	`Update_priv`	`Update_priv`
	`Delete_priv`	`Delete_priv`	`Delete_priv`
	`Index_priv`	`Index_priv`	`Index_priv`
	`Alter_priv`	`Alter_priv`	`Alter_priv`
	`Create_priv`	`Create_priv`	`Create_priv`
	`Drop_priv`	`Drop_priv`	`Drop_priv`
	`Grant_priv`	`Grant_priv`	`Grant_priv`
	`References_priv`
	`Reload_priv`
	`Shutdown_priv`
	`Process_priv`
	`File_priv`

Privilege fields indicate the privileges granted by a table entry, 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.

For each request that comes in on the connection, the server checks whether you have sufficient privileges to perform it, based on the type of operation you wish to perform. This is where the privilege fields in the grant tables come into play. These privileges can come from any of the user, db, host , tables_priv, or columns_priv tables. The grant tables are manipulated with GRANT and REVOKE commands.

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the current database is. For example, if the user table grants you the delete privilege, you can delete rows from any database on the server host! In other words, user table privileges are superuser privilege. The db and host tables grant database-specific privileges.

The request verification process is described below. (If you are familiar with the access-checking source code, you will notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)

For database-related requests (insert , update , etc.), the server first checks the user's global (superuser) privileges by looking in the user table entry. If the entry allows 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 the Host, Db, and User fields. The Host and User fields are matched to the connecting user's hostname and MySQL user name. The Db field is matched to the database the user wants to access. If there is no entry for the Host and User, access is denied.
If there is a matching db table entry and its Host field is not blank, that entry defines the user's database-specific privileges.
If the matching db table entry's Host field is blank, it signifies that the host table enumerates which hosts should be allowed access to the database. In this case, a further lookup is done in the host table to find a match on the Host and Db fields. If no host table entry 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 the db and host table entries, that is, the privileges that are 'Y' in both entries. (This way you can grant general privileges in the db table entry and then selectively restrict them on a host-by-host basis using the host 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 allows the requested operation, access is granted. Otherwise, the server checks the user's table and column privileges in the tables_priv and columns_priv tables and adds those to the user's privileges. Access is allowed or denied based on the result.

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

It may not be apparent why, if the global user entry privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database-, table-, and column-specific privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT ... SELECT statement, you need both insert and select privileges. Your privileges might be such that the user table entry grants one privilege and the db table entry 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 can be used to maintain a list of secure servers.
Naturally, you should always test your entries in the grant tables (for example, using mysqlaccess) to make sure your access privileges are actually set up the way you think they

Note that administrative privileges ( reload, shutdown , etc.) are specified only in the user table. This is because administrative operations are operations on the server itself and are not database-specific, so there is no reason to list such privileges in the other grant tables. In fact, only the user table need be consulted to determine whether or not you can perform
an administrative operation.

The file privilege is specified only in the user table, too. 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.

There are some things that you cannot do with the MySQL privilege system:

You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.
You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

Types and levels of Privilege

Privileges suitable for granting to regular user: SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP,
Privileges suitable for administration: RELOAD, SHUTDOWN, PROCESS
Special privileges: All (all privileges)

The grant command is used to create users and give them privileges.

General form:

GRANT privileges [columns] ON item TO user@host IDENTIFIED BY 'password' WITH GRANT OPTION;

privileges: comma separated list of privileges
columns: comma separated list of columns

item:
    * or *.*     All databases (Global)
    samdb.*    All tables in samdb
    samdb.clients   Clients table in samdb

WITH GRANT OPTION: Allows user to grant his/her own privileges to others

Example:

GRANT all on samdb.* TO sam@localhost IDENTIFIED BY 'password' ;

Use REVOKE to take privileges away from users.

General form:

REVOKE privileges ON item FROM user;

Example:
revoke alter on samdb from sam;