4.2.7 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 section 4.3.3 When Privilege Changes Take Effect.

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

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 any world readable file accessable by the MySQL server and create a new world readable file in any directory where the MySQL server can write. The user can also read any file in the current database directory. The user can however not change any existing file.

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

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 and SUPER privilege to kill threads initiated by other users. See section 4.5.6 KILL Syntax.

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 or any file in the current database directory on the server into a database table, the contents of which can then be accessed using SELECT.
• 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.

User Comments

I'm running into the situation where it would be
really nice for a connection to only have the
ability to SELECT and create/drop/insert/delete
TEMPORARY tables without having the privileges to
do this globally with all objects. I didn't see
this option listed in any current or upcoming
release. Can this function be implemented in
some sooner upcoming release? I think others
might find it useful as well.

Note that: "To use ALTER TABLE, you need ALTER,
INSERT, and CREATE privileges on the table." http://www.mysql.com/doc/en/ALTER_TABLE.html

I believe the CREATE TEMPORARY TABLES privilege would be what you need to handle temporary tables without having global privileges.

Your documentation says,
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.

But this is the Most important need i guess. Will MySQL team look for this ?

Add your own comment.