4.1 Privileges Provided by MySQL

MySQL provides privileges that apply in different contexts and at
different levels of operation:

Administrative privileges enable users to manage operation of
the MySQL server. These privileges are global because they are
not specific to a particular database.

Database privileges apply to a database and to all objects
within it. These privileges can be granted for specific
databases, or globally so that they apply to all databases.

Privileges for database objects such as tables, indexes,
views, and stored routines can be granted for specific objects
within a database, for all objects of a given type within a
database (for example, all tables in a database), or globally
for all objects of a given type in all databases).

Some releases of MySQL introduce changes to the structure of the
grant tables to add new privileges or features. To make sure that
you can take advantage of any new capabilities, update your grant
tables to have the current structure whenever you update to a new
version of MySQL. See mysql_upgrade — Check and Upgrade MySQL Tables.

The following table shows the privilege names used at the SQL
level in the GRANT and
REVOKE statements, along with the
column name associated with each privilege in the grant tables and
the context in which the privilege applies.

The following list provides a general description of each
privilege available in MySQL. Particular SQL statements might have
more specific privilege requirements than indicated here. If so,
the description for the statement in question provides the
details.

The ALL or
ALL PRIVILEGES
privilege specifier is shorthand. It stands for “all
privileges available at a given privilege level”
(except GRANT OPTION). For
example, granting ALL at the
global or table level grants all global privileges or all
table-level privileges.

However, other operations on a temporary table, such as
INSERT,
UPDATE, or
SELECT, require additional
privileges for those operations for the database containing
the temporary table, or for the nontemporary table of the same
name.

To keep privileges for temporary and nontemporary tables
separate, a common workaround for this situation is to create
a database dedicated to the use of temporary tables. Then for
that database, a user can be granted the
CREATE TEMPORARY TABLES
privilege, along with any other privileges required for
temporary table operations done by that user.

The DELETE privilege enables
rows to be deleted from tables in a database.

The DROP privilege enables you
to drop (remove) existing databases, tables, and views. The
DROP privilege is required in
order to use the statement ALTER TABLE ... DROP
PARTITION on a partitioned table. The
DROP privilege is also required
for TRUNCATE TABLE.
If you grant the DROP
privilege for the mysql database to a user,
that user can drop the database in which the MySQL access
privileges are stored.

The EVENT privilege is required
to create, alter, drop, or see events for the Event Scheduler.

The EXECUTE privilege is
required to execute stored routines (procedures and
functions).

The FILE privilege gives you
permission to read and write files on the server host using
the LOAD DATA
INFILE and
SELECT ... INTO
OUTFILE statements and the
LOAD_FILE() function. A user
who has the FILE privilege can
read any file on the server host that is either world-readable
or readable by the MySQL server. (This implies the user can
read any file in any database directory, because the server
can access any of those files.) The
FILE privilege also enables the
user to create new files in any directory where the MySQL
server has write access. This includes the server's data
directory containing the files that implement the privilege
tables. As a security measure, the server will not overwrite
existing files.

The GRANT OPTION privilege
enables you to give to other users or remove from other users
those privileges that you yourself possess.

The INDEX privilege enables you
to create or drop (remove) indexes.
INDEX applies to existing
tables. If you have the CREATE
privilege for a table, you can include index definitions in
the CREATE TABLE statement.

The LOCK TABLES privilege
enables the use of explicit LOCK
TABLES statements to lock tables for which you have
the SELECT privilege. This
includes the use of write locks, which prevents other sessions
from reading the locked table.

The PROCESS privilege pertains
to display of information about the threads executing within
the server (that is, information about the statements being
executed by sessions). The privilege enables use of
SHOW PROCESSLIST or
mysqladmin processlist to see threads
belonging to other accounts; you can always see your own
threads. The PROCESS privilege
also enables use of SHOW
ENGINE.

The PROXY privilege enables a
user to impersonate or become known as another user. See
Section 5.8, “Proxy Users”. This privilege was added in
MySQL 5.5.7.

The RELOAD privilege enables
use of the FLUSH statement. It
also enables mysqladmin commands that are
equivalent to FLUSH operations:
flush-hosts, flush-logs,
flush-privileges,
flush-status,
flush-tables,
flush-threads, refresh,
and reload.

