If you’re looking to learn all the privileges granted to a specific user, you can utilize the following data dictionary tables. Additionally, you have the option to utilize DBA_ tables.
--list system privileges to the current user
SELECT * FROM USER_SYS_PRIVS;
--list object privileges to the current user
SELECT * FROM USER_TAB_PRIVS;
--list roles granted to the current user
SELECT * FROM USER_ROLE_PRIVS;
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '&USER';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '&USER';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '&USER';
So, let’s demonstrate this with a brief example.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "HR"
SQL>
SQL> create table tab1
2 (
3 c1 number
4 );
Table created.
SQL> create user test_user identified by "test_user";
User created.
SQL> grant connect to test_user;
Grant succeeded.
SQL> grant unlimited tablespace to test_user;
Grant succeeded.
SQL> grant create table to test_user;
Grant succeeded.
SQL> grant create sequence to test_user;
Grant succeeded.
SQL> grant select, insert, delete, update on tab1 to test_user;
Grant succeeded.
Now let’s check the privileges.
SQL> SET linesize 100
SQL> COLUMN privilege FORMAT A25
SQL> COLUMN admin_option FORMAT A25
SQL> COLUMN table_name FORMAT A25
SQL> COLUMN grantor FORMAT A25
SQL> COLUMN granted_role FORMAT A25
SQL>
SQL> SELECT PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE = 'TEST_USER';
PRIVILEGE ADMIN_OPTION
------------------------- -------------------------
CREATE TABLE NO
UNLIMITED TABLESPACE NO
CREATE SEQUENCE NO
SQL>
SQL>
SQL> SELECT TABLE_NAME, GRANTOR, PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'TEST_USER';
TABLE_NAME GRANTOR PRIVILEGE
------------------------- ------------------------- -------------------------
TAB1 HR DELETE
TAB1 HR INSERT
TAB1 HR SELECT
TAB1 HR UPDATE
SQL>
SQL>
SQL> SELECT GRANTED_ROLE, ADMIN_OPTION FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'TEST_USER';
GRANTED_ROLE ADMIN_OPTION
------------------------- -------------------------
CONNECT NO
Moreover, you can explore the details of which privileges belong to which roles by utilizing the ROLE_SYS_PRIVS table.
SQL> COLUMN role FORMAT A25
SQL> COLUMN privilege FORMAT A25
SQL>
SQL> SELECT ROLE, PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE = 'CONNECT';
ROLE PRIVILEGE
------------------------- -------------------------
CONNECT SET CONTAINER
CONNECT CREATE SESSION
I hope this helps 🙂
Thank you very much