34.1. User Account Management | MySQL 5.0 Certification Study Guide

The MySQL access control system enables you to create MySQL accounts and define what each account can do. In MySQL, the concept of "account" is tied to two things: a username and a hostname. That is, when you connect to the server, it checks not only the username that you specify, but also what host you're connecting from. One implication of this concept of an account is that it is possible to set up separate accounts for different users who have the same username but connect from different hosts.

In SQL statements that require account names, the name is given in 'user_name'@'host_name' format. It is also possible to specify a pattern for the host part so that the account can be used for connecting to the MySQL server from several client hosts. For example, an account name given as 'maria'@'%.example.com' would apply to a user named maria who connects from any host in the example.com domain.

Several types of privileges can be granted to an account. Privileges 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 only the SELECT privilege.
An account that needs 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 statements such as CREATE USER, GRANT, and REVOKE. These statements provide an interface to the grant tables that enables you to specify account-management operations without having to determine how to modify the tables directly. The MySQL server determines what changes to the grant tables are needed and makes the modifications for you.

The following discussion describes the structure and contents of the grant tables and the various SQL statements that help you manage user accounts. Section 34.2, "Client Access Control," describes how the server uses the grant tables to check access privileges when clients connect.

Note: When you install MySQL, any initial accounts specified in the grant tables should be given passwords. The procedure for doing this is covered in Section 35.5.1, "Securing the Initial MySQL Accounts."

34.1.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 tables. The first lists the administrative privileges and the second lists the privileges that control access to databases or objects stored in databases.

Administrative Privileges:

Privilege	Operations Allowed by Privilege
`CREATE TEMPORARY TABLES`	Use `TEMPORARY` with `CREATE TABLE`
`CREATE USER`	Create, drop, rename accounts
`FILE`	Use statements that read or write files on the server host
`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 database names with `SHOW DATABASES`
`SHUTDOWN`	Shut down the server
`SUPER`	Miscellaneous administrative operations

Database-Access Privileges:

Privilege	Operations Allowed by Privilege
`ALTE`	Modify tables with `ALTER TABLE`
`ALTER ROUTINE`	Alter or drop stored routines
`CREATE`	Create databases and tables
`CREATE ROUTINE`	Create stored routines
`CREATE VIEW`	Create views
`DELETE`	Remove rows from tables
`DROP`	Drop databases and tables
`EXECUTE`	Execute stored routines
`GRANT OPTION`	Grant privileges to other accounts
`INDEX`	Create and drop indexes
`INSERT`	Add rows to tables
`SELECT`	Select records from tables
`SHOW VIEW`	Use `SHOW CREATE VIEW`
`UPDATE`	Modify records in tables

There is also a REFERENCES privilege, but it is unused currently.

There are also some special privilege specifiers:

ALL and ALL PRIVILEGES are shorthand for "all privileges except GRANT OPTION." That is, they are 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. Granting this "privilege" causes a record to be created in the user table for the account, but without any privileges. 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 database contents such as 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, CREATE VIEW, DELETE, DROP, GRANT OPTION, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, and UPDATE. A database-level privilege applies to all tables and stored routines in the database.
Some privileges can be granted for specific tables: ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, and UPDATE. A table-level privilege applies to all columns in the table.
Some privileges can be granted for specific table columns: INSERT, SELECT, and UPDATE.
Some privileges can be granted for specific stored routines: EXECUTE, ALTER ROUTINE, and GRANT OPTION.

34.1.2. The Grant Tables

Several 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 is 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 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.
The procs_priv table lists privileges that accounts have for stored procedures and functions.

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, columns_priv, and procs_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 34.2, "Client Access Control."

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

If you look in the mysql database, you might also see a user_info table. This table is created by MySQL Administrator, but has nothing to do with access control, so it's not covered here.

The grant tables are created during MySQL installation as MyISAM tables. The MyISAM storage engine is always guaranteed to be enabled, which is not true for optional storage engines such as InnoDB. (InnoDB is enabled by default, but it can be turned off.)

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 it 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 account-management statement such as CREATE USER, GRANT, REVOKE, or SET PASSWORD.
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.

34.1.3. Approaches to Account Management

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 35.5.1, "Securing the Initial MySQL Accounts," shows 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 statements such as CREATE USER, GRANT, and REVOKE that are intended specifically for account management. These statements offer the following advantages:

It's easier to use account-management statements than to modify the grant tables directly. Their syntax is more natural and less cumbersome for expressing privilege operations because that's what they're designed for. When you use statements such as CREATE USER and GRANT, the server determines the necessary modifications to the grant tables and makes the changes for you.
When you issue an account-management statement, the server automatically reloads the in-memory contents of the grant tables. If you modify the tables directly with a statement such as INSERT or DELETE, you must explicitly tell the server to reload the tables afterward by using a FLUSH PRIVILEGES statement or a mysqladmin flush- privileges command.

34.1.4. Creating and Dropping User Accounts

Three statements create, remove, or rename user accounts:

CREATE USER creates a new account and optionally assigns it a password. This statement creates a record in the user table. It does not grant any privileges; to do so, use the GRANT statement later. (Alternatively, you can use GRANT to create the account and grant it privileges at the same time.)
The following statement creates an account for jim@localhost and assigns the account a password of Abc123. That is, a user named jim will be able to connect from the local host, but must specify a password of Abc123:
```
 CREATE USER 'jim'@'localhost' IDENTIFIED BY 'Abc123'; 
```
If the CREATE USER includes no IDENTIFIED BY clause, the account is created with no password. This is insecure and not recommended.
DROP USER revokes all privileges for an existing account and then removes the account. It deletes all records for the account from any grant table in which they exist. To revoke privileges without removing the account itself, use the REVOKE statement.
The following statement removes the jim@localhost account:
```
 DROP USER 'jim'@'localhost'; 
```
RENAME USER changes the account name for an existing account. It can change the username or hostname parts of the account name, or both:
```
 RENAME USER 'jim'@'localhost' TO 'john'@'localhost'; 
```

For all three statements, accounts are named in 'user_name'@'host_name' format. More detail on account names is given in Section 34.1.5, "Specifying Account Names."

34.1.5. Specifying Account Names

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 SQL statements using 'user_name'@'host_name' format. 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 example SQL statements shown in this study guide use them.

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:

 CREATE USER ''@'localhost';

In general, it is best to avoid creating anonymous accounts, especially ones that have no password. Letting anyone connect to your server opens up your MySQL installation to security risks.

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 from an account name. A name specified as 'user_name' in an account-management statement is equivalent to 'user_name'@'%'.

Keep the proper perspective in mind when specifying the host part of an account name. 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, 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 CREATE USER statement should look like this:

 CREATE USER '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 34.2, "Client Access Control."

34.1.6. Granting Privileges

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

The privileges to be granted
What the privileges apply to
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';

If the account does not already exist, GRANT creates it and assigns the designated privileges. If the account does exist, GRANT modifies it by adding the privileges.

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 or for a specific database, table, or stored routine. The ON syntax for these levels is as follows:
```
 ON *.* ON db_name.* ON db_name.table_name ON db_name.routine_name 
```
For the formats that begin with db_name., it's allowable to omit the database name qualifier and specify just *, table_name, or routine_name. In these cases, the privileges are granted to all tables in the current database or to the named table or routine in the current database. Be sure that you know what the current database is, to avoid granting privileges to tables or routines in the incorrect database.
When granting privileges for a table or stored routine, there is an ambiguity if there are multiple objects (table, procedure, or function) with the same name. To indicate the type of object explicitly, use the keyword TABLE, PROCEDURE, or FUNCTION preceding the name:
```
 ON TABLE db_name.table_name ON PROCEDURE db_name.routine_name ON FUNCTION db_name.routine_name 
```
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 in Section 34.1.5, "Specifying Account Names," 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.
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!
As a security measure, you can prevent the GRANT statement from creating new accounts unless an IDENTIFIED BY clause is given. To do this, enable the NO_AUTO_CREATE_USER SQL mode.

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.

To see your own privileges, use SHOW GRANTS without an account name or with CURRENT_USER():

 SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER();

To see the privileges for a specific account, specify that account name in the statement. 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 which 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@%.

34.1.7. Revoking Privileges

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';

To revoke all privileges held by an account at any level, REVOKE supports a special syntax (note that this form of REVOKE has no ON clause):

 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'james'@'localhost';

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 some or all of these privileges, convert the GRANT statements to the corresponding REVOKE statements. The privilege names, privilege levels, and account name must be the same as displayed by SHOW GRANTS. For example, to revoke the global FILE privilege and the table-level privilege for test.mytable, issue these statements:

 mysql> REVOKE FILE ON *.* 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'       | | GRANT SELECT ON `mydb`.* TO 'jen'@'myhost.example.com' | +--------------------------------------------------------+

If you use REVOKE to remove all the privileges enabled by a record in the db, tables_priv, columns_priv, or procs_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. This means that although the account no longer has any privileges, it still exists and thus can be used to connect to the server. If you want to eliminate all traces of an account from the grant tables, you should use the DROP USER statement instead. After that, the account no longer exists and cannot be used to connect to the server.

34.1.8. Changing Account Passwords

As discussed earlier, you can specify a password for an account by including an IDENTIFIED BY clause in a CREATE USER or GRANT statement. For CREATE USER, the clause assigns the initial account password. For GRANT, the clause assigns the initial password or changes the current password, depending on whether the account is new or already exists.

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 non-anonymous 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 CREATE USER and 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. Accounts that have no password are insecure.

34.1.9. When Privilege Changes Take Effect

When you change the grant tables with an account-management statement, the effects of changes 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 a connected client. They apply the next time the client attempts to connect.

34.1.10. Specifying Resource Limits

By default, there is no limit on the number of times 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
The number of times the account can connect simultaneously to the server

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 does not matter.

To reset an existing limit for any of the per-hour resources 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;

The MAX_USER_CONNECTIONS limit also can be set to zero to set it to the default. However, that does not mean "no limit." Instead, when this resource is set to zero, the value that applies to the account is the value of the max_user_connections system variable.

34.1.11. Privileges Needed for Account Management

The statements that are used for account management in MySQL require the following privileges:

CREATE USER requires the CREATE USER privilege or the INSERT privilege for the mysql database.
DROP USER requires the CREATE USER privilege or the DELETE privilege for the mysql database.
RENAME USER requires the CREATE USER privilege or the UPDATE privilege for the mysql database.
GRANT requires the GRANT OPTION privilege, and you also must have the privileges that you are granting.
REVOKE without ALL PRIVILEGES requires the GRANT OPTION privilege, and you also must have the privileges that you are revoking. REVOKE ALL PRIVILEGES requires the CREATE USER privilege or the UPDATE privilege for the mysql database.
Use of SET PASSWORD to change another account's password requires the CREATE USER privilege or the UPDATE privilege for the mysql database. Any non-anonymous client can use SET PASSWORD to change the password for its own account, and no special privileges are required.
SHOW GRANTS requires the SELECT privilege for the mysql database to see another account's grants. It requires no special privileges to see the grants for your own account.