VIEW: SYS.KU$_AUDIT_POLICY_VIEW

Source

View Text - Preformatted

select
  policy#,
  value(o),
  type,
  condition,
  condition_eval,
  cast(multiset(select spm.privilege, spm.name, spm.property
                from sys.system_privilege_map spm
                where bitand(pol.type, 1) = 1 and
                      substr(pol.syspriv, -spm.privilege+1, 1) = 'S'
                       ) as ku$_audit_sys_priv_list_t
      ),
  cast(multiset(select asa.action, asa.name
                from sys.auditable_system_actions asa
                where bitand(pol.type, 2) = 2 and
                      asa.type = 4 and
                      substr(pol.sysactn, asa.action+1, 1) = 'S'
                       ) as ku$_audit_act_list_t
      ),
  cast(multiset(select ata.action, ata.name
                from sys.auditable_system_actions ata
                where bitand(pol.type, 2) = 2 and
                      ata.type = 6 and
                      substr(pol.sysactn, ((select max(action)+1 from
                        sys.auditable_system_actions where type = 4) +
                          ata.action+1), 1) = 'S'
                       ) as ku$_audit_act_list_t
      ),
  cast(multiset(select ata.action, ata.name
                from sys.auditable_system_actions ata
                where bitand(pol.type, 2) = 2 and
                      ata.type = 8 and
                      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) +
                         ata.action+1), 1) = 'S'
                       ) as ku$_audit_act_list_t
      ),
  cast(multiset(
      select aoa.action, value(ao) , aoa.name
      from sys.auditable_object_actions aoa, sys.aud_object_opt$ opt,
           sys.ku$_schemaobj_view ao
      where opt.policy# = pol.policy# and
        opt.object# = ao.obj_num 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'
                       ) as ku$_auditp_obj_list_t
      ),
  cast(multiset(
      select u.user#, u.name
      from sys.aud_object_opt$ opt, sys.user$ u
      where 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 */
                       ) as ku$_audit_pol_role_list_t
      )
from sys.aud_policy$ pol, ku$_schemaobj_view o
where o.obj_num=pol.policy#

View Text - HTML Formatted

SELECT POLICY#
, VALUE(O)
, TYPE
, CONDITION
, CONDITION_EVAL
, CAST(MULTISET(SELECT SPM.PRIVILEGE
, SPM.NAME
, SPM.PROPERTY
FROM SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE BITAND(POL.TYPE
, 1) = 1 AND SUBSTR(POL.SYSPRIV
, -SPM.PRIVILEGE+1
, 1) = 'S' ) AS KU$_AUDIT_SYS_PRIV_LIST_T )
, CAST(MULTISET(SELECT ASA.ACTION
, ASA.NAME
FROM SYS.AUDITABLE_SYSTEM_ACTIONS ASA
WHERE BITAND(POL.TYPE
, 2) = 2 AND ASA.TYPE = 4 AND SUBSTR(POL.SYSACTN
, ASA.ACTION+1
, 1) = 'S' ) AS KU$_AUDIT_ACT_LIST_T )
, CAST(MULTISET(SELECT ATA.ACTION
, ATA.NAME
FROM SYS.AUDITABLE_SYSTEM_ACTIONS ATA
WHERE BITAND(POL.TYPE
, 2) = 2 AND ATA.TYPE = 6 AND SUBSTR(POL.SYSACTN
, ((SELECT MAX(ACTION)+1 FROM SYS.AUDITABLE_SYSTEM_ACTIONS
WHERE TYPE = 4) + ATA.ACTION+1)
, 1) = 'S' ) AS KU$_AUDIT_ACT_LIST_T )
, CAST(MULTISET(SELECT ATA.ACTION
, ATA.NAME
FROM SYS.AUDITABLE_SYSTEM_ACTIONS ATA
WHERE BITAND(POL.TYPE
, 2) = 2 AND ATA.TYPE = 8 AND 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) + ATA.ACTION+1)
, 1) = 'S' ) AS KU$_AUDIT_ACT_LIST_T )
, CAST(MULTISET( SELECT AOA.ACTION
, VALUE(AO)
, AOA.NAME
FROM SYS.AUDITABLE_OBJECT_ACTIONS AOA
, SYS.AUD_OBJECT_OPT$ OPT
, SYS.KU$_SCHEMAOBJ_VIEW AO
WHERE OPT.POLICY# = POL.POLICY# AND OPT.OBJECT# = AO.OBJ_NUM 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' ) AS KU$_AUDITP_OBJ_LIST_T )
, CAST(MULTISET( SELECT U.USER#
, U.NAME
FROM SYS.AUD_OBJECT_OPT$ OPT
, SYS.USER$ U
WHERE 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 */ ) AS KU$_AUDIT_POL_ROLE_LIST_T ) FROM SYS.AUD_POLICY$ POL
, KU$_SCHEMAOBJ_VIEW O WHERE O.OBJ_NUM=POL.POLICY#