All Products
Search
Document Center

REVOKE

Last Updated: Jun 18, 2021

Description

You can execute the REVOKE statement to revoke permissions from users as a system administrator.

Notes:

  • To revoke permissions from a user, ensure that you have the permissions to be revoked. For example, to revoke the SELECT permission on Table 1 from User 2 as User 1, ensure that User 1 has this permission and the GRANT OPTION permission.
  • To revoke the ALL PRIVILEGES or GRANT OPTION permission, ensure that you have the global permission to execute the GRANT OPTION statement or the UPDATE and DELETE permissions on the permission table.
  • Revocations do not have cascading effects. Assume that User 1 has granted permissions to User 2. If you revoke permissions from User 1, the permissions are not revoked from User 2.

Syntax

REVOKE priv_type 
     ON database.tblname 
     FROM 'username';

privilege_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE

Parameters

Parameter

Description

priv_type

The permission that you want to revoke. For more information, see the Permissions table in the following description.

To revoke multiple permissions from a user, separate the permissions with commas (,).

database.tblname

The table in the database.

To revoke permissions on all tables in the database, use an asterisk (*) to replace database or table_name.

username

The user from which the permissions are revoked. To revoke permissions from multiple users at a time, separate the usernames with commas (,).

The following table lists the permissions that can be revoked.

Permissions

Permission

Description

ALL PRIVILEGES

All permissions except GRANT OPTION.

ALTER

The permission to execute the ALTER TABLE statement.

CREATE

The permission to execute the CREATE TABLE statement.

CREATE USER

The permission to execute the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.

CREATE TABLEGROUP

The global permission to execute the CREATE TABLEGROUP statement.

DELETE

The permission to execute the DELETE statement.

DROP

The permission to execute the DROP statement.

GRANT OPTION

The permission to execute the GRANT OPTION statement.

INSERT

The permission to execute the INSERT statement.

SELECT

The permission to execute the SELECT statement.

UPDATE

The permission to execute the UPDATE statement.

SUPER

The permission to execute the SET GLOBAL statement to modify global system parameters.

SHOW DATABASES

The global permission to execute the SHOW DATABASES statement.

INDEX

The permission to execute the CREATE INDEX and DROP INDEX statements.

CREATE VIEW

The permission to create or delete a view.

SHOW VIEW

The permission to execute the SHOW CREATE VIEW statement.

CREATE SYNONYM

The permission to create a synonym.

Note

The permission to execute the CHANGE EFFECTIVE TENANT statement is not limited. Therefore, all users under the system tenant can revoke this permission from other users.

Examples

Run the following command to revoke all permissions from the obsqluser01 user:

OceanBase(admin@TEST)>REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'obsqluser01';
Query OK, 0 rows affected (0.03 sec)