Table of Contents for

MySQL in a Nutshell, 2nd Edition

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:

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:

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.