MySQL Permissions Tutorial

MySQL provides permissions (privileges) based on the level of operation of the user. Administrative privileges manage the operation of the server. Database privileges are specific to the databases and the objects with in them. Object privileges apply to tables, indexes, views and stored routines within the database. These privileges are given to a user with the GRANT command and revoked with the REVOKE command.

The Privileges

The MySQL Reference manual has a complete list of available privileges. A few of the more common privileges are:

The ALL privilege grants a user the entire set of privileges available at a given privilege level. At the global level, the user has the complete set of privileges available. At the table level, the user has only the table level privileges.

The CREATE privilege allows the user to create new databases and tables.

The DROP privilege allows the user to remove existing databases and tables.

The EXECUTE privilege allows a user to execute stored routines.

The INSERT privilege allows the user to insert records or data into a table.

The SELECT privilege allows the user to use the SELECT statement to retrieve data from tables.

The SHOW DATABASE privilege allows the user to see the database names with the SHOW DATABASE statement.

The SHUTDOWN privilege allows the user to shutdown the database with the "mysqladmin shutdown" command.

The GRANT Command

To use the GRANT command, you must have the privileges you want to give to another user and must have the GRANT OPTION privilege. A typical GRANT statement is:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;

This statement says to give "ALL PRIVILEGES" at the global level to the user "mysql" on all hosts with the "GRANT OPTION."

If you want to the user to only have privileges on a single database, you will use the "database.*" syntax, where "database" is the name of the database. For example:

GRANT SELECT, INSERT on database.* TO 'user'@'hostname';

To grant only table privileges, specify the table with the "database.table" syntax, such as:

GRANT ALL ON database.table TO 'user'@'hostname';

The REVOKE command

Like the GRANT command, you must have the privileges you want to revoke from the user along with the GRANT OPTION privilege. To revoke all privileges from a user the syntax would be:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user';

Other REVOKE statements have the same syntax as the GRANT statement. For example, to remove the INSERT privileges on one database for a user, the statement would be:

REVOKE INSERT ON database.* FROM 'user';

You can check that privileges have been granted or revoked correctly with the "SHOW GRANTS FOR 'user';" statement.

References

• MySQL Documentation

Resources

• Privileges Provided by MySQL