EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL REVOKE
 

MySQL REVOKE

Priya Pedamkar
Article byPriya Pedamkar

Updated May 17, 2023

MySQL REVOKE

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL REVOKE Example 1

Query:

We have granted the new user ‘all’ access privileges for the database’ world’.

SHOW GRANTS FOR abraham;

Output:

MySQL REVOKE Example 2

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:

MySQL REVOKE Example 3

Executing the SHOW GRANTS query after the REVOKE query displays the difference in privileges the user holds before and after executing the REVOKE function.

MySQL REVOKE Example 4

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:

MySQL REVOKE Example 5

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.

MySQL REVOKE Example 6

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:

SHOW GRANTS Example 19

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:

MySQL REVOKE Example 8

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.

MySQL REVOKE Example 9

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’.

MySQL REVOKE Example 10

Execute the SHOW GRANTS on ‘abraham’.

SHOW GRANTS;

MySQL REVOKE Example 11

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:

MySQL REVOKE Example 12

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;

MySQL REVOKE Example 13

Now, let’s revoke the EXECUTE privilege from this user by the REVOKE function.

Query:

REVOKE EXECUTE ON FUNCTION Addition FROM abraham;

Output:

MySQL REVOKE Example 14

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;

GRANTS Example 15

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:

Grants Execute Privilege Example 16

Let’s run the SHOW GRANTS query to check the privileges assigned to the user ‘abraham’.

SHOW GRANTS;

GRANTS Query Example 17

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:

EXECUTE privilege Example 18

To verify if the privilege is revoked for the user ‘abraham’, execute the SHOW GRANTS query for that user.

SHOW GRANTS;

GRANTS Example 19

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.

  1. Introduction to MySQL Operators
  2. MySQL vs SQLite | Top 14 Comparison
  3. Explain Insert Command in MySQL with Examples
  4. Complete Guide on MySQL BETWEEN

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW