[sql] How can I list ALL grants a user received?

I need to see all grants on an Oracle DB.

I used the TOAD feature to compare schemas but it does not shows temptable grants etc. so there's my question:

How can I list all grants on a Oracle DB?

Following query can be used to get all privileges of one user .. Just provide user name in first query and you will get all privileges to that

WITH users AS (SELECT 'SCHEMA_USER' usr FROM dual), Roles AS (SELECT granted_role FROM dba_role_privs rp JOIN users ON rp.GRANTEE = users.usr UNION SELECT granted_role FROM role_role_privs WHERE role IN (SELECT granted_role FROM dba_role_privs rp JOIN users ON rp.GRANTEE = users.usr)), tab_privilage AS (SELECT OWNER, TABLE_NAME, PRIVILEGE FROM role_tab_privs rtp JOIN roles r ON rtp.role = r.granted_role UNION SELECT OWNER, TABLE_NAME, PRIVILEGE FROM Dba_Tab_Privs dtp JOIN Users ON dtp.grantee = users.usr), sys_privileges AS (SELECT privilege FROM dba_sys_privs dsp JOIN users ON dsp.grantee = users.usr) SELECT * FROM tab_privilage ORDER BY owner, table_name --SELECT * FROM sys_privileges

select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||RP.GRANTEE
from DBA_ROLE_PRIVS RP join ROLE_TAB_PRIVS RTP 
on (RP.GRANTED_ROLE = RTP.role)  
where (OWNER in ('YOUR USER') --Change User Name
   OR RP.GRANTEE in ('YOUR USER')) --Change User Name
and RP.GRANTEE not in ('SYS', 'SYSTEM')
;

Assuming you want to list grants on all objects a particular user has received:

select * from all_tab_privs_recd where grantee = 'your user'

This will not return objects owned by the user. If you need those, use all_tab_privs view instead.

Sorry guys, but selecting from all_tab_privs_recd where grantee = 'your user' will not give any output except public grants and current user grants if you run the select from a different (let us say, SYS) user. As documentation says,

ALL_TAB_PRIVS_RECD describes the following types of grants:

Object grants for which the current user is the grantee
Object grants for which an enabled role or PUBLIC is the grantee

So, if you're a DBA and want to list all object grants for a particular (not SYS itself) user, you can't use that system view.

In this case, you must perform a more complex query. Here is one taken (traced) from TOAD to select all object grants for a particular user:

select tpm.name privilege,
       decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
       ue.name grantee,
       ur.name grantor,
       u.name owner,
       decode(o.TYPE#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                       4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                       7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                       11, 'PACKAGE BODY', 12, 'TRIGGER',
                       13, 'TYPE', 14, 'TYPE BODY',
                       19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                       22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                       28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                       32, 'INDEXTYPE', 33, 'OPERATOR',
                       34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                       40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                       42, 'MATERIALIZED VIEW',
                       43, 'DIMENSION',
                       44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                       66, 'JOB', 67, 'PROGRAM', 74, 'SCHEDULE',
                       48, 'CONSUMER GROUP',
                       51, 'SUBSCRIPTION', 52, 'LOCATION',
                       55, 'XML SCHEMA', 56, 'JAVA DATA',
                       57, 'EDITION', 59, 'RULE',
                       62, 'EVALUATION CONTEXT',
                       'UNDEFINED') object_type,
       o.name object_name,
       '' column_name
        from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
             table_privilege_map tpm
        where oa.obj# = o.obj#
          and oa.grantor# = ur.user#
          and oa.grantee# = ue.user#
          and oa.col# is null
          and oa.privilege# = tpm.privilege
          and u.user# = o.owner#
          and o.TYPE# in (2, 4, 6, 9, 7, 8, 42, 23, 22, 13, 33, 32, 66, 67, 74, 57)
  and ue.name = 'your user'
  and bitand (o.flags, 128) = 0
union all -- column level grants
select tpm.name privilege,
       decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
       ue.name grantee,
       ur.name grantor,
       u.name owner,
       decode(o.TYPE#, 2, 'TABLE', 4, 'VIEW', 42, 'MATERIALIZED VIEW') object_type,
       o.name object_name,
       c.name column_name
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
     sys.col$ c, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and oa.obj# = c.obj#
  and oa.col# = c.col#
  and bitand(c.property, 32) = 0 /* not hidden column */
  and oa.col# is not null
  and oa.privilege# = tpm.privilege
  and u.user# = o.owner#
  and o.TYPE# in (2, 4, 42)
  and ue.name = 'your user'
  and bitand (o.flags, 128) = 0;

This will list all object grants (including column grants) for your (specified) user. If you don't want column level grants then delete all part of the select beginning with 'union' clause.

UPD: Studying the documentation I found another view that lists all grants in much simpler way:

select * from DBA_TAB_PRIVS where grantee = 'your user';

Bear in mind that there's no DBA_TAB_PRIVS_RECD view in Oracle.

The most comprehensive and reliable method I know is still by using DBMS_METADATA:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', :username ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', :username ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', :username ) from dual;

(username must be written all uppercase)

Interesting answers though.