MySQL User Account Management

12.2 User Account Management

The MySQL access control system enables you to create MySQL accounts and define what each account can do. Several types of privileges can be assigned to an account. They should be granted according to how the account is to be used. Some examples:

• An account that needs only read access to a database can be given just the SELECT privilege.

• An account used to modify data can be given the DELETE, INSERT, and UPDATE privileges.

• Administrative accounts can be given the PROCESS or SUPER privileges for viewing client process activity or killing connections, or the SHUTDOWN privilege for stopping the server.

The MySQL server bases access control on the contents of the grant tables in the mysql database. These tables define MySQL accounts and the privileges they hold. To manage their contents, use the GRANT and REVOKE statements. These statements provide an interface to the grant tables that enables you to specify privileges without having to determine how to modify the tables directly. When you use GRANT and REVOKE to perform a privilege operation, the MySQL server determines what changes to the grant tables are needed and makes the modifications for you.

This section describes the structure and contents of the grant tables and how you set up user accounts using GRANT and REVOKE. Section 12.3, "Client Access Control," describes how the server uses the grant tables to check access privileges when clients connect.

12.2.1 Types of Privileges That MySQL Supports

You can grant several types of privileges to a MySQL account, and you can grant privileges at different levels (globally or just for particular databases, tables, or columns). For example, you can allow a user to select from any table in any database by granting the SELECT privilege at the global level. Or you might grant an account no global privileges, but give it complete control over a specific database. That allows the account to create the database and tables in it, select from the tables, and add new records, delete them, or update them.

The privileges that MySQL supports are shown in the following lists. The first names the administrative privileges and the second names the database-access privileges.

Administrative Privileges:

CREATE TEMPORARY TABLES

FILE

GRANT OPTION

LOCK TABLES

PROCESS

RELOAD

REPLICATION CLIENT

REPLICATION SLAVE

SHOW DATABASES

SHUTDOWN

SUPER

Database-Access Privileges:

ALTER

CREATE

DELETE

DROP

INDEX

INSERT

SELECT

UPDATE

Some privileges not shown in these lists can be assigned to accounts but currently are unused. EXECUTE is reserved for future versions of MySQL, when stored procedures are implemented. REFERENCES may be implemented in relation to foreign key support at some point.

There are also some special privilege specifiers:

• ALL and ALL PRIVILEGES are shorthand for all privileges except GRANT OPTION. They're shorthand for granting all privileges except the ability to give privileges to other accounts.

• USAGE means no privileges other than being allowed to connect to the server. A record is created for the account in the user table. This causes the account to exist, and it can then be used to access the server for limited purposes such as issuing SHOW VARIABLES or SHOW STATUS statements. The account cannot be used to access databases or tables (although you could grant such privileges to the account at a later time).

Privileges can exist at different levels:

• Any privilege can be granted globally. An account that possesses a global privilege can exercise it at any time. Global privileges are therefore quite powerful and are normally granted only to administrative accounts. For example, a global DELETE privilege allows the account to remove records from any table in any database.

• Some privileges can be granted for specific databases: ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, GRANT OPTION, INDEX, INSERT, LOCK TABLES, SELECT, and UPDATE. A database-specific privilege applies to all tables in the database.

• Some privileges can be granted for specific tables: ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, and UPDATE. A table-specific privilege applies to all columns in the table.

• Some privileges can be granted for specific table columns: INSERT, SELECT, and UPDATE.