Finding all the privileges and roles granted to a user

To find all the privileges and roles granted to a user 3 dictionary views comes very handy:

1. dba_sys_privs
2. dba_tab_privs
3. dba_role_privs

I queried all 3 views and then verified with toad to match the result for a user called "USERNAME".
The result was very accurate.

SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='USERNAME';
GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
USERNAME                        CREATE PUBLIC SYNONYM
USERNAME                        CREATE SYNONYM
USERNAME                        UNLIMITED TABLESPACE
USERNAME                        CREATE SEQUENCE
USERNAME                        CREATE ANY SYNONYM
USERNAME                        CREATE MATERIALIZED VIEW
USERNAME                        CREATE TRIGGER
USERNAME                        CREATE TABLE
USERNAME                        CREATE SESSION
USERNAME                        CREATE DATABASE LINK
USERNAME                        CREATE PROCEDURE
USERNAME                        CREATE VIEW

12 rows selected.

SQL> select GRANTEE, PRIVILEGE from dba_tab_privs where GRANTEE='USERNAME';

no rows selected

SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where GRANTEE='USERNAME';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
USERNAME                        CONNECT
USERNAME                        RESOURCE