2012
04.03

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.

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)

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

  • role_tab_privs
  • dba_role_privs
  • Comments are closed.