MySQL | Grant / Revoke Privileges

In MySQL, managing user access is done with the GRANT statement to assign privileges and the REVOKE statement to remove them. After creating a user, these commands ensure they have only the permissions needed for their role.

• In MySQL, creating a user account using the CREATE USER statement is just the first step in user management.
• While this command establishes a new user, it does not assign any privileges.
• To control what actions a user can perform, the GRANT statement is essential for assigning specific privileges to user accounts.

Syntax:

GRANT privileges_names ON object TO user;

In the above syntax:

• privileges_name: These are the access rights or privileges granted to the user.
• object: It is the name of the database object to which permissions are being granted. In the case of granting privileges on a table, this would be the table name.
• user: It is the name of the user to whom the privileges would be granted.

Types of Privileges

Privileges: The privileges that can be granted to the users are listed below along with the description:

Granting Privileges to Users

1. To grant Select Privilege to a table named "users" where User Name is Amit, the following GRANT statement should be executed.
2. The general syntax of specifying a username is: 'user_name'@'address'.
3. If the user 'Amit' is on the local host then we have to mention it as 'Amit'@'localhost'. Or suppose if the 'Amit' username is on 192.168.1.100 IP address then we have to mention it as 'Amit'@'192.168.1.100'.
4. 'user_name'@'address' - When you're granting or revoking permissions in MySQL, you use the 'username' or 'hostname' format to tell which users are allowed or denied. This is important for keeping security and access control in place, so here's why we use it:

1. Granting SELECT Privilege to a User

To grant the SELECT privilege on a table named "users" to a user named "Amit", use the following command:

GRANT SELECT ON Users TO 'Amit'@'localhost;'

2. Granting Multiple Privileges

To grant multiple privileges, such as SELECT, INSERT, DELETE, and UPDATE to the user "Amit",

GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Amit'@'localhost';

3. Granting All Privileges

To grant all privileges on the "users" table to "Amit", use:

GRANT ALL ON Users TO 'Amit'@'localhost';

4. Granting Privileges to All Users

To grant a specific privilege (e.g., SELECT) to all users on the "users" table, execute:

GRANT SELECT ON Users TO '*'@'localhost';

In the above example the "*" symbol is used to grant select permission to all the users of the table "users".

5. Granting Privileges on Functions/Procedures

While using functions and procedures, the Grant statement can be used to grant users the ability to execute the functions and procedures in MySQL.

GRANT EXECUTE ON FUNCTION function_name TO 'Amit'@'localhost';

Example for granting EXECUTE privilege on a function named "CalculateSalary":

GRANT EXECUTE ON FUNCTION CalculateSalary TO 'Amit'@'localhost';

Granting Execute Privilege: Execute privilege gives the ability to execute a function or procedure.Syntax:

GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user; 

Different Ways of Granting EXECUTE Privileges

1. Granting EXECUTE privileges on a function in MySQL

If there is a function named "CalculateSalary" and you want to grant EXECUTE access to the user named Amit, then the following GRANT statement should be executed.

GRANT EXECUTE ON FUNCTION Calculatesalary TO 'Amit'@'localhost';

2. Granting EXECUTE privileges to all Users on a function in MySQL

If there is a function named "CalculateSalary" and you want to grant EXECUTE access to all the users, then the following GRANT statement should be executed.

GRANT EXECUTE ON FUNCTION Calculatesalary TO '*'@'localhost'; 

3. Granting EXECUTE privilege to a Users on a procedure in MySQL

If there is a procedure named "DBMSProcedure" and you want to grant EXECUTE access to the user named Amit, then the following GRANT statement should be executed.

GRANT EXECUTE ON PROCEDURE DBMSProcedure TO 'Amit'@'localhost'; 

4. Granting EXECUTE privileges to all Users on a procedure in MySQL

If there is a procedure called "DBMSProcedure" and you want to grant EXECUTE access to all the users, then the following GRANT statement should be executed. 

GRANT EXECUTE ON PROCEDURE DBMSProcedure TO '*'@'localhost'; 

5. Checking the Privileges Granted to a User

To see the privileges granted to a user in a table, the SHOW GRANTS statement is used. To check the privileges granted to a user named "Amit" and host as "localhost", the following SHOW GRANTS statement will be executed:

SHOW GRANTS FOR 'Amit'@'localhost'; 

Output:

GRANTS FOR Amit@localhost 
GRANT USAGE ON *.* TO `SUPER`@`localhost` 

Revoking Privileges from Users

The Revoke statement is used to revoke some or all of the privileges which have been granted to a user in the past.

Syntax:

REVOKE privileges ON object FROM user;

Parameters Used:

• object: It is the name of the database object from which permissions are being revoked. In the case of revoking privileges from a table, this would be the table name.
• user: It is the name of the user from whom the privileges are being revoked.

Privileges can be of the following values

Various Ways of Revoking Privileges From a User

Below are the different ways of revoking privileges from a user in MySQL.

1. Revoking SELECT Privilege

To revoke Select Privilege to a table named "users" where User Name is Amit, the following revoke statement should be executed.

REVOKE SELECT ON Users FROM 'Amit'@'localhost'; 

2. Revoking Multiple Privileges

To revoke multiple Privileges to a user named "Amit" in a table "users", the following revoke statement should be executed.

REVOKE SELECT, INSERT, DELETE, UPDATE ON Users FROM 'Amit'@'localhost'; 

3. Revoking All Privileges

To revoke all the privileges to a user named "Amit" in a table "users", the following revoke statement should be executed.

REVOKE ALL ON Users FROM 'Amit'@'localhost'; 

Revoking Privileges on Functions/Procedures

While using functions and procedures, the revoke statement can be used to revoke the privileges from users which have been EXECUTE privileges in the past.

Syntax:

REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM User; 

1. Revoking EXECUTE Privileges on a Function in MySQL

If there is a function called "CalculateSalary" and you want to revoke EXECUTE access to the user named Amit, then the following revoke statement should be executed.

REVOKE EXECUTE ON FUNCTION Calculatesalary FROM 'Amit'@'localhost'; 

2. Revoking EXECUTE Privileges to All Users on a Function in MySQL

If there is a function called "CalculateSalary" and you want to revoke EXECUTE access to all the users, then the following revoke statement should be executed.

REVOKE EXECUTE ON FUNCTION Calculatesalary FROM '*'@'localhost'; 

3. Revoking EXECUTE Privilege to a Users on a Procedure in MySQL

If there is a procedure called "DBMSProcedure" and you want to revoke EXECUTE access to the user named Amit, then the following revoke statement should be executed.

REVOKE EXECUTE ON PROCEDURE DBMSProcedure FROM 'Amit'@'localhost'; 

4. Revoking EXECUTE Privileges to all Users on a Procedure in MySQL

If there is a procedure called "DBMSProcedure" and you want to revoke EXECUTE access to all the users, then the following revoke statement should be executed.

REVOKE EXECUTE ON PROCEDURE DBMSProcedure FROM '*'@'localhost';