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.

12.2.2 The Grant Tables

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.

12.2.3 Granting and Revoking Privileges

It's possible to manage MySQL accounts by modifying the grant tables directly with SQL statements such as INSERT, DELETE, and UPDATE. The procedure described in section 12.1.2, "Securing the Initial MySQL Accounts," is an example of how UPDATE and DELETE can be used in this way. In general, however, the recommended way to set up and modify MySQL accounts is to use the GRANT and REVOKE statements because they offer these advantages:

• It's easier to use GRANT and REVOKE than to modify the grant tables directly. The syntax of GRANT and REVOKE is more natural and less cumbersome for expressing privilege operations because that's what it's designed for. When you use GRANT and REVOKE, the server determines the necessary modifications to the grant tables and makes the changes for you.

• With GRANT and REVOKE, the server automatically reloads the in-memory contents of the grant tables. If you modify the tables directly, you must explicitly tell the server to reload the tables by using a FLUSH PRIVILEGES statement or a mysqladmin flush-privileges command.

In addition to GRANT and REVOKE, the SET PASSWORD statement is useful when all you want to do is change an account's password. SET PASSWORD causes the server to automatically refresh its in-memory grant tables when you use it.

Despite the advantages of GRANT and REVOKE, it is occasionally necessary to manipulate the grant tables directly. The principal reason for this is that REVOKE does not remove records from the user table. You can use REVOKE to disable the global privileges recorded in that table, but it leaves the record in the table in case you want to assign different privileges later. If you want to eliminate all traces of an account from the grant tables, you must also use DELETE to remove its user table record.

12.2.3.1 The GRANT Statement

The syntax for the GRANT statement includes several sections. In simplest form, you specify the following:

• The privileges to be granted

• How broadly the privileges apply

• The account that should be given the privileges

• A password

As an example, the following statement grants the SELECT privilege for all tables in the world database to a user named jim, who must connect from the local host and use a password of Abc123:

GRANT SELECT ON world.* TO 'jim'@'localhost' IDENTIFIED BY 'Abc123';

The parts of the statement have the following effects:

• The statement begins with the GRANT keyword and one or more privilege names indicating which privileges are to be granted. Privilege names are not case sensitive. To list multiple privileges, separate them by commas. For example, if you want jim to be able to manipulate records in the world database, not just retrieve them, write the GRANT statement like this:

  
  

  
  GRANT SELECT, INSERT, DELETE, UPDATE ON world.*
  
  TO 'jim'@'localhost' IDENTIFIED BY 'Abc123';
  
  

  
  

• The ON clause specifies the level of the granted privileges (how broadly they apply). You can grant privileges globally, for a specific database, or for a specific table. The ON syntax for these levels is as follows:

  
  

  
  ON *.*
  
  ON db_name.*
  
  ON db_name.table_name
  
  

  
  

  For the formats that begin with db_name., it's allowable to omit the database name qualifier and specify just * or table_name. In these cases, the privileges are granted to all tables in the current database or to the named table in the current database. Be sure that you know what the current database is, to avoid granting privileges to tables in the incorrect database.

  To grant privileges at a column-specific level, use an ON clause that names a particular table, and specify a comma-separated list of column names within parentheses after each privilege to be granted. The following statement indicates that the named account can retrieve three of the columns in the City table of the world database, but can update only two of them:

  
  

  
  GRANT SELECT (ID, Name, CountryCode), UPDATE (Name, CountryCode)
  
  ON world.City TO 'jim'@'localhost'
  
  IDENTIFIED BY 'Abc123';
  
  

  
  

• The TO clause specifies the account to be granted the privileges. An account name consists of a username and the name of the client host from which the user must connect to the server. The account name is given in 'user_name'@'host_name' format. More detail on this format is given later, but note that the user and host parts of account names should be quoted separately. Quotes actually are necessary only for values that contain special characters such as dashes. If a value is legal as an unquoted identifier, the quotes are optional. However, quotes are always acceptable and examples shown here use them.

  Because an account name includes a hostname part, it's possible to set up separate accounts for different users who have the same username but connect from different hosts.

• The IDENTIFIED BY clause is optional. If present, it assigns a password to the account. If the account already exists and IDENTIFIED BY is given, the password replaces any old one. If the account exists but IDENTIFIED BY is omitted from the GRANT statement, the account's current password remains unchanged. If an account has no password, clients can use it to connect to the server without a password!

To specify an anonymous-user account (that is, an account that matches any username), specify an empty string for the user part of the account name:

GRANT SELECT ON world.* TO ''@'localhost';

The host part of an account name may be given in any of the following formats:

• The name localhost.

• A hostname, such as myhost.example.com.

• An IP number, such as 192.168.1.47.

• A pattern containing the % or _ wildcard characters. Patterns are useful for setting up an account that allows a client to connect from any host in an entire domain or subnet. A host value of %.example.com matches any host in the example.com domain. A host value of 192.168.% matches any host in the 192.168 subnet. A host value of % matches any host, allowing the client to connect from anywhere.

