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).
Information about account privileges is stored in the
procs_priv tables in the
mysql database (see
Section 4.2, “Privilege System Grant Tables”). The MySQL server reads
the contents of these tables into memory when it starts and
reloads them under the circumstances indicated in
Section 4.6, “When Privilege Changes Take Effect”. Access-control decisions are
based on the in-memory copies of the grant tables.
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
REVOKE statements, along with the
column name associated with each privilege in the grant tables and
the context in which the privilege applies.
Table 4.1 Permissible Privileges for
|databases, tables, or indexes|
|databases, tables, or views|
|databases, tables, or stored routines|
|databases or tables|
|tables or columns|
|tables or columns|
|tables or columns|
|file access on server host|
|see ||server administration|
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.
privilege specifier is shorthand. It stands for “all
privileges available at a given privilege level”
GRANT OPTION). For
ALL at the
global or table level grants all global privileges or all
ALTER privilege enables use
ALTER TABLE to change the
structure of tables.
TABLE also requires the
INSERT privileges. Renaming a
DROP on the old table,
INSERT on the new table.
ALTER ROUTINE privilege is
needed to alter or drop stored routines (procedures and
CREATE privilege enables
creation of new databases and tables.
CREATE ROUTINE privilege is
needed to create stored routines (procedures and functions).
CREATE TABLESPACE privilege
is needed to create, alter, or drop tablespaces and log file
After a session has created a temporary table, the server
performs no further privilege checks on the table. The
creating session can perform any operation on the table, such
One implication of this behavior is that a session can
manipulate its temporary tables even if the current user has
no privilege to create them. Suppose that the current user
does not have the
TABLES privilege but is able to execute a
DEFINER-context stored procedure that
executes with the privileges of a user who does have
CREATE TEMPORARY TABLES and
that creates a temporary table. While the procedure executes,
the session uses the privileges of the defining user. After
the procedure returns, the effective privileges revert to
those of the current user, which can still see the temporary
table and perform any operation on it.
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.
DELETE privilege enables
rows to be deleted from tables in a database.
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
If you grant the
privilege for the
mysql database to a user,
that user can drop the database in which the MySQL access
privileges are stored.
EVENT privilege is required
to create, alter, drop, or see events for the Event Scheduler.
EXECUTE privilege is
required to execute stored routines (procedures and
FILE privilege gives you
permission to read and write files on the server host using
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
GRANT OPTION privilege
enables you to give to other users or remove from other users
those privileges that you yourself possess.
INDEX privilege enables you
to create or drop (remove) indexes.
INDEX applies to existing
tables. If you have the
privilege for a table, you can include index definitions in
CREATE TABLE statement.
LOCK TABLES privilege
enables the use of explicit
TABLES statements to lock tables for which you have
SELECT privilege. This
includes the use of write locks, which prevents other sessions
from reading the locked table.
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
also enables use of
currently is unused.
RELOAD privilege enables
use of the
FLUSH statement. It
also enables mysqladmin commands that are
reload command tells the server to
reload the grant tables into memory.
flush-privileges is a synonym for
command closes and reopens the log files and flushes all
tables. The other
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
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.
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;
SELECT privilege is also
needed for other statements that read column values. For
SELECT is needed for
columns referenced on the right hand side of
or for columns named in the
WHERE clause of
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
Note that any global privilege is a
privilege for the database.
SHUTDOWN privilege enables
use of the mysqladmin shutdown command.
There is no corresponding SQL statement.
SUPER privilege enables an
account to use
mysqladmin kill to kill threads belonging
to other accounts (you can always kill your own threads),
PURGE BINARY LOGS,
configuration changes using
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
attribute of stored programs and views, and enables you to
connect (once) even if the connection limit controlled by the
variable is reached.
TRIGGER privilege enables
trigger operations. You must have this privilege for a table
to create, drop, or execute triggers for that table.
UPDATE privilege enables
rows to be updated in tables in a database.
USAGE privilege specifier
stands for “no privileges.” It is used at the
global level with
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
FILE and administrative
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.
ALTER privilege may be used
to subvert the privilege system by renaming tables.
SHUTDOWN privilege can be
abused to deny service to other users entirely by terminating
PROCESS privilege can be
used to view the plain text of currently executing statements,
including statements that set or change passwords.
SUPER privilege can be used
to terminate other sessions or change how the server operates.
Privileges granted for the
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
column can change an account's password, and then connect to
the MySQL server using that account.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices