Specifications

Types and Levels of Privilege
Three basic types of privileges exist in MySQL: privileges suitable for granting to regular
users, privileges suitable for administrators, and a couple of special privileges. Any user can be
granted any of these privileges, but it’s usually sensible to restrict the administrator type ones
to administrators, according to the principle of least privilege.
You should grant privileges to users only for the databases and tables they need to use. You
should not grant access to the mysql database to anyone except an administrator. This is where
all the users, passwords, and so on are stored. (We will look at this database in Chapter 11.)
Privileges for regular users directly relate to specific types of SQL commands and whether a
user is allowed to run them. We will discuss these SQL commands in detail in the next chapter.
For now, we have given a conceptual description of what they do. These privileges are shown
in Table 8.1. The items under the Applies To column list the objects to which privileges of this
type can be granted.
T
ABLE 8.1 Privileges for Users
Privilege Applies To Description
SELECT tables, Allows users to select rows (records) from tables.
columns
INSERT tables, Allows users to insert new rows into tables.
columns
UPDATE tables, Allows users to modify values in existing table rows.
columns
DELETE tables Allows users to delete existing table rows.
INDEX tables Allows users to create and drop indexes on particular
tables.
ALTER tables Allows users to alter the structure of existing tables by, for
example, adding columns, renaming columns or tables, and
changing data types of columns.
CREATE databases, Allows users to create new databases or tables. If a
tables particular database or table is specified in the GRANT, they
can only CREATE that database or table, which means they
will have to DROP it first.
DROP databases, Allows users to drop (delete) databases or tables.
tables
Most of the privileges for regular users are relatively harmless in terms of system security. The
ALTER privilege can be used to work around the privilege system by renaming tables, but it is
Using MySQL
P
ART II
190
11 7842 CH08 3/6/01 3:38 PM Page 190