Updated May 17, 2023
Introduction to MySQL REVOKE
MySQL users utilize the ‘REVOKE’ function to revoke all or specific access privileges from a user. You can use this function to revoke a specific access or multiple/all accesses simultaneously in a single query. It can remove the privileges from different database objects like an entire database, a table, or at the column level. To use the ‘REVOKE’ function, a user must have the ‘CREATE USER’ or ‘UPDATE’ privileges. This function takes away the access privileges from a user, keeping the user account safe at MySQL.user system table.
The output of the REVOKE query is always a message that says ‘Query OK. 0 row(s) affected’.
Before discussing further the REVOKE function, it would be desirable to have a strong understanding of the GRANT function and the scope and privileges of accesses in MySQL.
Syntax:
The ‘REVOKE’ function has three levels, and the syntax goes as below:
REVOKE privilege_type[column_name]
ON [obj_type] privilege_level
FROM user1 [,user2]…;REVOKE ALL [privileges],
GRANT OPTION
FROM user1 [,user2]…;REVOKE PROXY
ON user
FROM user1 [,user2]…;Explanation:
- obj_type: This is the database object from which the access privilege is revoked.
- privilege_type: Is the type of privilege being revoked.
- user: Is the entity from whom the privilege is revoked.
How Does REVOKE Function Work in MySQL?
When we discuss the REVOKE access function, we need a user with accesses we wish to revoke. So let’s first create a user and grant some access privileges.
Query:
CREATE USER abraham
IDENTIFIED BY 'marklist';The new user created is ‘abraham’, and the scope is table ‘marklist’.
Output:
Query:
We have granted the new user ‘all’ access privileges for the database’ world’.
SHOW GRANTS FOR abraham;Output:
So the new user has all the privileges to proceed. Let’s now look at different scopes to revoke the same.
Query:
Let’s remove the update access of the user ‘abraham’.
REVOKE UPDATE ON marklist FROM abraham;Initially, the user ‘Abraham’ had the privilege to update any row or column in the table ‘marklist’. But the query has removed it.
Output:
Executing the SHOW GRANTS query after the REVOKE query displays the difference in privileges the user holds before and after executing the REVOKE function.
Query:
To revoke all privileges from a user, we can use the REVOKE ALL syntax. Ensure you are not revoking the GRANT option. Otherwise, you will not be able to grant privileges back if needed.
EVOKE ALL, GRANT OPTION FROM abraham;Output:
Executing the SHOW GRANTS query after the REVOKE query displays the difference in privileges the user holds. Now all privileges are removed from the user. The user can log in to the database but has no other privileges.
Explanation:
When granting a user proxy privilege, a special revoke statement is used to revoke this privilege. For now, consider the user ‘abraham’ as the proxy access as the ‘alina’ user. For this, let’s see what privileges the user ‘alina’ holds and grant the same to new user ‘abraham’.
Query:
SHOW GRANTS FOR alina;Output:
User ‘alina’ holds all privileges except ‘UPDATE’ and ‘ALTER’. And our user ‘abraham’ holds no privileges, as we checked in the last query.
Query:
Let’s grant all privileges that user ‘alina’ hold t user ‘abraham’.
GRANT PROXY
ON alina
TO abraham;Output:
This will grant all privileges to Abraham except UPDATE and ALTER since ‘alina’ didn’t have these privileges. Let’s confirm this by executing SHOW GRANTS to Abraham.
Query:
If we run the REVOKE PROXY query on Abraham now, it will remove all the proxy privileges from Abraham.
REVOKE PROXY
ON alina
FROM abraham;Output:
The query removes those privileges entitled upon user ‘abraham’ as proxy from user ‘alina’.
Execute the SHOW GRANTS on ‘abraham’.
SHOW GRANTS;
The user lost all privileges and has only login access now.
Explanation: Revoking ‘EXECUTE’ privileges on functions or procedures. The ‘REVOKE’ command can remove the execute privileges on a function or procedure.
For example, we can consider the ‘Addition’ function and user ‘abraham’. To grant the ‘EXECUTE’ privilege to this user, use the below query:
Query:
GRANT EXECUTE ON FUNCTION Addition TO abraham;The query grants user ‘abraham’ permission to execute the Addition function.
Output:
The EXECUTE privilege is granted to the user ‘abraham’ for the function Addition.
The SHOW GRANTS query is returned with the user ‘abraham’ privileges list.
SHOW GRANTS;
Now, let’s revoke the EXECUTE privilege from this user by the REVOKE function.
Query:
REVOKE EXECUTE ON FUNCTION Addition FROM abraham;Output:
In this query, the EXECUTE privilege has been revoked for the user ‘abraham’. Let’s try the SHOW GRANTS query to check the same.
SHOW GRANTS;
The important point is that the revoke function works only for stored functions or procedures in MySQL.
We shall also see an example of EXECUTE privilege revoking on the procedure. Consider the table world.city and procedure defined ‘CountOfCities’. To grant EXECUTE privilege for this procedure to user ‘abraham’, use the below query,
Query:
GRANT EXECUTE ON PROCEDURE CountOfCities TO abraham;The query grants execute privileges to our users on the procedure CounOfCities.
Output:
Let’s run the SHOW GRANTS query to check the privileges assigned to the user ‘abraham’.
SHOW GRANTS;
Now, we can revoke the privilege from the user using the below query.
Query:
REVOKE EXECUTE ON PROCEDURE CountOfCities FROM abraham;This query revokes the EXECUTE privilege from user ‘abraham’.
Output:
To verify if the privilege is revoked for the user ‘abraham’, execute the SHOW GRANTS query for that user.
SHOW GRANTS;
The privilege has been successfully revoked.
Thus we saw examples of revoking privileges from a particular user at different levels.
Conclusion
As discussed, users utilize the REVOKE function to retract any previously granted privileges for a specific user or a list of users. This function retains the user details row in the system table but removes all access privileges from the user. The REVOKE function returns constant output if executed successfully. The REVOKE function has three levels: individual privileges, revoking ALL privileges at once, and revoking the proxy privileges entitled to any user. The same function can remove EXECUTE privilege from any stored function or procedure.
Recommended Article
We hope that this EDUCBA information on “MySQL REVOKE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
