EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL REVOKE
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL REVOKE

By Priya PedamkarPriya Pedamkar

MySQL REVOKE

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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]…;

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,650 ratings)

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:

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 on different scopes to revoke the same.

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:

MySQL REVOKE Example 3

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.

MySQL REVOKE Example 4

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:

MySQL REVOKE Example 5

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.

MySQL REVOKE Example 6

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:

SHOW GRANTS Example 19

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:

MySQL REVOKE Example 8

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.

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 which were 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 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:

MySQL REVOKE Example 12

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;

MySQL REVOKE Example 13

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

Query:

REVOKE EXECUTE ON FUNCTION Addition FROM abraham;

Output:

MySQL REVOKE Example 14

The EXECUTE privilege is revoked for user ‘abraham’ in this query. Let’s try the SHOW GRANTS query to check the same.

SHOW GRANTS;

GRANTS Example 15

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:

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

We can check the privilege is revoked or not by running the SHOW GRANTS query for user ‘abraham’.

SHOW GRANTS;

GRANTS Example 19

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 –

  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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more