Modify user privileges|V4.1.0| docs|Distributed Database

Modify user privileges

Last Updated：2023-08-01 06:02:28 Updated

You can grant and revoke user privileges.

Grant privileges

Prerequisites

When you grant an object privilege, you must be the owner of the object or have the privilege to be granted. For example, for the test1 user to grant the SELECT privilege on table t1 to the test2 user, the test1 user must have the SELECT privilege on table t1. In addition, you must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege.
When you grant a system privilege or a role, you must have the privilege or role to be granted and have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege.

For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information about how to view your roles, see View roles.

Considerations

When you grant privileges, note the following information:

When you grant multiple privileges to a user at a time, separate the privileges with commas (,).
After a user is granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.

Syntax for granting privileges

Syntax for granting object privileges:

GRANT obj_with_col_priv_list
    ON obj_clause TO grant_user_list [WITH GRANT OPTION];

obj_with_col_priv_list:
  obj_with_col_priv
  | obj_with_col_priv_list, obj_with_col_priv

obj_with_col_priv:
  obj_privilege [column_list]

obj_privilege:
    ALTER
  | DELETE
  | INDEX
  | INSERT
  | SELECT
  | UPDATE
  | REFERENCES
  | EXECUTE

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

grant_user_list:
    grant_user [, grant_user ...]

Syntax for granting system privileges:

GRANT {system_privilege_list | ALL PRIVILEGES}
    TO grantee_user [IDENTIFIED BY password];

system_privilege_list:
  system_privilege [, system_privilege ...]

system_privilege:
    CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

Notes:

obj_privilege: specifies the object privileges to be granted. When you grant multiple privileges to a user, the privileges must be separated with commas (,).
obj_clause: specifies the objects related to the object privileges to be granted.
system_privilege: specifies the system privileges to be granted. When you grant multiple privileges to a user, the privileges must be separated with commas (,).
WITH GRANT OPTION: specifies whether to enable privilege delegation. When privilege delegation is enabled, grant revocation extends to dependent users.

Examples

Grant system privileges

Grant the CREATE SEQUENCE privilege to the test user.
```
obclient> GRANT CREATE SEQUENCE TO test;
```
Grant object privileges

Grant the SELECT and UPDATE privileges on the emp_view view to the test user.
```
obclient> GRANT SELECT, UPDATE ON emp_view TO test;
```

For more information about the GRANT statement, see GRANT.

Revoke privileges

Prerequisites

When you revoke an object privilege, you must have the privilege to be revoked. For example, for the test1 user to revoke the SELECT privilege on table t1 from the test2 user, the test1 user must have the SELECT privilege on table t1. In addition, you must have the GRANT OPTION or GRANT ANY OBJECT PRIVILEGE privilege.
When you revoke a system privilege or a role, you must have the privilege or role to be revoked and have the GRANT OPTION, GRANT ANY PRIVILEGE, or GRANT ANY ROLE privilege.
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.

For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges.

Considerations

When you revoke multiple privileges of a user, the privileges must be separated with commas (,).
When you revoke privileges of multiple users, the usernames must be separated with commas (,).
If GRANT OPTION is not specified when you grant privileges to a user, grant revocation does not extend to dependent users. For example, if the test1 user has granted some privileges to the test2 user, when the privileges of the test1 user are revoked, the privileges granted to the test2 user will not be revoked.

Syntax for revoking privileges

Syntax for revoking object privileges:

REVOKE obj_privileges
  ON obj_clause FROM user_list;

user_list:
  user [, user ...]

obj_privileges:
  obj_privilege [, obj_privilege ...]

obj_privilege:
    ALTER
  | DELETE
  | INDEX
  | INSERT
  | SELECT
  | UPDATE
  | REFERENCES
  | EXECUTE

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

relation_name:
  STR_VALUE

Syntax for revoking system privileges:

REVOKE {system_privilege_list | ALL PRIVILEGES}
  FROM user_list;
REVOKE ALL [PRIVILEGES], GRANT_OPTION FROM user_list;

system_privilege_list:
  system_privilege [, system_privilege ...]

system_privilege:
  CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

Notes:

obj_privilege: specifies the object privileges to be revoked. When you revoke multiple privileges, the privileges must be separated with commas (,).
obj_clause: specifies the objects related to the object privileges to be revoked.
system_privilege: specifies the system privileges to be revoked. When you revoke multiple privileges, the privileges must be separated with commas (,).

Examples

Revoke system privileges

Revoke the CREATE SEQUENCE privilege of the test user.
```
obclient> REVOKE CREATE SEQUENCE FROM test;
```
Revoke object privileges

Revoke the SELECT and UPDATE privileges on the emp_view view from the test user.
```
obclient> REVOKE ALL PRIVILEGES FROM user_name;
```

For more information about the REVOKE statement, see REVOKE.