(mysql.info.gz) Privileges provided
(mysql.info.gz) Privilege system
5.5.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 Privilege changes. 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 `GRANT' GRANT.
*Privilege* *Column* *Context*
`CREATE' `Create_priv' databases, tables, or
`DROP' `Drop_priv' databases or tables
`GRANT' `Grant_priv' databases, tables, or
`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
`ALTER `Alter_routine_priv'stored routines
`CREATE `Create_routine_priv'stored routines
`EXECUTE' `Execute_priv' stored routines
`CREATE `Create_tmp_table_priv'server administration
`FILE' `File_priv' file access on server
`LOCK `Lock_tables_priv'server administration
`PROCESS' `Process_priv' server administration
`RELOAD' `Reload_priv' server administration
`SHOW `Show_db_priv' server administration
`SHUTDOWN' `Shutdown_priv'server administration
`SUPER' `Super_priv' server administration
The `CREATE TEMPORARY TABLES', `EXECUTE', `LOCK TABLES', `REPLICATION
CLIENT', `REPLICATION SLAVE', `SHOW DATABASES', and `SUPER' privileges
were added in MySQL 4.0.2. (`EXECUTE' is not operational until MySQL
5.0.3.) `CREATE VIEW' and `SHOW VIEW' were added in MySQL 5.0.1.
`CREATE ROUTINE' and `ALTER ROUTINE' were added in MySQL 5.0.3. To use
these privileges when upgrading from an earlier version of MySQL that
does not have them, , you must upgrade your grant tables.
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
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:
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
The `INDEX' privilege allows 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'
The `ALTER' privilege allows 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 `GRANT' privilege allows 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 allows the user to create new files in any
directory where the MySQL server has write access. Existing files
cannot be overwritten.
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 allows you to execute:
*Privilege* *Commands Permitted to Privilege Holders*
`RELOAD' `flush-hosts', `flush-logs', `flush-privileges',
`flush-status', `flush-tables', `flush-threads',
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. This command can be issued
only from `mysqladmin'. There is no corresponding SQL statement.
The `processlist' command displays information about the threads
executing within the server (that is, about the statements being
executed by clients associated with other accounts). 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.
`KILL' KILL. Prior to MySQL 4.0.2 when `SUPER' was introduced, the
`PROCESS' privilege controls the ability to both see and terminate
threads for other clients.
The `CREATE TEMPORARY TABLES' privilege allows the use of the keyword
`TEMPORARY' in `CREATE TABLE' statements.
The `LOCK TABLES' privilege allows 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 allows 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
It is a good idea in general 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 allows users to give their privileges to
other users. Two users with 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 queries, including queries that set or change
* 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
There are some things that you cannot do with the MySQL privilege
* 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.
(mysql.info.gz) Privilege system
automatically generated byinfo2html