How to list all privileges of a user

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 🙂

Posted in DBA
Leave a Reply

Your email address will not be published. Required fields are marked *