Working in multiple environments, I sometimes get tripped up by the RDBMS telling me a program unit doesn’t exist when I know it does.
What with system privileges either being granted directly, or via roles, I find the following query really useful in working out just what privileges I’ve got on an environment.
SELECT distinct privilege FROM dba_sys_privs WHERE grantee = USER OR grantee IN ( SELECT granted_role FROM dba_role_privs START WITH grantee = USER CONNECT BY PRIOR granted_role = grantee) ORDER BY 1
As a SQL Developer user, I drop this into my User Defined Reports folder so it’s always handy.
Assumes you have access to DBA_* views.
I like
SELECT* FROM SESSION_PRIVS;
PS. Only just found your blog., but now you are in my Google Reader
Larry Wall ( inventor of Perl) is often quoted as saying “there’s more than one way to do it”. I think I should really come up with something snappy like that…except in my case it would be “there’s always a better way to do it”.
Yep SESSION_PRIVS gives the same result with less typing and doesn’t rely on you having access to the DBA or ALL version of these views. Nice one