SQL > Data Definition Language (DDL) > Revoke

The REVOKE command is used to take away database privileges from a user. It is the opposite of the GRANT command.

It is worth noting that some lightweight databases, such as SQLite, do not implement REVOKE (and GRANT) commands. This is because such databases are intended to be an embedded database engine and hence user-level privileges are meaningless.

Syntax

The syntax for REVOKE is as follows:

REVOKE "Privilege_Name" ON "Database_Object" FROM "User";

The available "Privilege_Name" will vary depending on the relational database. Typical privileges include the following:

  • SELECT
  • INSERT
  • DELETE
  • UPDATE

In many databases there is a privilege called "ALL." To remove all privileges from a user, it is tempting to use the "ALL" privilege type. However because "ALL" doesn't always cover all the privileges (it is database-dependent), it is best to first check what privileges that user has and revoke each of them. Such information is typically stored in a system table (each database has its own set of system tables), so please consult your database documentation for this information.

The links below show the types of privileges available for each of the databases:

Examples

Example 1: Revoke one privilege from a user

To remove the SELECT privilege on the "Sales" table from user tanner1, we type in the following:

REVOKE SELECT ON Sales FROM tanner1;

Example 2: Revoke one privilege from all users

To remove the DELETE privilege on the "Sales" table from all users, we use the following:

REVOKE SELECT ON Sales FROM public;

Note that some databases such as MySQL have a different way to identify all users. So please consult with your database documentation if public does not work.

Next: SQL Constraint

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact