12.2.3 Granting and Revoking Privileges - MySQL

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'@'%'.