• An IP number/netmask combination. The value allows a client to connect from any host with an address that matches the IP number for all bits that are 1 in the netmask. For example, a value of 10.0.0.0/255.255.255.0 matches any host with 10.0.0 in the first 24 bits of its IP number. This format is useful for allowing an account with a given username to connect from any host in a subnet.

It's allowable to omit the host part of an account name in the GRANT statement. An account name specified as 'user_name' is equivalent to 'user_name'@'%'.

Keep the proper perspective in mind when specifying the host part of an account name in GRANT statements. When you connect to the server using a client program, you specify the host to which you want to connect. On the other hand, when the server checks the client against Host column values in the grant tables, it uses the host from which the client connects. When setting up an account with GRANT, you should specify the client host from the server's point of view. For example, if the server runs on server.example.com and you want to allow jim to connect from client.example.com, the GRANT statement should look like this:

GRANT ... TO 'jim'@'client.example.com' ... ;

Be aware that it is possible to have multiple accounts that could apply to a given client. For example, if you set up accounts for 'jim'@'localhost' and 'jim'@'%', the server could use either one when jim connects from the local host. The rules that the server employs to determine which account to use in such cases are covered in section 12.3, "Client Access Control."

If you want to give an account the capability to grant its privileges to other accounts, add a WITH GRANT OPTION clause to the statement. For example, if you want jim to have read access to the world database and to be able to create other users that have read access to that database, use this statement:

GRANT SELECT ON world.* TO 'jim'@'localhost'

IDENTIFIED BY 'Abc123'

WITH GRANT OPTION;

To find out what privileges a particular account has, use the SHOW GRANTS statement. It displays the GRANT statements that would be required to set up the account. The account name for this statement has the same 'user_name'@'host_name' format as that used with GRANT. You can always see your own privileges with SHOW GRANTS. You cannot see the privileges for other accounts unless you have the SELECT privilege for the mysql database.

Suppose that you've set up an account for a user jen who connects from the host myhost.example.com. To see this account's privileges, use the following statement:

mysql> SHOW GRANTS FOR 'jen'@'myhost.example.com';

+----------------------------------------------------------------+

| Grants for jen@myhost.example.com                              |

+----------------------------------------------------------------+

| GRANT FILE ON *.* TO 'jen'@'myhost.example.com'                |

| GRANT SELECT ON `mydb`.* TO 'jen'@'myhost.example.com'         |

| GRANT UPDATE ON `test`.`mytable` TO 'jen'@'myhost.example.com' |

+----------------------------------------------------------------+

The output displayed here by SHOW GRANTS consists of three GRANT statements. Their ON clauses indicate that jen has privileges at the global, database, and table levels, respectively.

If the account has a password, SHOW GRANTS displays an IDENTIFIED BY PASSWORD clause at the end of the GRANT statement that lists the account's global privileges. (The word PASSWORD after IDENTIFIED BY indicates that the password value shown is the encrypted value stored in the user table, not the actual password.) If the account can grant some or all of its privileges to other accounts, SHOW GRANTS displays WITH GRANT OPTION at the end of each GRANT statement to which it applies.

SHOW GRANTS displays privileges only for the exact account specified in the statement. For example, the preceding SHOW GRANTS statement shows privileges only for 'jen'@'myhost.example.com', not for 'jen'@'%.example.com', 'jen'@'%.com', or 'jen'@'%'.

12.2.3.2 The REVOKE Statement

Use the REVOKE statement to revoke privileges from an account. Its syntax has the following sections:

• The keyword REVOKE followed by the list of privileges to be revoked

• An ON clause indicating the level at which privileges are to be revoked

• A FROM clause that specifies the account name

Suppose that jim on the local host has SELECT, DELETE, INSERT, and UPDATE privileges on the world database, but you want to change the account so that he has SELECT access only. To do this, revoke those privileges that allow him to make changes:

REVOKE DELETE, INSERT, UPDATE ON world.* FROM 'jim'@'localhost';

To revoke the GRANT OPTION privilege from an account that has it, you must revoke it in a separate statement. For example, if jill has the ability to grant her privileges for the world database to other users, you can revoke that ability as follows:

REVOKE GRANT OPTION ON world.* FROM 'jill'@'localhost';

If you use REVOKE to remove all the privileges enabled by a record in the db, tables_priv, or columns_priv tables, REVOKE removes the record entirely. However, REVOKE does not remove an account's user table record, even if you revoke all privileges for the account. It's necessary to use DELETE to remove a user record. A later example demonstrates this.

To determine what REVOKE statements are needed to revoke an account's privileges, SHOW GRANTS might be helpful. Consider again the output from SHOW GRANTS for the jen@localhost account:

