Overview of role management

The Oracle-compatible mode of OceanBase Database supports role management.

In Oracle-compatible mode, a role is a combination of system and object privileges. By using roles, you can easily manage user privileges.

Roles have the following characteristics:

• Roles can be granted system or object privileges.

• Roles can be granted other roles, that is, roles can include other roles.

• A user can be granted multiple roles, and a role can also be granted to multiple users.

Currently, OceanBase Database has the following system roles by default:

• CONNECT role

  This role provides the CREATE SESSION privilege, which is a system privilege. To grant the CREATE SESSION privilege to a user, you can grant this privilege directly or grant the CONNECT role to the user.

• RESOURCE role

  This role provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, and CREATE TYPE.

  You can view the privileges included in this role by querying the DBA_SYS_PRIVS dictionary view.

• DBA role

  This role is powerful and provides a large number of system privileges, such as DELETE ANY TABLE and GRANT ANY PRIVILEGE.

  You can view the privileges included in this role by querying the DBA_SYS_PRIVS dictionary view.

  Notice

  To ensure database security, grant this role only when necessary.

• PUBLIC role

  This role applies to all users in a tenant. By default, no privilege is granted to this role.

  If you grant a privilege to the PUBLIC role, all users in the tenant have the privilege. This means that all users can immediately perform operations that are authorized by the privilege.

  Notice

  To ensure database security, only grant a privilege to this role when it is necessary.

• STANDBY_REPLICATION role

  This role applies to network-based physical standby database scenarios. You can grant this role to a user dedicated for accessing views in the primary tenant. This way, related information in the primary tenant can be accessed from a standby tenant during synchronization.

  By default, this role has the CREATE SESSION system privilege and the privilege to query the following views:

  • GV$OB_LOG_STAT
  • GV$OB_UNITS
  • GV$OB_PARAMETERS
  • DBA_OB_ACCESS_POINT
  • DBA_OB_TENANTS
  • DBA_OB_LS
  • DBA_OB_LS_HISTORY

  To view the privileges of the role, query the DBA_SYS_PRIVS dictionary view.

References

For more information about how to manage roles, see the following topics:

• Create a role

• Grant a role to another role

• Grant a role to a user

• Activate or deactivate roles for a user

• View roles

• Modify a role

• Revoke a role

• Drop a role