Managing & Using MySQL, 2nd Edition

Privilege Management

MySQL stores information about who has which privileges in special tables in the system database mysql. It then consults these tables when determining whether to allow certain operations. Because MySQL privilege information is stored as regular database data, you can manage privileges using the SQL you already know. We will cover the structure of these tables later in the chapter. First, however, we will go into the preferred method of managing privileges: ANSI SQL’s GRANT and REVOKE statements.

GRANT and REVOKE

Privilege management includes granting privileges to users and taking them away. ANSI SQL provides two database-independent statements that support these operations. By learning these two statements, you can manage access privileges for MySQL and any other database without knowing the details of how the database actually stores privilege information.

The GRANT statement is the preferred method for adding new users and granting them access to MySQL objects. It has the following syntax:

GRANT privilege  [(column)] [, privilege [(columns)], ...]
ON table1, table2, ..., tablen
TO user [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password'], ...]
[WITH GRANT OPTION]

The simplest form of this statement looks like the following SQL statement:

GRANT SELECT ON Book to andy;

This statement gives the user andy the ability to read data from the table Book. The GRANT statement has three basic components: the privilege, the object, and the user.

The privilege ...