REVOKE

2024-06-28 05:30:30  Updated

Purpose

You can use this statement to revoke privileges or roles granted to a user or role.

Limitations and considerations

  • To revoke the ALL PRIVILEGES and GRANT OPTION privileges, you must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the table.
  • Privilege revocation does not cascade to dependent users or roles. For example, if user1 has granted some privileges to user2, when the privileges of user1 are revoked, the privileges granted to user2 are not revoked.
  • The REVOKE statement takes effect immediately for the currently logged-on user.
  • Roles defined in the system variable mandatory_roles cannot be revoked.

Required privileges

Privileges required for revoking privileges from a user or role

You must have the privileges to be revoked and the GRANT OPTION privilege. For example, if you want to revoke the SELECT privilege on table t1 from the user user2 as the user user1, you must have the SELECT privilege on table t1 and the GRANT OPTION privilege.

Privileges required for revoking roles from a user or role

You must have the roles to be revoked and the ADMIN OPTION privilege.

Syntax

Revoke privileges from a user or role

REVOKE priv_type
     ON database.table_name
     FROM 'user_or_role';

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

user_or_role:
    user_name | role_name

Revoke roles from a user or role

REVOKE [IF EXISTS] role_name [, role_name ...]
    FROM user_or_role [, user_or_role ...]
    [IGNORE UNKNOWN USER];

user_or_role:
    user_name | role_name

Parameters

Parameter Description
priv_type The type of the privilege to be revoked. For information about the specific privilege types and their description, see the following table. Multiple privileges must be separated with commas (,).
database.table_name The name of the target table in the database. If you replace database or table_name with an asterisk (*), operation privileges on all tables in the database will be revoked.
user_name The name of the user from which the privilege is to be revoked. Multiple usernames must be separated with commas (,).
IF EXISTS | IGNORE UNKNOWN USER Optional. If you specify IF EXISTS and IGNORE UNKNOWN USER, a warning instead of an error message is generated when the role to be revoked has not been granted or the user or role from which the role is revoked does not exist.
role_name The name of the role.

The following table lists the types of privileges that can be revoked.

Privilege type Description
ALL PRIVILEGES All privileges except GRANT OPTION.
ALTER The ALTER TABLE privilege.
CREATE The CREATE TABLE privilege.
CREATE USER The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges.
CREATE TABLEGROUP The global CREATE TABLEGROUP privilege.
DELETE The DELETE privilege.
DROP The DROP privilege.
GRANT OPTION The GRANT OPTION privilege.
INSERT The INSERT privilege.
SELECT The SELECT privilege.
UPDATE The UPDATE privilege.
SUPER The SET GLOBAL privilege for modifying global system parameters.
SHOW DATABASES The global SHOW DATABASES privilege.
INDEX The CREATE INDEX and DROP INDEX privileges.
CREATE VIEW The CREATE VIEW and DROP VIEW privileges.
SHOW VIEW The SHOW CREATE VIEW privilege.

Note

Currently, all users in the sys tenant can execute the CHANGE EFFECTIVE TENANT statement to revoke privileges of specified users in user tenants.

Examples

Example 1: Revoke a privilege

Revoke the SELECT privilege from user u1 and then check whether the SELECT privilege is revoked from the privileges that were granted by using the GRANT ALL PRIVILEGES statement.

obclient> GRANT ALL PRIVILEGES ON *.* TO u1;
Query OK, 0 rows affected

obclient> SHOW GRANTS FOR u1;
+-------------------------------------+
| Grants for u1@%                     |
+-------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'u1' |
+-------------------------------------+
1 row in set

obclient> REVOKE SELECT ON *.* FROM u1;
Query OK, 0 rows affected

obclient> SHOW GRANTS FOR u1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for u1@%
                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, INDEX, CREATE VIEW, SHOW VIEW, SHOW DB, SUPER, PROCESS, FILE, ALTER TENANT, ALTER SYSTEM, CREATE RESOURCE POOL, CREATE RESOURCE UNIT ON *.* TO 'u1' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Example 2: Revoke a role

  • Revoke the role001 role from the role002 role.

    obclient [test]> REVOKE role001 FROM role002;

  • Revoke the role001 role from the user001 user.

    obclient [test]> REVOKE role001 FROM user001;

References

Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartnerTrust Center

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite Paper

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us