Home arrow MySQL arrow Page 2 - MySQL User Account Management

12.2.2 The Grant Tables - MySQL

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

  1. MySQL User Account Management
  2. 12.2.2 The Grant Tables
  3. 12.2.3 Granting and Revoking Privileges
  4. The REVOKE Statement
  5. 12.2.4 Changing Account Passwords
  6. 12.2.5 Specifying Resource Limits
By: Sams Publishing
Rating: starstarstarstarstar / 20
July 20, 2006

print this article



Four grant tables in the mysql database contain most of the access control information used by the server. They contain information to indicate what the legal accounts are and the privileges held at each access level by each account:

  • The user table contains a record for each account known to the server. The user record for an account lists its global privileges. It also indicates other information about the account, such as any resource limits it's subject to, and whether client connections that use the account must be made over a secure connection using the Secure Sockets Layer (SSL). Use of SSL connections is not covered on the Professional Exam.

  • The db table lists database-specific privileges for accounts.

  • The tables_priv table lists table-specific privileges for accounts.

  • The columns_priv table lists column-specific privileges for accounts.

Every account must have a user table record because the server uses that table's contents when determining whether to accept or reject client connection attempts. An account also will have records in the other grant tables if it has privileges at other than the global level.

Each grant table has columns that identify which accounts its records apply to:

  • The server decides whether a client can connect based on the Host, User, and Password columns of the user table. An account is defined by a hostname and username, so for a client to be able to connect, some record in the user table must match the host from which the client connects and the username given by the client. In addition, the client must provide the password listed in the matching record.

  • After a client connects, the server determines its access privileges based on the Host and User columns of the user, db, tables_priv, and columns_priv tables. Any privileges enabled in the matching user table record may be used globally by the client. The privileges in the matching records of the other grant tables apply in more limited contexts. For example, privileges in a db table record apply to the database named in the record, but not to other databases.

Use of the grant tables for controlling what clients can do is discussed further in section 12.3, "Client Access Control."

There is also a fifth grant table named host that exists for historical reasons. It is not affected by the GRANT and REVOKE statements, so it's discussed no further here. For more information about the host table, see the MySQL Reference Manual.

The grant tables are stored as MyISAM tables. The MyISAM storage engine is always guaranteed to be enabled, which is not true for storage engines such as InnoDB and BDB.

As already mentioned, the server uses the information in the grant tables to determine whether to allow clients to connect, and to determine for every statement that a connected client issues whether the client has sufficient privileges to execute it. However, the server does not actually access the on-disk grant tables each time it needs to verify client access because that would result in a great deal of overhead. Instead, the server reads the grant tables into memory during its startup sequence and uses the in-memory copies to check client access.

The server refreshes its in-memory copies of the grant tables under the following conditions:

  • You modify a user account in the on-disk tables by issuing a GRANT, REVOKE, or SET PASSWORD statement.

  • You tell the server to reload the tables explicitly by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: