Modify user privileges|V3.2.4|OceanBase Database| docs|Distributed Database

Modify user privileges

Last Updated：2023-10-27 09:57:43 Updated

You can grant or revoke user privileges as needed.

Grant privileges by using SQL statements

Prerequisites

When you grant an object privilege, you must be the owner of the object or have the privilege to be granted. For example, if user1 wants to grant the SELECT privilege on table t1 to user2, user1 must have the SELECT privilege on table t1. In addition, user1 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

The syntax for granting object privileges is as follows:

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 ...]

The syntax for granting system privileges is as follows:

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

Some notes about this:

obj_privilege: specifies the object privileges to be granted. When you grant multiple privileges to a user, the privileges must be separated with commas (,).

For more information about object privileges supported in Oracle mode, see Overview of user privileges.
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 (,).

For more information about system privileges supported in Oracle mode, see Overview of user privileges.
WITH GRANT OPTION: specifies whether the currently granted privileges can be further granted to other users. If yes, revoking the original privileges will also result in the revocation of all derived privileges.

Examples

Grant system privileges

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

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

For more information about the GRANT statement, see GRANT.

Revoke privileges by using SQL statements

Prerequisites

When you revoke an object privilege, you must have the privilege to be revoked. For example, if user1 wants to revoke the SELECT privilege on table t1 from user2, user1 must have the SELECT privilege on table t1. In addition, user1 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, the revocation of a privilege will not have a cascading effect. For example, if user1 has granted privileges to user2, revoking those privileges from user1 will not automatically revoke them from user2.

Syntax for revoking privileges

The syntax for revoking object privileges is as follows:

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

The syntax for revoking system privileges is as follows:

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

Some notes about this:

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

For more information about object privileges supported in Oracle mode, see Overview.
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 (,).

For more information about system privileges supported in Oracle mode, see Overview.

Examples

Revoke system privileges

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

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

For more information about the REVOKE statement, see REVOKE.

Grant or revoke user privileges in the OCP console

Prerequisites

Before you modify user privileges, ensure that:

You have permissions of the TENANT_MANAGER role. Otherwise, request the OceanBase Cloud Platform (OCP) administrator to assign the role. For more information, see "Edit a user" in the OCP User Guide of the corresponding version.
Your password box contains the password of the sys user under the tenant. For more information about the password box of an OCP user, see the OCP User Guide of the corresponding version.

Procedure

Log on to the OCP console.
In the left-side navigation pane, click Tenants to go to the Tenants page.
In the tenant list, click a tenant whose Tenant Mode is Oracle to go to the Overview page.
In the left-side navigation pane, click User Management.
On the Users tab, find the user whose privileges are to be modified and click its name to go to the details page of the user.
In the upper-right corner of the System Permissions section, click Modify System Permissions.
In the dialog box that appears, select the privileges to be granted or deselect the privileges to be revoked, and then click OK.