How to check Roles & Privs for a certain user

DBA_ROLES:
DBA_ROLES lists all roles that exist in the database.
select * from DBA_ROLES

DBA_ROLE_PRIVS:
check user’s roles granted:
select * from DBA_ROLE_PRIVS where grantee = 'TEST_OWNER'

DBA_SYS_PRIVS:
check user’s or role’s sys privs granted:
select * from DBA_SYS_PRIVS where grantee = 'TEST_OWNER' (user)
select * from DBA_SYS_PRIVS where grantee = 'TEST_OWNER_ROLE' (role)

ROLE_SYS_PRIVS:
check role’s sys privs granted:
select * from ROLE_SYS_PRIVS where ROLE ='TEST_OWNER_ROLE' (role)

ROLE_ROLE_PRIVS: (ROLE_ROLE_PRIVS describes the roles granted to other roles.)
select * from ROLE_ROLE_PRIVS

DBA_TAB_PRIVS:
DBA_TAB_PRIVS describes all object grants in the database. It includes all objects(tables, views, packages…, NOT only tables), normally the grantee is a role, we grant the role to user. The query displays the direct grants on user or role.
select * from DBA_TAB_PRIVS where grantee='TEST_ROLE'

ALL_COL_PRIVS:
DBA_COL_PRIVS describes all column object grants in the database. If no grants on column, will no records.

So what exactly is in those CONNECT and RESOURCE Oracle roles? The ship with every Oracle database and many apps require they be granted. I did a little digging and found the following:

In Oracle 10gR2 things are fairly sane:
CONNECT role has only CREATE SESSION
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

In Oracle 9iR2 things get a little scary:
CONNECT has ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW. Rather a scary lot for a role called ‘connect’
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

The admin option would allow the users to grant the privlelge to another user. Thankfully neither of these roles have the admin option in the versions of Oracle I checked.

To find these privileges you can query the DBA_SYS_PRIVS table with a query like this:

select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

Of course an oracle role could also have table or column privileges granted to it, so to be thorough you should also check for entries in DBA_TAB_PRIVS and DBA_COL_PRIVS.

NOTE: You should always check these privileges in your database before granting roles as a script, application or previous DBA may have granted or revoked additional roles.