View roles

You can view roles in an OceanBase cluster and the privileges of a role.

View roles by using views

You can view roles in the cluster, roles assigned to a role, and the privileges of a role.

• View roles in the cluster

  You can query the DBA_ROLES view to view all roles in the cluster.

  obclient> SELECT * FROM DBA_ROLES;
  +----------+-------------------+---------------------+
  | ROLE     | PASSWORD_REQUIRED | AUTHENTICATION_TYPE |
  +----------+-------------------+---------------------+
  | CONNECT  | NO                | NONE                |
  | RESOURCE | NO                | NONE                |
  | DBA      | NO                | NONE                |
  | PUBLIC   | NO                | NONE                |
  | ROLE1    | NO                | NONE                |
  +----------+-------------------+---------------------+
  5 rows in set
  

  For more information about fields in the DBA_ROLES view, see DBA_ROLES.

• View roles assigned to a role

  You can query the ROLE_ROLE_PRIVS view to view the roles assigned to a role. If no roles are assigned to the role, no result is returned.

  obclient> SELECT * FROM ROLE_ROLE_PRIVS WHERE role='ROLE1';
  +-------+--------------+--------------+
  | ROLE  | GRANTED_ROLE | ADMIN_OPTION |
  +-------+--------------+--------------+
  | ROLE1 | ROLE2        | NO           |
  +-------+--------------+--------------+
  1 row in set
  

  For more information about fields in the ROLE_ROLE_PRIVS view, see ROLE_ROLE_PRIVS.

• View the system privileges of a role

  To view the system privileges of a role, query the ROLE_SYS_PRIVS view as an administrator.

  obclient> SELECT * FROM ROLE_SYS_PRIVS WHERE role='CONNECT';
  +---------+----------------+--------------+
  | ROLE    | PRIVILEGE      | ADMIN_OPTION |
  +---------+----------------+--------------+
  | CONNECT | CREATE SESSION | NO           |
  +---------+----------------+--------------+
  1 row in set
  

  For more information about fields in the ROLE_SYS_PRIVS view, see ROLE_SYS_PRIVS.

• View the object privileges of a role

  You can query the ROLE_TAB_PRIVS view to view the object privileges of a role.

  obclient> SELECT * FROM ROLE_TAB_PRIVS WHERE role='ROLE1';
  +-------+-------+------------+-------------+-----------+-----------+
  | ROLE  | OWNER | TABLE_NAME | COLUMN_NAME | PRIVILEGE | GRANTABLE |
  +-------+-------+------------+-------------+-----------+-----------+
  | ROLE1 | SYS   | T1         | NULL        | SELECT    | NO        |
  +-------+-------+------------+-------------+-----------+-----------+
  1 row in set
  

  For more information about fields in the ROLE_TAB_PRIVS view, see ROLE_TAB_PRIVS.