MySQL User Account Management
(Page 1 of 6 )

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).

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:

Privilege	Operations Allowed by Privilege
CREATE TEMPORARY TABLES	Use TEMPORARY with CREATE TABLE
FILE	Use statements that read and write files on the server host
GRANT OPTION	Grant privileges to other accounts
LOCK TABLES	Explicitly lock tables with LOCK TABLES
PROCESS	View process (thread) activity
RELOAD	Use FLUSH and RESET
REPLICATION CLIENT	Ask server for information about replication hosts
REPLICATION SLAVE	Act as a replication slave
SHOW DATABASES	See all databases with SHOW DATABASES
SHUTDOWN	Shut down the server
SUPER	Miscellaneous administrative operations

Database-Access Privileges:

Privilege	Operations Allowed by Privilege
ALTER	Modify tables with ALTER TABLE
CREATE	Create databases and tables
DELETE	Remove rows from tables
DROP	Drop databases and tables
INDEX	Create and drop indexes
INSERT	Add rows to tables
SELECT	Select records from tables
UPDATE	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 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.

Next: 12.2.2 The Grant Tables >>
 

More MySQL Articles
More By Sams Publishing

Comments On: MySQL User Account Management

· This article is an excerpt from the book "MySQL 5.0 Certification Guide," published...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.