5.7.3. Privileges Provided by MySQL
Information about account privileges is stored in the
user, db,
host, tables_priv,
columns_priv, and
procs_priv tables in the
mysql database. The MySQL server reads the
contents of these tables into memory when it starts and re-reads
them under the circumstances indicated in
Section 5.7.7, “When Privilege Changes Take Effect”. Access-control decisions
are based on the in-memory copies of the grant tables.
The names used in the GRANT and
REVOKE statements to refer to privileges are
shown in the following table, along with the column name
associated with each privilege in the grant tables and the
context in which the privilege applies. Further information
about the meaning of each privilege may be found at
Section 13.5.1.3, “GRANT Syntax”.
Some releases of MySQL introduce changes to the structure of the
grant tables to add new 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 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
The EVENT and TRIGGER
privileges were added in MySQL 5.1.6.
To create or alter stored functions if binary logging is
enabled, you may also need the SUPER
privilege, as described in
Section 19.4, “Binary Logging of Stored Routines and Triggers”.
The CREATE and DROP
privileges allow you to create new databases and tables, or 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 SELECT, INSERT,
UPDATE, and DELETE
privileges allow you to perform operations on rows in existing
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 the
mysql client as a simple calculator to
evaluate expressions that make no reference to tables:
SELECT 1+1;
SELECT PI()*2;
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 ALTER privilege enables you to use
ALTER TABLE to change the structure of or
rename tables.
The CREATE ROUTINE privilege is needed for
creating stored routines (functions and procedures).
ALTER ROUTINE privilege is needed for
altering or dropping stored routines, and
EXECUTE is needed for executing stored
routines.
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. (Prior to MySQL 5.1.6,
these operations required the SUPER
privilege.)
The EVENT privilege enables you to set up
events for the event scheduler.
The GRANT privilege enables you to give to
other users those privileges that you yourself possess. It can
be used for databases, tables, and stored routines.
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. 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 remaining privileges are used for administrative operations.
Many of them can be performed by using the
mysqladmin program or by issuing SQL
statements. The following table shows which
mysqladmin commands each administrative
privilege enables you to execute:
The reload command tells the server to
re-read 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 shutdown command shuts down the server.
There is no corresponding SQL statement.
The processlist command displays information
about the threads executing within the server (that is,
information about the statements being executed by clients). The
kill command terminates server threads. You
can always display or kill your own threads, but you need the
PROCESS privilege to display threads
initiated by other users and the SUPER
privilege to kill them. See Section 13.5.5.3, “KILL Syntax”.
The CREATE TEMPORARY TABLES privilege enables
the use of the keyword TEMPORARY in
CREATE TABLE 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
anyone else from reading the locked table.
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 SHOW DATABASES privilege allows 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.
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 privilege enables users to give
their privileges to other users. Two users that have
different privileges and with the GRANT
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 clients 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.
There are some things that you cannot do with the MySQL
privilege system:
You cannot explicitly specify that a given user should be
denied access. That is, you cannot explicitly match a user
and then refuse the connection.
You cannot specify that a user has privileges to create or
drop tables in a database but not to create or drop the
database itself.
A password applies globally to an account. You cannot
associate a password with a specific object such as a
database, table, or routine.
