View user privileges

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

Procedure

1. Log on to an Oracle tenant of the 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 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.