Mysql Grant Revoke Privileges
MySQL: Grant/Revoke Privileges
This tutorial explains how to grant and revoke privileges in MySQL with syntax and examples.
Description
You can GRANT and REVOKE privileges on various database objects in MySQL. You can then view the privileges assigned to a user using the SHOW GRANTS command. We'll look at how to grant and revoke privileges on tables, function, and procedures in MySQL.
Grant Privileges on Table
You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or ALL.
Syntax
The syntax for granting privileges on a table in MySQL is:
GRANT privileges ON object TO user;- privileges
- object
- user
It can be any of the following values:
| Privilege | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table. |
| INSERT | Ability to perform INSERT statements on the table. |
| UPDATE | Ability to perform UPDATE statements on the table. |
| DELETE | Ability to perform DELETE statements on the table. |
| INDEX | Ability to create an index on an existing table. |
| CREATE | Ability to perform CREATE TABLE statements. |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition. |
| DROP | Ability to perform DROP TABLE statements. |
| GRANT OPTION | Allows you to grant the privileges that you possess to other users. |
| ALL | Grants all permissions except GRANT OPTION. |
The name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.
The name of the user that will be granted these privileges.
Example
Let's look at some examples of how to grant privileges on tables in MySQL.
For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called contacts to a user name ao, you would run the following GRANT statement:
GRANT SELECT, INSERT, UPDATE, DELETE ON contacts TO 'ao'@'localhost';You can also use the ALL keyword to indicate that you wish to grant all permissions except GRANT OPTION to a user named ao. For example:
GRANT ALL ON contacts TO 'ao'@'localhost';If you wanted to grant only SELECT access on the contacts table to all users, you could grant the privileges to *. For example:
GRANT SELECT ON contacts TO '*'@'localhost';Revoke Privileges on Table
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.
Syntax
The syntax for revoking privileges on a table in MySQL is:
REVOKE privileges ON object FROM user;- privileges
- object
- user
It can be any of the following values:
| Privilege | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table. |
| INSERT | Ability to perform INSERT statements on the table. |
| UPDATE | Ability to perform UPDATE statements on the table. |
| DELETE | Ability to perform DELETE statements on the table. |
| INDEX | Ability to create an index on an existing table. |
| CREATE | Ability to perform CREATE TABLE statements. |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition. |
| DROP | Ability to perform DROP TABLE statements. |
| GRANT OPTION | Allows you to grant the privileges that you possess to other users. |
| ALL | Grants all permissions except GRANT OPTION. |
The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.
The name of the user that will have these privileges revoked.
Example
Let's look at some examples of how to revoke privileges on tables in MySQL.
For example, if you wanted to revoke DELETE and UPDATE privileges on a table called contacts from a user named ao, you would run the following REVOKE statement:
REVOKE DELETE, UPDATE ON contacts FROM 'ao'@'localhost';If you wanted to revoke all permissions (except GRANT OPTION) on a table for a user named ao, you could use the ALL keyword as follows:
REVOKE ALL ON contacts FROM 'ao'@'localhost';If you had granted SELECT privileges to * (ie: all users) on the contacts table and you wanted to revoke these privileges, you could run the following REVOKE statement:
REVOKE SELECT ON contacts FROM '*'@'localhost';Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures in MySQL.
Syntax
The syntax for granting EXECUTE privileges on a function/procedure in MySQL is:
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;- EXECUTE
- PROCEDURE
- FUNCTION
- object
- user
The ability to execute the function or procedure.
It is used when the privilege is being granted on a procedure in MySQL.
It is used when the privilege is being granted on a function in MySQL.
The name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.
The name of the user that will be granted the EXECUTE privileges.
Example - Function
Let's look at some examples of how to grant EXECUTE privileges on a function in MySQL.
For example, if you had a function called CalcIncome and you wanted to grant EXECUTE access to the user named ao, you would run the following GRANT statement:
GRANT EXECUTE ON FUNCTION CalcIncome TO 'ao'@'localhost';If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:
GRANT EXECUTE ON FUNCTION CalcIncome TO '*'@'localhost';Example - Procedure
Let's look at some examples of how to grant EXECUTE privileges on a procedure in MySQL.
For example, if you had a procedure called MyFirstProc and you wanted to grant EXECUTE access to the user named ao, you would run the following GRANT statement:
GRANT EXECUTE ON PROCEDURE MyFirstProc TO 'ao'@'localhost';If you wanted to grant ALL users the ability to EXECUTE this procedure, you would run the following GRANT statement:
GRANT EXECUTE ON PROCEDURE MyFirstProc TO '*'@'localhost';Revoke Privileges on Functions/Procedures
Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user in MySQL. To do this, you can execute a REVOKE command.
Syntax
The syntax for the revoking privileges on a function or procedure in MySQL is:
REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user;- EXECUTE
- PROCEDURE
- FUNCTION
- object
- user
The ability to execute the function or procedure is being revoked.
It is used when the privilege is being revoked on a procedure in MySQL.
It is used when the privilege is being revoked on a function in MySQL.
The name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.
The name of the user that will be revoked the EXECUTE privileges.
Example - Function
Let's look at some examples of how to revoke EXECUTE privileges on a function in MySQL.
If you wanted to revoke EXECUTE privileges on a function called CalcIncome from a user named ao, you would run the following REVOKE statement:
REVOKE EXECUTE ON FUNCTION CalcIncome FROM 'ao'@'localhost';If you had granted EXECUTE privileges to * (all users) on the function called CalcIncome and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:
REVOKE EXECUTE ON FUNCTION CalcIncome FROM '*'@'localhost';Example - Procedure
Let's look at some examples of how to revoke EXECUTE privileges on a procedure in MySQL.
If you wanted to revoke EXECUTE privileges on a procedure called MyFirstProc from a user named ao, you would run the following REVOKE statement:
REVOKE EXECUTE ON PROCEDURE MyFirstProc FROM 'ao'@'localhost';If you had granted EXECUTE privileges to * (all users) on the procedure called CalcIncome and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:
REVOKE EXECUTE ON PROCEDURE MyFirstProc FROM '*'@'localhost';