How to show all privileges from a user in oracle?

Question

Can someone please tell me how to show all privileges/rules from a specific user in the sql-console?

Solution 1

You can try these below views.

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

Those views only show the privileges granted directly to the user. Finding all the privileges, including those granted indirectly through roles, requires more complicated recursive SQL statements:

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

Solution 2

There are various scripts floating around that will do that depending on how crazy you want to get. I would personally use Pete Finnigan's find_all_privs script.

If you want to write it yourself, the query gets rather challenging. Users can be granted system privileges which are visible in DBA_SYS_PRIVS. They can be granted object privileges which are visible in DBA_TAB_PRIVS. And they can be granted roles which are visible in DBA_ROLE_PRIVS (roles can be default or non-default and can require a password as well, so just because a user has been granted a role doesn't mean that the user can necessarily use the privileges he acquired through the role by default). But those roles can, in turn, be granted system privileges, object privileges, and additional roles which can be viewed by looking at ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, and ROLE_ROLE_PRIVS. Pete's script walks through those relationships to show all the privileges that end up flowing to a user.

Solution 3

Another useful resource:

http://psoug.org/reference/roles.html

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS

Solution 4

While Raviteja Vutukuri's answer works and is quick to put together, it's not particularly flexible for varying the filters and doesn't help too much if you're looking to do something programmatically. So I put together my own query:

SELECT
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME,
    LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lists the sources of the permission
    MAX(ADMIN_OR_GRANT_OPT) AS ADMIN_OR_GRANT_OPT, -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
    MAX(HIERARCHY_OPT) AS HIERARCHY_OPT -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
FROM (
    -- Gets all roles a user has, even inherited ones
    WITH ALL_ROLES_FOR_USER AS (
        SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
        FROM DBA_ROLE_PRIVS
        CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
    )
    SELECT
        PRIVILEGE,
        OBJ_OWNER,
        OBJ_NAME,
        USERNAME,
        REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
        ADMIN_OR_GRANT_OPT,
        HIERARCHY_OPT
    FROM (
        -- System privileges granted directly to users
        SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
        FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- System privileges granted users through roles
        SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
        FROM DBA_SYS_PRIVS
        JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
        UNION ALL
        -- Object privileges granted directly to users
        SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, GRANTABLE, HIERARCHY
        FROM DBA_TAB_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- Object privileges granted users through roles
        SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, ALL_ROLES_FOR_USER.GRANTED_ROLE AS GRANT_TARGET, GRANTABLE, HIERARCHY
        FROM DBA_TAB_PRIVS
        JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
    ) ALL_USER_PRIVS
    -- Adjust your filter here
    WHERE USERNAME = 'USER_NAME'
) DISTINCT_USER_PRIVS
GROUP BY
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME
;

Advantages:

• I easily can filter by a lot of different pieces of information, like the object, the privilege, whether it's through a particular role, etc. just by changing that one WHERE clause.
• It's a single query, meaning I don't have to mentally compose the results together.
• It resolves the issue of whether they can grant the privilege or not and whether it includes the privileges for subobjects (the "hierarchical" part) across differences sources of the privilege.
• It's easy to see everything I need to do to revoke the privilege, since it lists all the sources of the privilege.
• It combines table and system privileges into a single coherent view, allowing us to list all the privileges of a user in one fell swoop.
• It's a query, not a function that spews all this out to DBMS_OUTPUT or something (compared to Pete Finnigan's linked script). This makes it useful for programmatic use and for exporting.
• The filter is not repeated; it only appears once. This makes it easier to change.
• The subquery can easily be pulled out if you need to examine it by each individual GRANT.

Solution 5

More simpler single query oracle version.

WITH data 
     AS (SELECT granted_role 
         FROM   dba_role_privs 
         CONNECT BY PRIOR granted_role = grantee 
         START WITH grantee = '&USER') 
SELECT 'SYSTEM'     typ, 
       grantee      grantee, 
       privilege    priv, 
       admin_option ad, 
       '--'         tabnm, 
       '--'         colnm, 
       '--'         owner 
FROM   dba_sys_privs 
WHERE  grantee = '&USER' 
        OR grantee IN (SELECT granted_role 
                       FROM   data) 
UNION 
SELECT 'TABLE'    typ, 
       grantee    grantee, 
       privilege  priv, 
       grantable  ad, 
       table_name tabnm, 
       '--'       colnm, 
       owner      owner 
FROM   dba_tab_privs 
WHERE  grantee = '&USER' 
        OR grantee IN (SELECT granted_role 
                       FROM   data) 
ORDER  BY 1; 

View more solutions

Comments

[I.Tyger]

The script is awesome just checked it out

[Jan]

You need privileges to the UTL_FILE package or else you get an error when running Pete Finnigan's script: "identifier 'UTL_FILE' must be declared". You can connect as sys with roll sysdba through SQL Developer and then it will work or grant yourself execute privileges to this package using: grant execute on UTL_FILE to <user>;

[vapcguy]

And for those of us without SYS privileges and just want to look at the privileges of our own account, the script is utterly worthless. I don't have access to UTL_FILE nor to DBA_SYS_PRIVS and the other DBA and SYS areas the script looks at.

[vapcguy]

Except if you don't have DBA or SYS roles, and you just want to find the privileges of your own account.

[jpmc26]

I don't think ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, and ROLE_ROLE_PRIVS needs to be examined. The docs indicate they're for the current user.

[jpmc26]

Some TODOs for myself: 1. Add indicator if user can grant the privilege by granting a role to another user. 2. Figure out how to do this for current user without DBA privileges. Probably involves USER_SYS_PRIVS (directly granted system privileges), USER_TAB_PRIVS (directly granted object privs) USER_ROLE_PRIVS (user's directly granted roles), ROLE_ROLE_PRIVS (for getting inherited roles), ROLE_SYS_PRIVS (system privs through roles) and ROLE_TAB_PRIVS (object privs through roles). Ugh. Oracle is so complicated.

[jpmc26]

This does not list all the privileges. As demonstrated by several other answers that preceded yours by years, it omits table privileges and all privileges granted through roles.

[Nikhil Bansal]

If anyone has a copy of these scripts, can they post here or somewhere a little more evergreen like a gist? The site is down.

[Justin Cave]

@MichaelThompson - The site is up. At the top of the site, one of the restrictions is "The scripts cannot be posted / published / hosted or whatever anywhere else..." The gist of what the script is doing is spelled out in the second paragraph of my answer. The script itself is a couple hundred lines of code which makes it a pain to re-create.

[Vic VKh]

Much more clear code then the previous one.