eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.AUDIT_UNIFIED_POLICIES

Source

View Text - Preformatted


select obj.name,
       NVL(pol.condition, 'NONE'),
       decode(pol.condition_eval, 1, 'STATEMENT',
                                  2, 'SESSION',
                                  3, 'INSTANCE', 'NONE'),
       spm.name,
       'SYSTEM PRIVILEGE',
       'NONE',
       'NONE',
       'NONE',
       decode(bitand(pol.type, 24), 8, 'NO', 16, 'YES', NULL)
from sys.obj$ obj, sys.aud_policy$ pol, sys.system_privilege_map spm
where obj.obj# = pol.policy# and
      bitand(pol.type, 1) = 1 and
      substr(pol.syspriv, -spm.privilege+1, 1) = 'S'
UNION all
select obj.name,
       NVL(pol.condition, 'NONE'),
       decode(pol.condition_eval, 1, 'STATEMENT',
                                  2, 'SESSION',
                                  3, 'INSTANCE', 'NONE'),
       asa.name,
       decode(asa.type, 4,  'STANDARD ACTION',
                        6,  'XS ACTION',
                        8,  'OLS ACTION',
                        10, 'DATAPUMP ACTION',
                        11, 'DIRECT_LOAD ACTION', 'NONE'),
       'NONE',
       'NONE',
       'NONE',
       decode(bitand(pol.type, 24), 8, 'NO', 16, 'YES')
from sys.obj$ obj, sys.aud_policy$ pol, sys.auditable_system_actions asa
where obj.obj# = pol.policy# and
      bitand(pol.type, 2) = 2 and
      ((asa.type = 4 and              /* selecting STANDARD ACTION options */
        substr(pol.sysactn, asa.action+1, 1) = 'S') or
       (asa.type = 6 and                    /* selecting XS ACTION options */
        substr(pol.sysactn,
               ((select max(action)+1 from sys.auditable_system_actions
                               where type = 4) + asa.action + 1), 1) = 'S') or
       (asa.type = 8 and                   /* selecting OLS ACTION options */
        substr(pol.sysactn,
               (((select max(action)+1 from sys.auditable_system_actions
                               where type = 4) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 6)) + asa.action + 1), 1) = 'S') or
       (asa.type = 10 and             /* selecting DATAPUMP ACTION options */
        substr(pol.sysactn,
               (((select max(action)+1 from sys.auditable_system_actions
                               where type = 4) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 6) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 8)) + asa.action + 1), 1) = 'S') or
       (asa.type = 11 and          /* selecting DIRECT_LOAD ACTION options */
       substr(pol.sysactn,
               (((select max(action)+1 from sys.auditable_system_actions
                               where type = 4) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 6) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 8) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 7) +
                 (select count(*)+1 from sys.auditable_system_actions
                               where type = 10)) + asa.action + 1), 1) = 'S'))
UNION all
select obj.name,
       NVL(pol.condition, 'NONE'),
       decode(pol.condition_eval, 1, 'STATEMENT',
                                  2, 'SESSION',
                                  3, 'INSTANCE', 'NONE'),
       aoa.name,
       'OBJECT ACTION',
       obj2.owner,
       obj2.object_name,
       obj2.object_type,
       decode(bitand(pol.type, 24), 8, 'NO', 16, 'YES')
from sys.obj$ obj, sys.aud_policy$ pol, sys.auditable_object_actions aoa,
     sys.aud_object_opt$ opt, sys.dba_objects obj2
