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?

Answer 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;

Answer 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.

Answer 3

You can use below code to get all the privileges list from all users.

select * from dba_sys_privs 

Answer 4

Another useful resource:

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

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS

Answer 5

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, GRANTEE 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.