VIEW: SYS.DBA_USED_PRIVS

Source

View Text - Preformatted

select capture, run_seq#, os_user, host, module, username, used_role, sys_priv, obj_priv, user_priv, object_owner, object_name, object_type, column_name, option$, sys.string_to_grantpath(path)
from
(select unique cap.name capture,
              priv.run_seq# run_seq#,
              priv.os_user os_user,
              priv.host host,
              priv.module module,
              u.name username,
              (select name from sys.user$ where priv.role# != 0
               and priv.role#=user#) used_role,
              (select m.name from sys.system_privilege_map m
               where priv.syspriv# = -m.privilege) sys_priv,
              (select m.name from sys.table_privilege_map m
               where priv.objpriv# = m.privilege and priv.syspriv# = 0)
               obj_priv,
              decode(priv.userpriv#,
               0, 'INHERIT PRIVILEGES', 1, 'TRANSLATE SQL', NULL) user_priv,
              decode(priv.userpriv#, 255,
                     (select u.name from sys.obj$ o, sys.user$ u
                     where o.obj#=priv.obj# and o.owner#=u.user#), NULL) object_owner,
              decode(priv.userpriv#, 255, /* not user privilege */
                     (select o.name from sys.obj$ o, sys.user$ u
                      where o.obj#=priv.obj# and o.owner#=u.user#),
                     (select u.name from sys.user$ u where u.user# = priv.obj#)) object_name,
              decode(priv.userpriv#, 255, /* not user privilege */
                     (select d.object_type from dba_objects d
                      where d.object_id = priv.obj#), 'USER') object_type,
             (select c.name from sys.col$ c where priv.col# !=0 and
              obj#=priv.obj# and col#=priv.col#) column_name,
             priv.option$ option$,
             sys.grantpath_to_string(pa.path) path
from (select * from sys.captured_priv$ p where p.syspriv# != 0 or /* syspriv */
     (p.objpriv# != 255 and                       /* objpriv used and exists */
       exists (select name from sys.obj$ o where p.obj# = o.obj#)) or
     (p.userpriv# != 255  and                    /* userpriv used and exists */
       exists (select name from sys.user$ u where u.user#=p.obj#)) or
     (p.objpriv# = 255 and p.userpriv# = 255 and/* only role used and exists */
       exists (select name from sys.user$ where p.role# != 0 and
       p.role# = user#))) priv,
     sys.priv_used_path$ pa, sys.priv_capture$ cap, sys.user$ u
where priv.capture = cap.id# and u.user#=priv.user# and priv.id# =pa.id# and priv.capture = pa.capture)

View Text - HTML Formatted

SELECT CAPTURE
, RUN_SEQ#
, OS_USER
, HOST
, MODULE
, USERNAME
, USED_ROLE
, SYS_PRIV
, OBJ_PRIV
, USER_PRIV
, OBJECT_OWNER
, OBJECT_NAME
, OBJECT_TYPE
, COLUMN_NAME
, OPTION$
, SYS.STRING_TO_GRANTPATH(PATH) FROM (SELECT UNIQUE CAP.NAME CAPTURE
, PRIV.RUN_SEQ# RUN_SEQ#
, PRIV.OS_USER OS_USER
, PRIV.HOST HOST
, PRIV.MODULE MODULE
, U.NAME USERNAME
, (SELECT NAME
FROM SYS.USER$
WHERE PRIV.ROLE# != 0
AND PRIV.ROLE#=USER#) USED_ROLE
, (SELECT M.NAME
FROM SYS.SYSTEM_PRIVILEGE_MAP M
WHERE PRIV.SYSPRIV# = -M.PRIVILEGE) SYS_PRIV
, (SELECT M.NAME
FROM SYS.TABLE_PRIVILEGE_MAP M
WHERE PRIV.OBJPRIV# = M.PRIVILEGE
AND PRIV.SYSPRIV# = 0) OBJ_PRIV
, DECODE(PRIV.USERPRIV#
, 0
, 'INHERIT PRIVILEGES'
, 1
, 'TRANSLATE SQL'
, NULL) USER_PRIV
, DECODE(PRIV.USERPRIV#
, 255
, (SELECT U.NAME
FROM SYS.OBJ$ O
, SYS.USER$ U
WHERE O.OBJ#=PRIV.OBJ#
AND O.OWNER#=U.USER#)
, NULL) OBJECT_OWNER
, DECODE(PRIV.USERPRIV#
, 255
, /* NOT USER PRIVILEGE */ (SELECT O.NAME
FROM SYS.OBJ$ O
, SYS.USER$ U
WHERE O.OBJ#=PRIV.OBJ#
AND O.OWNER#=U.USER#)
, (SELECT U.NAME
FROM SYS.USER$ U
WHERE U.USER# = PRIV.OBJ#)) OBJECT_NAME
, DECODE(PRIV.USERPRIV#
, 255
, /* NOT USER PRIVILEGE */ (SELECT D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID = PRIV.OBJ#)
, 'USER') OBJECT_TYPE
, (SELECT C.NAME
FROM SYS.COL$ C
WHERE PRIV.COL# !=0 AND OBJ#=PRIV.OBJ#
AND COL#=PRIV.COL#) COLUMN_NAME
, PRIV.OPTION$ OPTION$
, SYS.GRANTPATH_TO_STRING(PA.PATH) PATH FROM (SELECT *
FROM SYS.CAPTURED_PRIV$ P
WHERE P.SYSPRIV# != 0 OR /* SYSPRIV */ (P.OBJPRIV# != 255
AND /* OBJPRIV USED
AND EXISTS */ EXISTS (SELECT NAME
FROM SYS.OBJ$ O
WHERE P.OBJ# = O.OBJ#)) OR (P.USERPRIV# != 255
AND /* USERPRIV USED
AND EXISTS */ EXISTS (SELECT NAME
FROM SYS.USER$ U
WHERE U.USER#=P.OBJ#)) OR (P.OBJPRIV# = 255
AND P.USERPRIV# = 255 AND/* ONLY ROLE USED
AND EXISTS */ EXISTS (SELECT NAME
FROM SYS.USER$
WHERE P.ROLE# != 0 AND P.ROLE# = USER#))) PRIV
, SYS.PRIV_USED_PATH$ PA
, SYS.PRIV_CAPTURE$ CAP
, SYS.USER$ U WHERE PRIV.CAPTURE = CAP.ID#
AND U.USER#=PRIV.USER#
AND PRIV.ID# =PA.ID#
AND PRIV.CAPTURE = PA.CAPTURE)