Privilege Levels

The privileges we can grant using the GRANT statement can be divided into two basic categories: user -level privileges and administrator-level privileges.

User-Level Privileges

The user-level privileges are shown in Table 11.1.

Table 11.1. User-Level Privileges

Privilege	Meaning
CREATE	User can create tables.
CREATE TEMPORARY TABLES	User can create temporary tables.
DELETE	User can delete rows.
EXECUTE	User can execute procedures.
INDEX	User can create indexes.
INSERT	User can insert rows.
LOCK TABLES	User can lock tables.
SELECT	User can select rows.
SHOW DATABASES	User can execute a SHOW DATABASES command to retrieve the list of available databases.
UPDATE	User can update rows.
USAGE	User can log in, but cannot do anything else.

Administrator-Level Privileges

Privileges that should be granted only to administrators are shown in Table 11.2. Some of these can be granted to users with caution at your discretion, but they should not be granted to users by default.

Table 11.2. Administrator-Level Privileges

Privilege	Meaning
ALL	User has all the privileges except WITH GRANT OPTION .
ALTER	User can alter tables. You may give this to some power users, but proceed with caution because it may be used to change the privilege tables.
DROP	User can drop tables. You may give this to trusted users.
FILE	User can load data from a file. Again, you may give this to trusted users. Beware of users trying to load arbitrary files, such as /etc/passwd or similar files!
PROCESS	User can show full process list ”that is, see all the processes that MySQL is executing.
RELOAD	User can use the FLUSH statement. This has various purposes. We will look at FLUSH PRIVILEGES later in this chapter and will revisit FLUSH in Chapter 13.
REPLICATION CLIENT	User can check where the masters and slaves are.
REPLICATION SLAVE	Special privilege designed for the special replication user on the slave. See Chapter 16 for more details.
SHUTDOWN	User can run mysqladmin shutdown . For more information see Chapter 13.
SUPER	User can connect even if MySQL has its maximum number of connections and can execute the commands CHANGE MASTER , KILL (thread) , mysqladmin debug , PURGE MASTER LOGS , and SET GLOBAL .
WITH GRANT OPTION	User can pass on any privileges he has.

There is one other privilege called REFERENCES . This is reserved for future use, and although you can grant it, at present, it doesn't do anything.