where obj.obj# = pol.policy# and
      opt.policy# = pol.policy# and
      opt.object# = obj2.object_id and
      bitand(pol.type, 4) = 4 and          /* Audit policy has Object option */
      opt.type = 2 and                         /* Schema Object audit option */
      substr(opt.action#, aoa.action+1, 1) = 'S'
UNION all
select obj.name,
       NVL(pol.condition, 'NONE'),
       decode(pol.condition_eval, 1, 'STATEMENT',
                                  2, 'SESSION',
                                  3, 'INSTANCE', 'NONE'),
       u.name,
       'ROLE PRIVILEGE',
       'NONE',
       'NONE',
       'NONE',
       decode(bitand(pol.type, 24), 8, 'NO', 16, 'YES')
from sys.obj$ obj, sys.aud_policy$ pol, sys.user$ u, sys.aud_object_opt$ opt
where obj.obj# = pol.policy# and
      opt.policy# = pol.policy# and
      opt.object# = u.user# and
      bitand(pol.type, 32) = 32 and          /* Audit policy has Role option */
      opt.type = 1                                      /* Role audit option */
UNION all
select obj.name,
       NVL(pol.condition, 'NONE'),
       decode(pol.condition_eval, 1, 'STATEMENT',
                                  2, 'SESSION',
                                  3, 'INSTANCE', 'NONE'),
       'NONE',
       'NONE',
       'NONE',
       'NONE',
       'NONE',
       decode(bitand(pol.type, 24), 8, 'NO', 16, 'YES')
from sys.obj$ obj, sys.aud_policy$ pol
where obj.obj# = pol.policy# and pol.type = 0
UNION all
select obj.name,
       NVL(pol.condition, 'NONE'),
       decode(pol.condition_eval, 1, 'STATEMENT',
                                  2, 'SESSION',
                                  3, 'INSTANCE', 'NONE'),
       asa.name,
       decode(asa.type, 7, 'DV ACTION', 'NONE'),
       'NONE',
       opt.comp_info,
       decode(opt.type, 3, 'REALM',
                        4, 'RULE_SET',
                        5, 'FACTOR', 'NONE'),
       decode(bitand(pol.type, 24), 8, 'NO', 16, 'YES')
from sys.obj$ obj, sys.aud_policy$ pol, sys.aud_object_opt$ opt,
     sys.auditable_system_actions asa
where obj.obj# = pol.policy# and
      bitand(pol.type, 64) = 64 and /* Audit Policy has DV object policies */
      opt.policy# = pol.policy# and
      (asa.type = 7 and                       /* asa.type = 7 is DV action */
       opt.type in (3, 4, 5) and  /* object type must be DV : bug 17985613 */
       substr(opt.action#, asa.action+1, 1) = 'S')

View Text - HTML Formatted

SELECT OBJ.NAME
, NVL(POL.CONDITION
, 'NONE')
, DECODE(POL.CONDITION_EVAL
, 1
, 'STATEMENT'
, 2
, 'SESSION'
, 3
, 'INSTANCE'
, 'NONE')
, SPM.NAME
, 'SYSTEM PRIVILEGE'
, 'NONE'
, 'NONE'
, 'NONE'
, DECODE(BITAND(POL.TYPE
, 24)
, 8
, 'NO'
, 16
, 'YES'
, NULL) FROM SYS.OBJ$ OBJ
, SYS.AUD_POLICY$ POL
, SYS.SYSTEM_PRIVILEGE_MAP SPM WHERE OBJ.OBJ# = POL.POLICY# AND BITAND(POL.TYPE
, 1) = 1 AND SUBSTR(POL.SYSPRIV
, -SPM.PRIVILEGE+1
, 1) = 'S' UNION ALL SELECT OBJ.NAME
, NVL(POL.CONDITION
, 'NONE')
, DECODE(POL.CONDITION_EVAL
, 1
, 'STATEMENT'
, 2
, 'SESSION'
, 3
, 'INSTANCE'
, 'NONE')
, ASA.NAME
, DECODE(ASA.TYPE
, 4
, 'STANDARD ACTION'
, 6
, 'XS ACTION'
, 8
, 'OLS ACTION'
, 10
, 'DATAPUMP ACTION'
, 11
, 'DIRECT_LOAD ACTION'
, 'NONE')
, 'NONE'
, 'NONE'
, 'NONE'
, DECODE(BITAND(POL.TYPE
, 24)
, 8
, 'NO'
, 16
, 'YES') FROM SYS.OBJ$ OBJ
, SYS.AUD_POLICY$ POL
, SYS.AUDITABLE_SYSTEM_ACTIONS ASA WHERE OBJ.OBJ# = POL.POLICY# AND BITAND(POL.TYPE
, 2) = 2 AND ((ASA.TYPE = 4
AND /* SELECTING STANDARD ACTION OPTIONS */ SUBSTR(POL.SYSACTN
, ASA.ACTION+1
, 1) = 'S') OR (ASA.TYPE = 6
AND /* SELECTING XS ACTION OPTIONS */ SUBSTR(POL.SYSACTN
, ((SELECT MAX(ACTION)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 4) + ASA.ACTION + 1)
, 1) = 'S') OR (ASA.TYPE = 8
AND /* SELECTING OLS ACTION OPTIONS */ SUBSTR(POL.SYSACTN
, (((SELECT MAX(ACTION)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 4) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 6)) + ASA.ACTION + 1)
, 1) = 'S') OR (ASA.TYPE = 10
AND /* SELECTING DATAPUMP ACTION OPTIONS */ SUBSTR(POL.SYSACTN
, (((SELECT MAX(ACTION)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 4) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 6) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 8)) + ASA.ACTION + 1)
, 1) = 'S') OR (ASA.TYPE = 11
AND /* SELECTING DIRECT_LOAD ACTION OPTIONS */ SUBSTR(POL.SYSACTN
, (((SELECT MAX(ACTION)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 4) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 6) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 8) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 7) + (SELECT COUNT(*)+1
FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 10)) + ASA.ACTION + 1)
, 1) = 'S')) UNION ALL SELECT OBJ.NAME
, NVL(POL.CONDITION
, 'NONE')
, DECODE(POL.CONDITION_EVAL
, 1
, 'STATEMENT'
, 2
, 'SESSION'
, 3
, 'INSTANCE'
, 'NONE')
, AOA.NAME
, 'OBJECT ACTION'
, OBJ2.OWNER
, OBJ2.OBJECT_NAME
, OBJ2.OBJECT_TYPE
, DECODE(BITAND(POL.TYPE
, 24)
, 8
, 'NO'
, 16
, 'YES') FROM SYS.OBJ$ OBJ
, SYS.AUD_POLICY$ POL
, SYS.AUDITABLE_OBJECT_ACTIONS AOA
, SYS.AUD_OBJECT_OPT$ OPT
, SYS.DBA_OBJECTS OBJ2 WHERE OBJ.OBJ# = POL.POLICY# AND OPT.POLICY# = POL.POLICY# AND OPT.OBJECT# = OBJ2.OBJECT_ID AND BITAND(POL.TYPE
, 4) = 4
AND /* AUDIT POLICY HAS OBJECT OPTION */ OPT.TYPE = 2
AND /* SCHEMA OBJECT AUDIT OPTION */ SUBSTR(OPT.ACTION#
, AOA.ACTION+1
, 1) = 'S' UNION ALL SELECT OBJ.NAME
, NVL(POL.CONDITION
, 'NONE')
, DECODE(POL.CONDITION_EVAL
, 1
, 'STATEMENT'
, 2
, 'SESSION'
, 3
, 'INSTANCE'
, 'NONE')
, U.NAME
, 'ROLE PRIVILEGE'
, 'NONE'
, 'NONE'
, 'NONE'
, DECODE(BITAND(POL.TYPE
, 24)
, 8
, 'NO'
, 16
, 'YES') FROM SYS.OBJ$ OBJ
, SYS.AUD_POLICY$ POL
, SYS.USER$ U
, SYS.AUD_OBJECT_OPT$ OPT WHERE OBJ.OBJ# = POL.POLICY# AND OPT.POLICY# = POL.POLICY# AND OPT.OBJECT# = U.USER# AND BITAND(POL.TYPE
, 32) = 32
AND /* AUDIT POLICY HAS ROLE OPTION */ OPT.TYPE = 1 /* ROLE AUDIT OPTION */ UNION ALL SELECT OBJ.NAME
, NVL(POL.CONDITION
, 'NONE')
, DECODE(POL.CONDITION_EVAL
, 1
, 'STATEMENT'
, 2
, 'SESSION'
, 3
, 'INSTANCE'
, 'NONE')
, 'NONE'
, 'NONE'
, 'NONE'
, 'NONE'
, 'NONE'
, DECODE(BITAND(POL.TYPE
, 24)
, 8
, 'NO'
, 16
, 'YES') FROM SYS.OBJ$ OBJ
, SYS.AUD_POLICY$ POL WHERE OBJ.OBJ# = POL.POLICY#
AND POL.TYPE = 0 UNION ALL SELECT OBJ.NAME
, NVL(POL.CONDITION
, 'NONE')
, DECODE(POL.CONDITION_EVAL
, 1
, 'STATEMENT'
, 2
, 'SESSION'
, 3
, 'INSTANCE'
, 'NONE')
, ASA.NAME
, DECODE(ASA.TYPE
, 7
, 'DV ACTION'
, 'NONE')
, 'NONE'
, OPT.COMP_INFO
, DECODE(OPT.TYPE
, 3
, 'REALM'
, 4
, 'RULE_SET'
, 5
, 'FACTOR'
, 'NONE')
, DECODE(BITAND(POL.TYPE
, 24)
, 8
, 'NO'
, 16
, 'YES') FROM SYS.OBJ$ OBJ
, SYS.AUD_POLICY$ POL
, SYS.AUD_OBJECT_OPT$ OPT
, SYS.AUDITABLE_SYSTEM_ACTIONS ASA WHERE OBJ.OBJ# = POL.POLICY# AND BITAND(POL.TYPE
, 64) = 64
AND /* AUDIT POLICY HAS DV OBJECT POLICIES */ OPT.POLICY# = POL.POLICY# AND (ASA.TYPE = 7
AND /* ASA.TYPE = 7 IS DV ACTION */ OPT.TYPE IN (3
, 4
, 5)
AND /* OBJECT TYPE MUST BE DV : BUG 17985613 */ SUBSTR(OPT.ACTION#
, ASA.ACTION+1
, 1) = 'S')