(mysql.info) privileges-provided
Info Catalog
(mysql.info) privileges
(mysql.info) privilege-system
(mysql.info) connecting
5.8.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.
*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 VIEW' `Create_view_priv' views
`SHOW VIEW' `Show_view_priv' views
`ALTER ROUTINE' `Alter_routine_priv' stored routines
`CREATE ROUTINE' `Create_routine_priv' stored routines
`EXECUTE' `Execute_priv' stored routines
`FILE' `File_priv' file access on server host
`CREATE TEMPORARY `Create_tmp_table_priv' server administration
TABLES'
`LOCK TABLES' `Lock_tables_priv' server administration
`CREATE USER' `Create_user_priv' server administration
`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
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 mysql-upgrade.
`CREATE VIEW' and `SHOW VIEW' were added in MySQL 5.0.1. `CREATE USER',
`CREATE ROUTINE', and `ALTER ROUTINE' were added in MySQL 5.0.3.
Although `EXECUTE' was present in MySQL 5.0.0, it did not become
operational until MySQL 5.0.3.
To create or alter stored routines if binary logging is enabled, you
may also need the `SUPER' privilege, as described in
stored-procedure-logging.
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 `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:
*Privilege* *Commands Permitted to Privilege Holders*
`RELOAD' `flush-hosts', `flush-logs', `flush-privileges',
`flush-status', `flush-tables', `flush-threads',
`refresh', `reload'
`SHUTDOWN' `shutdown'
`PROCESS' `processlist'
`SUPER' `kill'
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 kill.
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.
Info Catalog
(mysql.info) privileges
(mysql.info) privilege-system
(mysql.info) connecting
automatically generated byinfo2html