What roles do I have?

Ever wanted to know what Oracle roles your oracle user has? This simple query will offer up a complete list including the objects and privileges for each as specified in the role.

[code]
SELECT a.role, a.owner || ‘.’ || a.table_name tbl,
a.column_name,
a.privilege,
a.grantable
FROM role_tab_privs a, dba_role_privs b
WHERE a.role = b.granted_role
AND b.grantee LIKE UPPER (USER)
[/code]

Obviously it requires that you have a catalog role that allows you access to:

  • role_tab_privs
  • dba_role_privs