Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




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, “GRANT Syntax”.

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
EVENT Event_priv databases
ALTER Alter_priv tables
DELETE Delete_priv tables
INDEX Index_priv tables
INSERT Insert_priv tables
SELECT Select_priv tables
UPDATE Update_priv tables
TRIGGER Trigger_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 TABLES Create_tmp_table_priv server administration
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 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:


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:

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 Section, “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 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.

  Published under the terms of the GNU General Public License Design by Interspire