Last week, we began our discussion of MySQL database security. This week, we continue that discussion with user account management. The second of several parts, this article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).
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.
Operations Allowed by Privilege
CREATE TEMPORARY TABLES
Use TEMPORARY with CREATE TABLE
Use statements that read and write files on the server host
Grant privileges to other accounts
Explicitly lock tables with LOCK TABLES
View process (thread) activity
Use FLUSH and RESET
Ask server for information about replication hosts
Act as a replication slave
See all databases with SHOW DATABASES
Shut down the server
Miscellaneous administrative operations
Operations Allowed by Privilege
Modify tables with ALTER TABLE
Create databases and tables
Remove rows from tables
Drop databases and tables
Create and drop indexes
Add rows to tables
Select records from tables
Modify records in tables
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 GRANTOPTION. 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.