GNU Info

(mysql.info)Privileges provided

---

Next: Connecting Prev: Privileges Up: Privilege system

---

Enter node , (file) or (file)node

Privileges Provided by MySQL
----------------------------

Information about user privileges is stored in the `user', `db',
`host', `tables_priv', and `columns_priv' tables in the `mysql'
database (that is, in the database named `mysql').  The MySQL server
reads the contents of these tables when it starts up and under the
circumstances indicated in Note: Privilege changes.

The names used in this manual to refer to the privileges provided by
MySQL are shown below, along with the table column name associated with
each privilege in the grant tables and the context in which the
privilege applies:

*Privilege* *Column*           *Context*
*select*    `Select_priv'      tables
*insert*    `Insert_priv'      tables
*update*    `Update_priv'      tables
*delete*    `Delete_priv'      tables
*index*     `Index_priv'       tables
*alter*     `Alter_priv'       tables
*create*    `Create_priv'      databases, tables, or indexes
*drop*      `Drop_priv'        databases or tables
*grant*     `Grant_priv'       databases or tables
*references*`References_priv'  databases or tables
*reload*    `Reload_priv'      server administration
*shutdown*  `Shutdown_priv'    server administration
*process*   `Process_priv'     server administration
*file*      `File_priv'        file access on server

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.  You can execute certain `SELECT'
statements even without permission to access any of the databases on the
server.  For example, you could use the `mysql' client as a simple
calculator:

     mysql> SELECT 1+1;
     mysql> SELECT PI()*2;

The *index* privilege allows you to create or drop (remove) indexes.

The *alter* privilege allows you to use `ALTER TABLE'.

The *create* and *drop* privileges allow you to create new databases
and tables, or to drop (remove) existing databases and tables.

Note that 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 *grant* privilege allows you to give to other users those
privileges you yourself possess.

The *file* privilege gives you permission to read and write files on
the server using the `LOAD DATA INFILE' and `SELECT ... INTO OUTFILE'
statements.  Any user to whom this privilege is granted can read or
write any file that the MySQL server can read or write.

The remaining privileges are used for administrative operations, which
are performed using the `mysqladmin' program.  The table below shows
which `mysqladmin' commands each administrative privilege allows you to
execute:

*Privilege* *Commands permitted to privilege holders*
*reload*    `reload', `refresh', `flush-privileges', `flush-hosts',
            `flush-logs', and `flush-tables'
*shutdown*  `shutdown'
*process*   `processlist', `kill'

The `reload' command tells the server to re-read the grant tables.  The
`refresh' command flushes all tables and opens and closes the log
files.  `flush-privileges' is a synonym for `reload'.  The other
`flush-*' commands perform functions similar to `refresh' but are more
limited in scope, 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.

The `processlist' command displays information about the threads
executing within the server.  The `kill' command kills server threads.
You can always display or kill your own threads, but you need the
*process* privilege to display or kill threads initiated by other
users. Note: KILL.

It is a good idea in general to grant privileges only to those users
who need them, but you should exercise particular caution in granting
certain privileges:

   * The *grant* privilege allows users to give away 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 *file* privilege can be abused to read any world-readable file
     on the server into a database table, the contents of which can
     then be accessed using `SELECT'. This includes the contents of all
     databases hosted by the server!

   * 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
     passwords.

   * Privileges on the `mysql' database 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).  If they can access the `mysql.user' password
     column, they can use it to log into the MySQL server for the given
     user.  (With sufficient privileges, the same user can replace a
     password with a different one.)

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.