How can I list ALL grants a user received

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you want more than just direct table grants  e g   grants via roles  system privileges such as select any table  etc    here are some additional queries   System privileges for a user   SELECT PRIVILEGE   FROM sys dba sys privs  WHERE grantee    lt theUser gt  UNION SELECT PRIVILEGE    FROM dba role privs rp JOIN role sys privs rsp ON  rp granted role   rsp role   WHERE rp grantee    lt theUser gt   ORDER BY 1    Direct grants to tables views   SELECT owner  table name  select priv  insert priv  delete priv  update priv  references priv  alter priv  index priv    FROM table privileges  WHERE grantee    lt theUser gt   ORDER BY owner  table name    Indirect grants to tables views   SELECT DISTINCT owner  table name  PRIVILEGE    FROM dba role privs rp JOIN role tab privs rtp ON  rp granted role   rtp role   WHERE rp grantee    lt theUser gt   ORDER BY owner  table name

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to grant