MySQL Privileges

The user privilege system built into MySQL dictates who can do what within each particular database. The mysql database stores the specifics in terms of users, passwords, hosts, and database access. It also records what individual, allowable actions particular users can doin other words, what SQL commands they can run on the database. As newer features are added to MySQL, such as stored procedures and views, those permissions are added to the mysql database as well.

Tables B.4, B.5, and B.6 list the available privileges. I've grouped them into my own rough categories: basic, administrative, and new. These are entirely artificial distinctions of my own making but should help give you a sense of how to assign privileges based upon the version of MySQL you are using and what a user needs to be able to do. As a rule, always give each user the minimum required privileges on a database.

Table B.4. This is the list of basic privileges that can be assigned to MySQL users on a case-by-case basis. It is generally safe to assign these to users for a specific database (except for the mysql database, which must be kept off-limits).

Basic MySQL Privileges
Privilege	Allows For
SELECT	Reading of rows from tables
INSERT	Adding new rows of data to tables
UPDATE	Altering existing data in tables
DELETE	Removing existing data in tables
SHOW DATABASES	Listing the available databases
INDEX	Creating and dropping indexes in tables
ALTER	Modifying the structure or properties of a table
CREATE	Creating new tables or databases
CREATE TEMPORARY TABLES	Creating temporary tables
DROP	Deleting existing tables or databases

Table B.5. These privileges should be given only to administrative users and preferably on specific databases.

Administrative MySQL Privileges
Privilege	Allows For
RELOAD	Reloading the grant tables (and therefore enact user changes)
SHUTDOWN	Stopping the MySQL server
FILE	Importing data into tables from text files
GRANT OPTION	Creating new users with the same permissions as current user
CREATE USER	Creating new users
REVOKE	Removing the permissions of users
PROCESS	Showing currently running processes
SUPER	Terminating running processes

Table B.6. These privileges have been added in newer versions of MySQL (mostly 5.0 and up). Note that it's safer and normal for some users to be able to use a view or run a stored procedure, whereas limited users can create or modify them.

Newer MySQL Privileges
Privilege	Allows For
CREATE VIEW	Creating a view
SHOW VIEW	Using a view
ALTER ROUTINE	Modifying a stored procedure
CREATE ROUTINE	Creating a stored procedure
EXECUTE	Running a stored procedure
LOCK TABLES	Locking tables
REPLICATION CLIENT	Showing replication status
REPLICATION SLAVE	Performing replication