Oracle: object user grants

Oracle is Oracle… It has good and bad things.
The bad of course… it’s closed and you’ll be crazy before getting info about how to do things.
And the good is that everythings is inside!! just at 1 query distance ;-)

Some days ago I was trying to look for grants given to a user by another user (WITH ADMIN OPTION).
I was logged with sys and I used “my” 2 wonderful querys for obtaining that info (copy/pasted from the internet),

• Direct grants:

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:

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;

Ressult? 0 rows! WTF!
I know there’s a plenty of it!!!

Searching a bunch of time for info about that, I didn’t find any info that helps me.
Then I thought that maybe the grants where displayed logged as the grantor user?
BINGO!

But why oracle does not shows it logged as sys?
Why don’t have a look a the perf view’s metadata?

SELECT DBMS_METADATA.GET_DDL('VIEW','USER_TAB_PRIVS','SYS') FROM DUAL ;

DBMS_METADATA.GET_DDL('VIEW','USER_TAB_PRIVS','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."USER_TAB_PRIVS" ("GRANTEE", "OWNER", "T
ABLE_NAME", "GRANTOR", "PRIVILEGE", "GRANTABLE", "HI
ERARCHY") AS
  select ue.name, u.name, o.name, ur.name, tpm.name,
       decode(mod(oa.option$,2), 1, 'YES', 'NO'),
       decode(bitand(oa.option$,2), 2, 'YES', 'NO')
from sys.objauth$ oa, sys."_CURRENT_EDITION_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 u.user# = o.owner#
  and oa.privilege# = tpm.privilege
  and userenv('SCHEMAID') in (oa.grantor#, oa.grantee#, o.owner#)

YEAH!

Let’s do some test querys at that tables! ;

SELECT * FROM sys.objauth$ WHERE ROWNUM <10 ;
SELECT * FROM sys.objauth$ WHERE COL# IS NULL AND ROWNUM <10 ;
SELECT * FROM sys."_CURRENT_EDITION_OBJ" WHERE ROWNUM <10 ;
SELECT O.NAME, OA.*
FROM    sys.objauth$ oa, 
        sys."_CURRENT_EDITION_OBJ" o
WHERE   OA.OBJ#=O.OBJ#
AND     OA.COL# IS NULL
AND ROWNUM<10 ;
SELECT O.NAME, OA.*
FROM    sys.objauth$ oa, 
        sys."_CURRENT_EDITION_OBJ" o
WHERE   OA.OBJ#=O.OBJ#
AND     OA.COL# IS NULL
AND     O.NAME LIKE '%TEST%'
AND ROWNUM<10 ;

Nice!

So let’s do a smarter query:

COL OBJECT_NAME FORMAT A35
COL OBJECT_TYPE FORMAT A25
COL OWNER FORMAT A20
COL GRANTOR FORMAT A20
COL GRANTEE FORMAT A20
SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, GRANTOR, GRANTEE
FROM (
    SELECT  OBJECT.NAME OBJECT_NAME,
            decode (OBJECT.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',
                    48, 'CONSUMER GROUP', 55, 'XML SCHEMA', 
                    56, 'JAVA DATA', 57, 'EDITION', 59, 'RULE',
                    60, 'CAPTURE', 61, 'APPLY', 62, 'EVALUATION CONTEXT',
                    66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 
                    69, 'WINDOW', 72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 
                    79, 'CHAIN', 81, 'FILE GROUP', 82, 'MINING MODEL', 
                    87, 'ASSEMBLY', 90, 'CREDENTIAL', 
                    92, 'CUBE DIMENSION', 93, 'CUBE', 
                    94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                    100, 'FILE WATCHER', 101, 'DESTINATION','UNDEFINED'
                    ) OBJECT_TYPE,
            U.NAME      OWNER, 
            UR.NAME     GRANTOR, 
            UE.NAME     GRANTEE
    FROM    sys.objauth$ oa, 
            sys."_CURRENT_EDITION_OBJ" OBJECT,
            sys.user$ U,
            sys.user$ UR,
            sys.user$ UE
    WHERE   OA.OBJ#=OBJECT.OBJ#
    AND     OA.COL# IS NULL
    AND     U.USER#=OBJECT.OWNER#
    and     oa.grantor# = ur.user#
    and     oa.grantee# = ue.user#
    )
WHERE GRANTOR='<USERNAME>'
;

The final WHERE (here matching GRANTOR), you’re able to limit the ressults by any of the selected fields:

• GRANTOR
• GRANTEE
• OWNER
• OBJECT_TYPE
• OBJECT_NAME

And why decoding the OBJECT_TYPE? Cause there’s NO TABLE inside oracle with a OBJECT_TYPE, OBJECT_NAME detailed list.
I’ve obtained the list by querying the metadata of SYS.ALL_OBJECTS ¬¬’

enjoy!