Understanding MySQL Privileges

The primary function of the MySQL authorization system is to associate an authenticated user with privileges on a database, such as SELECT, INSERT, UPDATE, and DELETE. The authorization system’s functionality includes the ability to have anonymous users and to enable specific functions such as LOAD DATA INFILE and various administrative operations. This authorization ensures that users can perform only the operations for which they have been granted appropriate privileges.

Determining Appropriate User Privileges

You can grant several types of privileges to a MySQL account at different levels: globally, or for particular databases, tables, or columns. For example, you can give a user the ability to select from any table in any database by granting the user the SELECT privilege at the global level.

You can give an account complete control over a specific database without having any permissions on other databases. The account can then create the database, create tables and other database objects, select from the tables, and add, delete, or update new records.

Granting Administrative Privileges

The following global privileges apply to administrative users:

• FILE: Allows users to instruct the MySQL server to read and write files in the server host file system.
• PROCESS: Allows users to use the SHOW PROCESSLIST statement to see all statements that clients are executing.
• SUPER: Allows users to kill other client connections or change the runtime configuration of the server.
• ALL: Grants all privileges except the ability to grant privileges to other users.

The SUPER administrative privilege provides users with the ability to perform additional tasks, including setting global variables and terminating client connections. There are also some special privilege specifiers:

– Use ALL and ALL PRIVILEGES for granting all privileges except the ability to give privileges to other accounts. Use GRANT ALL … WITH GRANT OPTION to grant all privileges including the ability to give privileges to other accounts.
– Use USAGE to grant the ability to connect to the server. This privilege creates a record in the user table for the account, but without any privileges. The account 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 such privileges can be granted at a later time.

Other administrative privileges include CREATE USER, CREATE TEMPORARY TABLES, SHOW DATABASES, LOCK TABLES, RELOAD, and SHUTDOWN. Administrative privileges, including those in the slide, can be used to compromise security, access privileged data, or perform denial-of-service attacks on a server. Grant administrative privileges sparingly, because they can be abused by malicious or careless users.

GRANT Statement

The GRANT statement creates a new account or modifies an existing account.

GRANT syntax:

 GRANT SELECT ON world_innodb.* TO 'kari'@'localhost' IDENTIFIED BY 'Abc123';

In the syntax/example above, the statement grants the SELECT privilege for all tables in the world_innodb database to a user named kari, who must connect from the local host and use a password of Abc123.

Clauses of the statement:
1. Privileges to be granted
2. Privilege level:

• Global: *.*
• Database: [db_name].*
• Table: [db_name].[table_name]
• Stored routine: [db_name].[routine_name]

3. Account to which you are granting the privilege.
4. An optional password.

The clauses of the GRANT statement have the following effects:

• GRANT keyword: Specifies one or more privilege names indicating which privileges you are granting. Privilege names are not case-sensitive. To list multiple privileges, separate them by commas.
• ON clause: Specifies the level of the privileges that you are granting.
• TO clause: Specifies the account to which you are granting the privileges. If the account does not already exist, the statement creates it.
• IDENTIFIED BY clause: (Optional) Assigns the specified password to the account. If the account already exists, the password replaces any old one.

Omitting the IDENTIFIED BY clause has the following effect:

• If the account in the TO clause exists, its password remains unchanged.
• If the account in the TO clause does not exist, it is created with a blank password.

As a security measure, enable the NO_AUTO_CREATE_USER SQL mode to prevent the GRANT statement from creating new accounts when you do not specify an IDENTIFIED BY clause.

Display GRANT Privileges

SHOW GRANTS displays the statements that re-create the privileges for the specified user. It shows privileges only for the exact account specified in the statement. The example below, shows privileges only for kari@myhost.example.com, not for kari@%.

 SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

You can also specifiy an account name to show grants for:

 mysql>SHOW GRANTS FOR
