5.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).
Information about account privileges is stored in the
user
, db
, host
, tables_priv
, columns_priv
, and procs_priv
tables in the mysql
database (see Section 5.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 5.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 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.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.
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.
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.)
MySQL Enterprise. In some circumstances,
the ALTER
privilege is entirely unnecessary — on slaves where there are no nonreplicated tables, for instance. The MySQL Enterprise Monitor notifies subscribers when accounts have inappropriate privileges. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
The
ALTER ROUTINE
privilege is needed to alter or drop stored routines (procedures and functions).
The CREATE
privilege enables creation of new databases and tables.
The CREATE ROUTINE
privilege is needed to create stored routines (procedures and functions).
The CREATE TEMPORARY TABLES
privilege enables the use of the keyword TEMPORARY
in
CREATE
TABLE
statements.
The CREATE USER
privilege enables use of
CREATE USER
, DROP USER
, RENAME USER
, and
REVOKE ALL
PRIVILEGES
.
The CREATE VIEW
privilege enables use of CREATE VIEW
.
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. Beginning with
MySQL 5.1.10, the DROP
privilege is also required in order to use the statement ALTER TABLE ... DROP
PARTITION
on a partitioned table. Beginning with MySQL 5.1.16, the DROP
privilege is required for
TRUNCATE
TABLE
(before that, TRUNCATE
TABLE
requires the DELETE
privilege). 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, or drop events for the
Event Scheduler. This privilege was added in MySQL 5.1.6.
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. 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.
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 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-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 CLIENT
privilege enables the use of SHOW MASTER
STATUS
and SHOW SLAVE
STATUS
.
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.
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
SHOW VIEW
privilege enables use of SHOW CREATE VIEW
.
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.
To create or alter stored functions if binary logging is enabled, you may also need the SUPER
privilege, as
described in Section 19.6, “Binary Logging of Stored Programs”.
The TRIGGER
privilege enables you to create and drop triggers. You must have this privilege for a table
to create or drop triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the SUPER
privilege.)
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.
What are the privileges in MySQL?
Privilege Descriptions.
ALL , ALL PRIVILEGES. These privilege specifiers are shorthand for “all privileges available at a given privilege level” (except GRANT OPTION ). ... .
ALTER. ... .
ALTER ROUTINE. ... .
CREATE. ... .
CREATE ROUTINE. ... .
CREATE TABLESPACE. ... .
CREATE TEMPORARY TABLES. ... .
CREATE USER..
What are the four privilege levels in MySQL?
Figure 9-1 shows an example of the MySQL privilege hierarchy. There are four levels; reading from highest to lowest, these are global, database, table, and column.
How many privileges exist in MySQL?
Privilege levels in MySQL
There are six privilege levels used for granting privileges to the user: global, database, table, column, stored procedure or function, and proxy, as shown in the below image.
What are SQL privileges?
A privilege is a right to execute a particular type of SQL statement or to access another user's object. Some examples of privileges include the right to: Connect to the database (create a session) Create a table. Select rows from another user's table.