mysql> SHOW GRANTS FOR 'jen'@'myhost.example.com';

+----------------------------------------------------------------+

| Grants for jen@myhost.example.com                              |

+----------------------------------------------------------------+

| GRANT FILE ON *.* TO 'jen'@'myhost.example.com'                |

| GRANT SELECT ON `mydb`.* TO 'jen'@'myhost.example.com'         |

| GRANT UPDATE ON `test`.`mytable` TO 'jen'@'myhost.example.com' |

+----------------------------------------------------------------+

This output indicates that the account has global, database-level, and table-level privileges. To remove these privileges, convert those GRANT statements to the following corresponding REVOKE statements. The privilege names, privilege levels, and account name must be the same as displayed by SHOW GRANTS:

mysql> REVOKE FILE ON *.* FROM 'jen'@'myhost.example.com';

mysql> REVOKE SELECT ON mydb.* FROM 'jen'@'myhost.example.com';

mysql> REVOKE UPDATE ON test.mytable FROM 'jen'@'myhost.example.com';

After issuing the REVOKE statements, SHOW GRANTS produces this result:

mysql> SHOW GRANTS FOR 'jen'@'myhost.example.com';

+--------------------------------------------------+

| Grants for jen@myhost.example.com                |

+--------------------------------------------------+

| GRANT USAGE ON *.* TO 'jen'@'myhost.example.com' |

+--------------------------------------------------+

This means that the account no longer has any privileges, although it does still exist and thus can be used to connect to the server. (In other words, the user table still contains a record for the account, but all the global privileges listed in the record are disabled.) To remove the last trace of the account, use a DELETE statement to remove the user table record, and then tell the server to reload the grant tables:

mysql> USE mysql;

mysql> DELETE FROM user WHERE User = 'jen' AND Host = 'myhost.example.com';

mysql> FLUSH PRIVILEGES;

After that, the account no longer exists and cannot be used to connect to the server.

12.2.3.3 When Privilege Changes Take Effect

The effects of changes to the grant tables apply to existing client connections as follows:

• Table and column privilege changes apply to all statements issued after the changes are made.

• Database privilege changes apply with the next USE statement.

• Changes to global privileges and passwords do not apply to connected clients. They apply the next time a client attempts to connect.

12.2.4 Changing Account Passwords

As discussed earlier, you can specify a password for an account by including an IDENTIFIED BY clause in a GRANT statement. If the account is new, the clause assigns its initial password. If the account already exists, the clause changes its password.

To change an existing account's password without changing any of its privileges, you have two options:

• Use the SET PASSWORD statement, specifying the account name and the new password. For example, to set the password for jim on the local host to NewPass, use this statement:

  
  

  
  SET PASSWORD FOR 'jim'@'localhost' = PASSWORD('NewPass');
  
  

  
  

  Any nonanonymous client can change its own password by omitting the FOR clause:

  
  

  
  SET PASSWORD = PASSWORD('NewPass');
  
  

  
  

• Use GRANT with the USAGE privilege specifier at the global level and an IDENTIFIED BY clause:

  
  

  
  GRANT USAGE ON *.* TO 'jim'@'localhost' IDENTIFIED BY 'NewPass';
  
  

  
  

  USAGE means no privileges, so the statement changes the password without granting any privileges.

Note that with SET PASSWORD, you use PASSWORD() to encrypt the password, whereas with GRANT, you do not use it.

To allow a user to connect without specifying a password, change the password to the empty string. However, you cannot revoke the password this way with REVOKE. Instead, use either of the following statements:

SET PASSWORD FOR 'jim'@'localhost' = '';

GRANT USAGE ON *.* TO 'jim'@'localhost' IDENTIFIED BY '';

Be certain that you want to do this, however. It isn't a good idea to have accounts without passwords.

12.2.5 Specifying Resource Limits

By default, there is no limit on the number of times that a client can connect to the server or the number of queries it can issue. If that is not suitable, GRANT can establish limits on an account's resource consumption for the following characteristics:

• The number of times per hour the account is allowed to connect to the server

• The number of queries per hour the account is allowed to issue

• The number of updates per hour the account is allowed to issue

Each of these resource limits is specified using an option in a WITH clause. The following example creates an account that can use the test database, but can connect to the server a maximum of only 10 times per hour. The account can issue 50 queries per hour, and at most 20 of those queries can modify data:

GRANT ALL ON test.* TO 'quinn'@'localhost' IDENTIFIED BY 'SomePass'

WITH MAX_CONNECTIONS_PER_HOUR 10

MAX_QUERIES_PER_HOUR 50

MAX_UPDATES_PER_HOUR 20;

The order in which you name the options in the WITH clause doesn't matter.

To reset an existing limit to the default of no limit, specify a value of zero. For example:

GRANT USAGE ON *.* TO 'quinn'@'localhost'

WITH MAX_CONNECTIONS_PER_HOUR 0;