Introduction to MySQL REVOKE
The ‘REVOKE’ function is used in MySQL to revoke all or specified access privileges from a user. This function can be used to revoke one particular access and multiple or all accesses at once, in a single query. It can remove the privileges from different database objects like an entire database, a table or at the column level. A user must have the ‘CREATE USER’ or ‘UPDATE’ privileges, as required, in order to use the ‘REVOKE’ function. This function simply takes away the access privileges from a user, keeping the user account safe at the 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 on 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: 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 to have a user with accesses that 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 on different scopes to revoke the same.
4.5 (2,013 ratings)
View Course
Query:
Let’s remove the update access of the user ‘abraham’.
REVOKE UPDATE ON marklist FROM abraham;
So initially the user ‘Abraham’ held 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 held by the user before and after the REVOKE function is executed.
Query:
To revoke all privileges from a user, we can use the REVOKE ALL syntax. Make sure you are not revoking the GRANT option, otherwise you will not be able to grant privileges back if needed.
REVOKE ALL, GRANT OPTION FROM abraham;
Output:
Executing the SHOW GRANTS query after the REVOKE query displays the difference in privileges held by the user. Now all privileges are removed from the user. User can just login to the database but has no other privileges.
Explanation:
When a user is granted with proxy privilege, a special revoke statement is used to take back this privilege. For now, consider the user ‘abraham’ has the proxy accesses as the ‘alina’ user. For this, let’s see what all privileges the user ‘alina’ holds and also grant the same to new user ‘abraham’.
Query:
SHOW GRANTS FOR alina;
Output:
User ‘alina’ hold all privileges except ‘UPDATE’ and ‘ALTER’ privileges. 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 privileges 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 which were 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 be used to take back the execute privileges on a function or procedure.
As an 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 permission to user ‘abraham’ to execute the function Addition.
Output:
The EXECUTE privilege is granted to user ‘abraham’ for function Addition.
The SHOW GRANTS query is returned with the list of privileges for user ‘abraham’.
SHOW GRANTS;
Now, let’s revoke the EXECUTE privilege from this user by REVOKE function.
Query:
REVOKE EXECUTE ON FUNCTION Addition FROM abraham;
Output:
The EXECUTE privilege is revoked for user ‘abraham’ in this query. Let’s try the SHOW GRANTS query to check the same.
SHOW GRANTS;
An important point to note here is that, the revoke function works only for stored functions or procedures in MySQL.
We shall see an example for EXECUTE privilege revoking on the procedure as well. Consider the table world.city and procedure defined ‘CountOfCities’. To grant EXECUTE privilege for this procedure to user ‘abraham’, use below query,
Query:
GRANT EXECUTE ON PROCEDURE CountOfCities TO abraham;
The query grants execute privilege 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:
We can check the privilege is revoked or not by running the SHOW GRANTS query for user ‘abraham’.
SHOW GRANTS;
So the privilege is revoked now.
Thus we saw examples to revoke privileges from a particular user at different levels.
Conclusion
As discussed, the REVOKE function is used to take back any granted privileges for a particular user or user list. 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 three levels of REVOKE function are individual privileges, ALL privileges at once and the proxy privileges entitled to any user. The same function can also remove EXECUTE privilege from any stored function or procedure.
Recommended Article
This is a guide to the MySQL REVOKE. Here we discuss the MySQL REVOKE and the practical examples along with different subquery expressions and Output. you can also go through our suggested articles to learn more –
