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
user
, db
,
host
, tables_priv
, and
columns_priv
tables in the
mysql
database (see
Section 5.5.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 5.5.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 access privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System 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.
Privilege | Column | Context |
CREATE |
Create_priv |
databases, tables, or indexes |
DROP |
Drop_priv |
databases or tables |
GRANT OPTION |
Grant_priv |
databases, tables, or stored routines |
REFERENCES |
References_priv |
databases or tables |
ALTER |
Alter_priv |
tables |
DELETE |
Delete_priv |
tables |
INDEX |
Index_priv |
tables |
INSERT |
Insert_priv |
tables |
SELECT |
Select_priv |
tables |
UPDATE |
Update_priv |
tables |
CREATE TEMPORARY TABLES |
Create_tmp_table_priv |
tables |
LOCK TABLES |
Lock_tables_priv |
tables |
FILE |
File_priv |
file access on server host |
PROCESS |
Process_priv |
server administration |
RELOAD |
Reload_priv |
server administration |
REPLICATION CLIENT |
Repl_client_priv |
server administration |
REPLICATION SLAVE |
Repl_slave_priv |
server administration |
SHOW DATABASES |
Show_db_priv |
server administration |
SHUTDOWN |
Shutdown_priv |
server administration |
SUPER |
Super_priv |
server administration |
ALL [PRIVILEGES] |
server administration | |
USAGE |
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.
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.
The ALTER
privilege enables use
of ALTER TABLE
to change the
structure of or rename tables. (ALTER
TABLE
also requires the
INSERT
and
CREATE
privileges.)
The CREATE
privilege enables
creation of new databases and tables.
The CREATE TEMPORARY TABLES
privilege enables the use of the keyword
TEMPORARY
in CREATE
TABLE
statements. This privilege was added in MySQL
4.0.2.
The DELETE
privilege enables
rows to be deleted from tables in a database.
The DROP
privilege enables you
to drop (remove) existing databases and tables. 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 EXECUTE
privilege was added
in MySQL 4.0.2, but is not used until MySQL 5.0.
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. 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 INSERT
privilege enables
rows to be inserted into tables in a database.
INSERT
is also required for the
ANALYZE TABLE
,
OPTIMIZE TABLE
, and
REPAIR TABLE
table-maintenance
statements.
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. This privilege was added in
MySQL 4.0.2.
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. Prior to MySQL 4.0.2,
PROCESS
also enable the use of
KILL
to kill threads belonging
to other accounts; you can always kill your own threads.
The REFERENCES
privilege
currently is unused.
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-
commands perform functions similar to
xxx
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 CLIENT
privilege enables the use of SHOW MASTER
STATUS
and SHOW SLAVE
STATUS
. This privilege was added in MySQL 4.0.2.
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. This privilege was
added in MySQL 4.0.2.
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. SHOW
DATABASES
was added in MySQL 4.0.2.
MySQL Enterprise.
The SHOW DATABASES
privilege
should be granted only to users who need to see all the
databases on a MySQL server. Subscribers to the MySQL
Enterprise Monitor are alerted when servers are started
without the
--skip-show-database
option.
For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
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 via
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,
and allows you to connect (once) even if the connection limit
controlled by the
max_connections
system
variable is reached. This privilege was added in MySQL 4.0.2.
Prior to MySQL 4.0.2, the
PROCESS
privilege controls the
ability to terminate threads for other accounts.
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.
MySQL Enterprise. Accounts with unnecessary global privileges constitute a security risk. Subscribers to the MySQL Enterprise Monitor are automatically alerted to the existence of such accounts. For detailed information, see http://www.mysql.com/products/enterprise/advisors.html.
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:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE TEMPORARY TABLES ON tmp.* TO user@localhost;
Be sure that you do not give them CREATE or GRANT privileges!
3) Have you users create all temporary tables in that 'tmp' database instead of the current database:
mysql> USE mydb
mysql> CREATE TEMPORARY TABLE tmp.dummy SELECT * from mytable;
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.
One side-effect of priv_super is that users with priv_super are allowed to write to the database, regardless of the setting of the read_only global variables.
Add your own comment.