'kari'@'myhost.example.com'; 
+----------------------------------------------------------------+ 
| Grants for kari@myhost.example.com                             | 
+----------------------------------------------------------------+ 
| GRANT FILE ON *.* TO 'kari'@'myhost.example.com'               | 
| GRANT SELECT ON `world_innodb`.* TO 'kari'@'myhost.example.com‘| 
| IDENTIFIED BY PASSWORD                                         | 
| '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA'                    | 
+----------------------------------------------------------------+

The output displayed above consists of two GRANT statements. Their ON clauses display privileges at the global and database levels, respectively. If the account has a password, SHOW GRANTS displays an IDENTIFIED BY PASSWORD clause at the end of the GRANT statement; this clause 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. Because the password is stored using one-way encryption, MySQL has no way to display the unencrypted password.

If the account can grant some or all of its privileges to other accounts, the output displays WITH GRANT OPTION at the end of each GRANT statement to which it applies.

User Privilege Restrictions

You cannot explicitly deny access to a specific user. You cannot associate a password with a specific object such as a database, table, or routine.

Grant Tables

MySQL server reads the grant tables from the mysql database into memory at startup, and bases all access control decisions on those tables. Tables correspond to privilege levels:

The user table contains a record for each account known to the server, as well as its global privileges. It also indicates other information about the account, such as:

• Any resource limits that it is subject to
• Whether client connections that use the account must be made over a secure connection using SSL

Every account must have a user table record; the server determines whether to accept or reject each connection attempt by reading the contents of that table. Each account also has records in the other grant tables if it has privileges at a level other than the global level.

Use of Grant Tables

The server determines whether a client can connect based on the Host, User, and Password columns of the user table. To connect successfully, MySQL must match a record in the user table to the host from which the client connects, the username given by the client, and the password listed in the matching record.

After a client connects, MySQL checks the access privileges for each statement by matching the account’s identity to the Host and User columns of the privilege tables.

• Privileges in each row of the user table apply globally to the account identified by its Host and User columns.
• The privileges in the matching records of the db, tables_priv, columns_priv, and procs_priv tables apply at the level identified by the name of the specific privilege table.

For example, privileges in a db table record apply to the database named in the record, but not to other databases. The MySQL installation process creates the grant tables.

• Grant tables use the MyISAM storage engine.
• MyISAM is guaranteed to be available.

Effecting Privilege Changes

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 by issuing an account management statement such as CREATE USER, GRANT, REVOKE, or SET PASSWORD.
• You reload the tables explicitly by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

Avoid making changes directly to the grant tables for the following reasons:

• The syntax of account management statements is designed to be clear and straightforward.
• If you make a mistake in an account management statement, the statement fails and does not change any settings.
• If you make a mistake when changing grant tables directly, you can lock all users out of the system.

When are the changes applied

• Changes to global privileges and passwords apply only to subsequent connections of that account.
• Changes to database-level privileges apply after the client’s next USE db_name statement.
• Changes to table and routine privileges apply immediately.

Revoking Account Privileges

Use the REVOKE statement to revoke specific SQL statement privileges:

REVOKE DELETE, INSERT, UPDATE ON world_innodb.* FROM 'Amon'@'localhost';

Use the REVOKE statement to revoke privileges from an account. You may choose to revoke privileges for various reasons, such as a reduction in a user’s required access. The REVOKE statement’s syntax has the following clauses:

• REVOKE keyword: Specifies the list of privileges to be revoked.
• ON clause: Indicates the level at which privileges are to be revoked.
• FROM clause: Specifies the account name.

The example shown above, assume that Amon has SELECT, DELETE, INSERT, and UPDATE privileges on the world_innodb database, but you want to change the account so that he has SELECT access only. The first example revokes the privileges that enable him to make changes.

To revoke the privilege to grant privileges to other users:

REVOKE GRANT OPTION ON world_innodb.* FROM 'Jan'@'localhost';

The above example revokes Jan’s ability to grant to other users any privileges that he holds for the world_innodb database, by revoking the GRANT OPTION privilege from his account.

To revoke all privileges, including granting privileges to others:

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

The above example revokes all privileges held by Sasha’s account (at any level), by revoking ALL PRIVILEGES and GRANT OPTION from her account.