Description
This statement is used to revoke previously granted privileges from a user or role, or to revoke roles from a user or role.
Limitations and considerations
- Revoking object privileges does not cascade. For example, if user
user1grants certain privileges touser2, revokinguser1's privileges will not automatically revoke the corresponding privileges fromuser2. - The user or role for which you want to revoke the privilege must exist. Otherwise, an error will be returned.
- Roles in the system variable
mandatory_rolescannot be revoked. - The
REVOKEstatement takes effect immediately for the currently logged-in user.
Privilege requirements
Revoke a specific privilege level: The user must have the privilege to be revoked (for example, if
user1wants to revoke theSELECTprivilege on tablet1fromuser2, thenuser1must have theSELECTprivilege on tablet1), and must also have theGRANT OPTIONprivilege.WITHDRAW ALL PRIVILEGES AND GRANT OPTION: When revoking the
ALL PRIVILEGESandGRANT OPTIONprivileges, the current user must have the globalGRANT OPTIONprivilege, or theUPDATEandDELETEprivileges on the privilege table.Revoke role privileges from a user or role: When revoking a role, the current user must possess the role being revoked and have the
ADMIN OPTIONprivilege to succeed.
For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
Revoke object privileges from a user or role
revoke_stmt1:
REVOKE grant_privileges ON priv_level FROM user_or_role_list;
revoke_stmt2:
REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user_or_role_list;
revoke_stmt3:
REVOKE [IF EXISTS] role_name_list FROM user_or_role_list [IGNORE UNKNOWN USER];
grant_privileges:
priv_type_list
| ALL [PRIVILEGES]
priv_type_list:
priv_type[(column_name_list)] [, priv_type[(column_name_list)] ...]
column_name_list:
column_name [, column_name ...]
priv_level:
*.*
| db_name.*
| db_name.table_name
| CATALOG external_catalog_name
| LOCATION location_name
user_or_role_list:
user_or_role [, user_or_role ...]
user_or_role:
user_name | role_name
role_name_list:
role_name [, role_name ...]
Parameters
Parameters |
Description |
|---|---|
| revoke_stmt1 | SQL statements for revoking specific privilege levels from users or roles. |
| revoke_stmt2 | SQL statement for revoking all privileges and grant options from a user or role. |
| revoke_stmt3 | SQL statement for revoking roles from users or roles. |
| grant_privileges | Specify the type of privilege to revoke. It can be a list of specific privilege types or all privileges (GRANT OPTION(Except for the following). |
| priv_type_list | Represents a list of specific privilege types. When revoking multiple privileges simultaneously, separate the privileges with commas (,). |
| priv_type | Represents the specific permission type. For detailed information about the permission types, see Permission classification. Note: The PROXY parameter is only documented for syntax in the current version and its functionality is not available. |
| column_name_list | Optional. The list of column-level privileges to revoke from the table. If not specified, the privileges for the entire table or object are revoked. |
| ALL [PRIVILEGES] | Represents all values other than GRANT OPTION All other privileges. |
| priv_level | Specifies the level at which the privilege is granted. For more information about the possible values of priv_level, see priv_level. |
| user_or_role_list | Specify the list of users or roles whose privileges need to be revoked. When revoking privileges for multiple users or roles at the same time, separate the user and role names with commas (,). |
| user_name | Represents the username. |
| role_name | Represents the role name. |
| IF EXISTS | Optional. When the role to be revoked does not exist, and the IF EXISTS option is used, the system issues a warning instead of an error message. |
| role_name_list | Specify the list of roles to be revoked. When revoking multiple roles, separate the role names with commas (,). |
| IGNORE UNKNOWN USER | Optional. When the grantee (user or role) does not exist, and the IGNORE UNKNOWN USER option is used, the system issues a warning instead of an error message. |
| proxy | The syntax of this keyword is recorded only in the current version and is not yet functional. |
priv_level
*.*: All tables in all databases.Example:
Revoke the global
SELECTandINSERTprivileges from usersuser001anduser002.REVOKE SELECT, INSERT ON *.* FROM user001, user002;db_name.*: specifies all tables in the specified database.db_nameis the name of the database.Example:
Revoke the INSERT privilege on all tables in the
testdatabase from useruser003.REVOKE INSERT ON test.* FROM user003;db_name.table_name: specifies a specific table in a specific database.Example:
Revoke the
SELECTprivilege on thetbl2table in thetestdatabase from useruser003.REVOKE SELECT ON test.tbl2 FROM user003;CATALOG external_catalog_name: specifies the catalog within the catalog.Example:
Revoke the
USE CATALOGprivilege on thetest_odps_catalogobject from useruser004.REVOKE USE CATALOG ON CATALOG test_odps_catalog FROM user004;LOCATION location_name: specifies the location object.Example:
Revoke the
READprivilege on the objecttest_location_localfrom the useruser005.REVOKE READ ON LOCATION test_location_local FROM user005;
Examples
Example 1: Revoke object privileges
Revoke the SELECT privilege for user test_user001 to see which privileges were granted by ALL PRIVILEGES.
Grant the
ALL PRIVILEGESprivilege to the usertest_user001.GRANT ALL PRIVILEGES ON *.* TO test_user001;View the privileges granted to the user
test_user001.SHOW GRANTS FOR test_user001;The return result is as follows:
+-----------------------------------------------+ | Grants for test_user001@% | +-----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'test_user001' | +-----------------------------------------------+ 1 row in setRevoke the
SELECTprivilege for usertest_user001.REVOKE SELECT ON *.* FROM test_user001;Check the user
test_user001for authorizations again.SHOW GRANTS FOR test_user001;The return result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test_user001@% | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, INDEX, CREATE VIEW, SHOW VIEW, SHOW DATABASES, SUPER, PROCESS, CREATE_SYNONYM, REFERENCES, FILE, ALTER TENANT, ALTER SYSTEM, CREATE RESOURCE POOL, CREATE RESOURCE UNIT, REPLICATION SLAVE, REPLICATION CLIENT, DROP DATABASE LINK, CREATE DATABASE LINK, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TABLESPACE, SHUTDOWN, RELOAD, CREATE ROLE, DROP ROLE, TRIGGER ON *.* TO 'test_user001' | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
Example 2: Revoke all privileges from a user
Revoke all privileges from user test_user002.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM test_user002;
Example 3: Remove a role from a user or role
Remove role
role001from rolerole002.REVOKE role001 FROM role002;Revoke role
role001from useruser001.REVOKE role001 FROM user001;