You Don’t Have to be Posh to be Privileged

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.

About these ads

2 thoughts on “You Don’t Have to be Posh to be Privileged

    • 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 :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s