MySQL in a Nutshell, 2nd Edition

Chapter 4. Security and User Statements and Functions

User access and privileges can be global (i.e., apply to all databases on the server), or they can be database-specific, table-specific, or column-specific. In version 5 of MySQL, users can also be limited to particular functions and procedures.

In addition to security-related SQL statements, users can be limited in their use of MySQL resources in order to prevent the monopolization of resources and the indirect denial of service to other users. Thus, you can limit the number of connections or the maximum resources per hour for a user.

The primary information regarding user access and privileges is stored in a set of regular MyISAM tables, known as the grant tables, that reside in the mysql database on the server. The tables are:

user

Global privileges

db

Database-specific privileges

tables_priv

Table-specific privileges

columns_priv

Column-specific privileges

Several other tables provide fine-tuning for user access and security. Execute SHOW TABLES FROM mysql; to get a list on your server. You can manipulate the data in these tables directly with standard SQL statements, such as INSERT, UPDATE, and DELETE, followed by the FLUSH PRIVILEGES statement to update the server’s cache. However, it’s recommended that you use specialized SQL statements to manage users and assign access rights:

CREATE USER

To create new users

GRANT

To create a user account, assigning privileges for a new user account, or assigning privileges to an existing user

REVOKE

To remove privileges

RENAME USER

To change a user’s name

SET PASSWORD

To change a password

DROP USER

To delete a user’s account

All of these statements are described in this chapter. This chapter also lists and explains MySQL functions related to user maintenance and several related to database and network security.