View user privileges

After a user is created, you can view the privileges granted to the user as needed.

View user privileges by using SQL statements

1. Log on to an Oracle tenant of OceanBase Database.

2. Execute the following statements to view the privileges granted to a user:

   • View the system privileges granted to a user.

     The administrator can query the DBA_SYS_PRIVS view for the system privileges granted to a user.

     obclient> SELECT * FROM DBA_SYS_PRIVS WHERE grantee='SECUSER';
     +---------+-----------------+--------------+
     | GRANTEE | PRIVILEGE       | ADMIN_OPTION |
     +---------+-----------------+--------------+
     | SECUSER | CREATE SEQUENCE | NO           |
     +---------+-----------------+--------------+
     1 row in set
     

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

   • View the system privileges granted to the current user.

     Both the current user and the administrator can query the USER_SYS_PRIVS view for the system privileges granted to the current user.

     obclient> SELECT * FROM USER_SYS_PRIVS;
     +----------+----------------+--------------+
     | USERNAME | PRIVILEGE      | ADMIN_OPTION |
     +----------+----------------+--------------+
     | USER2    | CREATE SESSION | NO           |
     | USER2    | CREATE TABLE   | YES          |
     +----------+----------------+--------------+
     2 row in set
     

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

   • View the object privileges granted to a user.

     The administrator can query the DBA_TAB_PRIVS view for the object privileges granted to a user.

     obclient> SELECT * FROM DBA_TAB_PRIVS WHERE grantee='SECUSER';
     +---------+-------+------------+---------+-----------+-----------+-----------+
     | GRANTEE | OWNER | TABLE_NAME | GRANTOR | PRIVILEGE | GRANTABLE | HIERARCHY |
     +---------+-------+------------+---------+-----------+-----------+-----------+
     | SECUSER | SYS   | TBL4       | SYS     | SELECT    | NO        | NO        |
     | SECUSER | SYS   | TBL4       | SYS     | UPDATE    | NO        | NO        |
     +---------+-------+------------+---------+-----------+-----------+-----------+
     2 rows in set
     

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

   • View the object privileges granted to the current user.

     Both the current user and the administrator can query the USER_TAB_PRIVS view for the object privileges granted to the current user.

     obclient> SELECT * FROM USER_TAB_PRIVS;
     +---------+-------+-----------------+---------+-----------+-----------+-----------+
     | GRANTEE | OWNER | TABLE_NAME      | GRANTOR | PRIVILEGE | GRANTABLE | HIERARCHY |
     +---------+-------+-----------------+---------+-----------+-----------+-----------+
     | PUBLIC  | SYS   | VERIFY_FUNCTION | SYS     | EXECUTE   | NO        | NO        |
     +---------+-------+-----------------+---------+-----------+-----------+-----------+
     1 row in set
     

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

   • View the role of a user.

     The administrator can query the DBA_ROLE_PRIVS view for the role of a user.

     obclient> SELECT * FROM DBA_ROLE_PRIVS WHERE grantee='USER2';
     +---------+--------------+--------------+--------------+
     | GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DEFAULT_ROLE |
     +---------+--------------+--------------+--------------+
     | USER2   | ROLE1        | NO           | YES          |
     +---------+--------------+--------------+--------------+
     1 row in set
     

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

     For more information about how to view the object privileges or system privileges of each role, see View a role.

   • View the role of the current user.

     Both the current user and the administrator can query the USER_ROLE_PRIVS view for the role of the current user.

     obclient> SELECT * FROM USER_ROLE_PRIVS;
     +---------+--------------+--------------+--------------+
     | GRANTEE | GRANTED_ROLE | ADMIN_OPTION | DEFAULT_ROLE |
     +---------+--------------+--------------+--------------+
     | SYS     | ROLE1        | YES          | YES          |
     +---------+--------------+--------------+--------------+
     1 row in set
     

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

     For more information about how to query the object privileges or system privileges of the current role, see View a role.

View user privileges in the OCP console

You can also view the privileges and role granted to a specified user in the OceanBase Cloud Platform (OCP) console.

1. Log on to the OCP console.

2. In the left-side navigation pane, click Tenants to go to the Tenants page.

3. In the tenant list, click a tenant whose Tenant Mode is Oracle to go to the Overview page.

4. In the left-side navigation pane, click User Management.

5. In the user list, find the target user and move the pointer over the System Permissions column to view the privileges of the user, or over the Roles column to view the roles assigned to the user.