information about who has which
privileges in special
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:
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.
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
GRANT statement has three basic components: the
privilege, the object, and the user.
The privilege ...