1 PROCEDURE dbms_feature_unified_audit
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage VARCHAR2(1000);
7 uniaud_linkon NUMBER;
8 unified_policies NUMBER;
9 unified_policies_enb NUMBER;
10 unified_policies_cond NUMBER;
11 unified_policies_dv NUMBER;
12 unified_policies_ols NUMBER;
13 unified_policies_xs NUMBER;
14 unified_policies_dp NUMBER;
15 unified_contexts NUMBER;
16 BEGIN
17
18 -- Initialize
19 feature_boolean := 0;
20 aux_count := 0;
21 feature_info := NULL;
22 unified_policies := 0;
23 unified_policies_enb := 0;
24 unified_policies_cond := 0;
25 unified_policies_dv := 0;
26 unified_policies_ols := 0;
27 unified_policies_xs := 0;
28 unified_policies_dp := 0;
29 unified_contexts := 0;
30
31 -- Check if 'uniaud_on' is linked
32 select count(*) into uniaud_linkon from v$option
33 where parameter like '%Unified Auditing%' and value = 'TRUE';
34
35 -- Get number of Unified Audit policies created in the database
36 select count(*) into unified_policies from aud_policy$;
37
38 -- Get number of Unified Audit policies enabled in the database
39 select count(distinct policy#) into unified_policies_enb from audit_ng$;
40
41 -- Get number of Unified Audit policies with condition
42 select count(*) into unified_policies_cond from aud_policy$
43 where condition is NOT NULL;
44
45 -- Get number of Unified Audit policies for each componenet
46 FOR item IN (SELECT audit_option_type, count(distinct policy_name) pol_cnt
47 FROM AUDIT_UNIFIED_POLICIES group by audit_option_type)
48 LOOP
49 IF (item.audit_option_type LIKE 'DV%') THEN
50 unified_policies_dv := item.pol_cnt;
51 ELSIF (item.audit_option_type LIKE 'OLS%') THEN
52 unified_policies_ols := item.pol_cnt;
53 ELSIF (item.audit_option_type LIKE 'XS%') THEN
54 unified_policies_xs := item.pol_cnt;
55 ELSIF (item.audit_option_type LIKE 'DATAPUMP%') THEN
56 unified_policies_dp := item.pol_cnt;
57 END IF;
58 END LOOP;
59
60 -- Get number of contexts enabled for audit
61 select count(*) into unified_contexts from aud_context$;
62
63 -- If 'uniaud_on' is linked, then Unified audit feature is enabled.
64 -- Else if atleast a single Unified audit policy is enabled,
65 -- then Unified audit feature is enabled.
66 if ((uniaud_linkon > 0) OR (unified_policies_enb > 0)) then
67 feature_boolean := 1;
68 end if;
69
70 feature_usage := 'Number of Unified Audit policies=' ||
71 to_char(unified_policies) || '; ' ||
72 'Number of Enabled Unified Audit policies=' ||
73 to_char(unified_policies_enb) || '; ' ||
74 'Number of Unified Audit policies with condition=' ||
75 to_char(unified_policies_cond) || '; ' ||
76 'Number of Unified Audit policies on DV=' ||
77 to_char(unified_policies_dv) || '; ' ||
78 'Number of Unified Audit policies on OLS=' ||
79 to_char(unified_policies_ols) || '; ' ||
80 'Number of Unified Audit policies on XS=' ||
81 to_char(unified_policies_xs) || '; ' ||
82 'Number of Unified Audit policies on DATAPUMP=' ||
83 to_char(unified_policies_dp) || '; ' ||
84 'Number of Enabled Unified Audit Contexts=' ||
85 to_char(unified_contexts);
86 feature_info := to_clob(feature_usage);
87
88 END dbms_feature_unified_audit;