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 SQL Tutorial SQL REVOKE
 

SQL REVOKE

Updated March 14, 2023

SQL REVOKE

 

 

Introduction to SQL REVOKE

REVOKE command in standard query language (SQL) is used to remove previously granted permissions or privileges to a user account on database objects. Therefore, we can consider REVOKE as a tool to restrict the ability of an already permitted role or user to perform select, insert, delete, alter statements, assign constraints like foreign keys, update a data record, etc., on the database objects.

Watch our Demo Courses and Videos

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

REVOKE is a command antipodal to GRANT, which grants privileges on database objects to a user account or role. For example, in SQL, we can grant or revoke the following sets of privileges from a user.

You may consider it as a summary table for reference purposes.

Privilege Description
SELECT The ability of the user account to perform SELECT statements and fetch records from the said database object.
DELETE The ability of the user account to perform DELETE statements and delete or remove records from the said database object.
INSERT The ability of the user account to perform INSERT statements and insert records in the said database object.
UPDATE The ability of the user account to perform UPDATE statements and update records in the said database object.
ALTER The ability of the user account to perform ALTER statements and add, delete or modify columns in the said database object.
TRUNCATE The ability of the user account to perform TRUNCATE statements and delete the said database object.
TRIGGER The ability of the user account to specify TRIGGER constraints like logon triggers to the said data objects.
REFERENCES The ability of the user account to specify constraints like foreign keys that reference the said data objects.
ALL The ability of the user account to perform all the above-mentioned statements except ALTER, i.e., SELECT, INSERT, DELETE, UPDATE, REFERENCES, etc.

Having seen about the types of privileges we can grant a user account in SQL databases.

The syntax for REVOKE Command in SQL

The basic syntax for writing a REVOKE command in SQL is as follows:

REVOKE PRIVILEGES [, ...]
ON DATABASE_OBJECTS [, ...]
FROM ROLE
[ CASCADE | RESTRICT ]

The parameters used in the above syntax are as follows:

  • PRIVILEGES: This is the argument where we specify the privilege(s) that has to be removed for a user account. We may refer to the table mentioned above on privileges.
  • DATABASE OBJECTS: Database objects can be anything like a specific table or all the tables in a schema.
  • ROLE: Here, we specify the user account from which the privileges have to be removed. We can choose from an array of possibilities like PUBLIC, GROUP, or USER.
  • CASCADE | RESTRICT: We can indicate if the revocation of the above privileges also revokes other privileges which derive from the former using the CASCADE keyword. Otherwise, we can restrict revocation to just the specified privilege.

It has been rightly said that learning is incomplete without kinesthetic learning.

Examples of SQL REVOKE

Given below are the examples of SQL REVOKE:

In order to illustrate the use of the REVOKE command, let us first create a user account and then grant it some privileges using the GRANT command. We will use the following SQL statement to create a new role called ‘user_1’. You may rename it to your preference.

Code:

CREATE ROLE user_1;

Output:

SQL REVOKE 1

Now we have successfully created a new role, ‘user_1’ in the database. Now let us grant it some privileges using the GRANT command. We can use the following code_snippet to grant ALL privileges.

ALL keyword grants privileges such as SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.

Code:

GRANT ALL PRIVILEGES
ON sales_details
TO user_1;

Output:

SQL REVOKE 2

Example #1

SQL query to illustrate revocation of UPDATE privilege from the user_1.

Code:

REVOKE UPDATE
ON sales_details
FROM user_1;

Output:

SQL REVOKE 3

In this example, we have successfully removed user_1’s ability to perform UPDATE statements on the sales_details table. So now, user_1 cannot update any record in the said table.

Example #2

SQL query to illustrate revocation of SELECT and INSERT privileges from the user_1.

Code:

REVOKE SELECT, INSERT
ON sales_details
FROM user_1;

Output:

SELECT and INSERT

Here, we have revoked user_1’s ability to perform SELECT and INSERT statements on the sales_details table. Ergo, user_1 will not be able to fetch records from the sales_details table. It will not also be able to insert new records in the table.

Example #3

SQL query to illustrate revocation of DELETE and TRUNCATE privileges from the user_1.

Code:

REVOKE DELETE, TRUNCATE
ON sales_details
FROM user_1;

Output:

DELETE and TRUNCATE

In this example, we have revoked user_1’s ability to perform DELETE and TRUNCATE statements. That is, user_1 would not be able to delete one or more rows from the sales_details table.

Example #4

SQL query to illustrate revocation of TRIGGER and REFERENCES privileges from the user_1.

Code:

REVOKE TRIGGER, REFERENCES
ON sales_details
FROM user_1;

Output:

TRIGGER and REFERENCES privileges

Here we have removed user_1’s ability to perform TRIGGER and REFERENCE constraints on the sales_details table. Ergo, user_1 will not be able to make references such as foreign keys on the sales_details table. In addition, it will not be able to specify triggers.

Conclusion

The REVOKE command in SQL is used to remove already granted permissions or privileges to a user account on database objects like tables and schemas. It is useful for maintaining the integrity and security of the database.

Recommended Articles

We hope that this EDUCBA information on “SQL REVOKE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL DML Commands
  2. SQL WITH AS Statement
  3. SQL TRUNCATE()
  4. Cursors in SQL

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