The reload command tells the server to
reload the grant tables into memory.
flush-privileges is a synonym for
reload. The refresh
command closes and reopens the log files and flushes all
tables. The other
flush-xxx
commands perform functions similar to
refresh, but are more specific and may be
preferable in some instances. For example, if you want to
flush just the log files, flush-logs is a
better choice than refresh.

The REPLICATION SLAVE privilege
should be granted to accounts that are used by slave servers
to connect to the current server as their master. Without this
privilege, the slave cannot request updates that have been
made to databases on the master server.

The SELECT privilege enables
you to select rows from tables in a database.
SELECT statements require the
SELECT privilege only if they
actually retrieve rows from a table. Some
SELECT statements do not access
tables and can be executed without permission for any
database. For example, you can use
SELECT as a simple calculator
to evaluate expressions that make no reference to tables:

SELECT 1+1;
SELECT PI()*2;

The SELECT privilege is also
needed for other statements that read column values. For
example, SELECT is needed for
columns referenced on the right hand side of
col_name=expr
assignment in UPDATE statements
or for columns named in the WHERE clause of
DELETE or
UPDATE statements.

The SHOW DATABASES privilege
enables the account to see database names by issuing the
SHOW DATABASE statement. Accounts that do
not have this privilege see only databases for which they have
some privileges, and cannot use the statement at all if the
server was started with the
--skip-show-database option.
Note that any global privilege is a
privilege for the database.

The SHUTDOWN privilege enables
use of the mysqladmin shutdown command.
There is no corresponding SQL statement.

The SUPER privilege enables an
account to use CHANGE MASTER
TO, KILL or
mysqladmin kill to kill threads belonging
to other accounts (you can always kill your own threads),
PURGE BINARY LOGS,
configuration changes using
SET
GLOBAL to modify global system variables, the
mysqladmin debug command, enabling or
disabling logging, performing updates even if the
read_only system variable is
enabled, starting and stopping replication on slave servers,
specification of any account in the DEFINER
attribute of stored programs and views, and enables you to
connect (once) even if the connection limit controlled by the
max_connections system
variable is reached.

The TRIGGER privilege enables
trigger operations. You must have this privilege for a table
to create, drop, or execute triggers for that table.

The UPDATE privilege enables
rows to be updated in tables in a database.

The USAGE privilege specifier
stands for “no privileges.” It is used at the
global level with GRANT to
modify account attributes such as resource limits or SSL
characteristics without affecting existing account privileges.

It is a good idea to grant to an account only those privileges
that it needs. You should exercise particular caution in granting
the FILE and administrative
privileges:

The FILE privilege can be
abused to read into a database table any files that the MySQL
server can read on the server host. This includes all
world-readable files and files in the server's data directory.
The table can then be accessed using
SELECT to transfer its contents
to the client host.

The GRANT OPTION privilege
enables users to give their privileges to other users. Two
users that have different privileges and with the
GRANT OPTION privilege are able
to combine privileges.

The ALTER privilege may be used
to subvert the privilege system by renaming tables.

The SHUTDOWN privilege can be
abused to deny service to other users entirely by terminating
the server.

The PROCESS privilege can be
used to view the plain text of currently executing statements,
including statements that set or change passwords.

The SUPER privilege can be used
to terminate other sessions or change how the server operates.

Privileges granted for the mysql database
itself can be used to change passwords and other access
privilege information. Passwords are stored encrypted, so a
malicious user cannot simply read them to know the plain text
password. However, a user with write access to the
user table Password
column can change an account's password, and then connect to
the MySQL server using that account.

User Comments

One workaround to give users permissions on temporary tables that you don't want to give them on regular tables is the following. We just have to keep in mind that users have the same access rights on temporary tables that they have on all tables in a particular database:

1) create a dedicated database for temporary tables:

mysql> CREATE DATABASE tmp;

2) Give your users all the access privileges that they need to create and use temporary tables:

Your users have to explicitly call their temporary tables as tmp.<tablename> in all requests. There is no problem if two users use the same name for a temporary table since they will not be able to see each other's temporary tables. You can also put the 'tmp' database on a dedicated disk.

Note that although REFERENCES privilege is currently "unused", granting it on a table allows the user to query the information_schema database for column names etc. We found this useful for creating database documentation from the schema without needing to grant even SELECT privilege to the user (our wiki, in fact).