CODEWITHSUNDEEP
sqloracle-databasesql-grant
guerda
guerda

Reputation: 24049

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?

Upvotes: 129

Views: 706462

Answers (7)

Arash moradabadi
Arash moradabadi

Reputation: 365

To list all system privileges granted to current user(the user which is connected and owned the session), the following query would work:

select * from USER_SYS_PRIVS where USERNAME = <CURRENT_USER>;

The query should execute in current user session and username MUST include quotations. For example:

select * from USER_SYS_PRIVS where USERNAME = 'arash';

Upvotes: 0

Matteo Steccolini
Matteo Steccolini

Reputation: 1845

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 match case, so usually should be uppercase)

Interesting answers though.

Upvotes: 26

Pradeep Dewani
Pradeep Dewani

Reputation: 1

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

Upvotes: 0

Oleksii Cherkas
Oleksii Cherkas

Reputation: 351

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.

Upvotes: 25

Sujit
Sujit

Reputation: 51

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')
;

Upvotes: 5

DCookie
DCookie

Reputation: 43523

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 = <theUser>
UNION
SELECT PRIVILEGE 
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = <theUser>
 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 = <theUser>
 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 = <theUser>
 ORDER BY owner, table_name;

Upvotes: 173

Juris
Juris

Reputation: 1537

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.

Upvotes: 40

Related Questions