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)

As a SQL Developer user, I drop this into my User Defined Reports folder so it’s always handy.


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.