PROCEDURE: SYS.DBMS_FEATURE_FGA_AUDIT

Source

1 PROCEDURE dbms_feature_fga_audit
2      ( feature_boolean  OUT  NUMBER,
3        aux_count        OUT  NUMBER,
4        feature_info     OUT  CLOB)
5 AS
6    feature_usage           VARCHAR2(1000);
7    fga_policies_total      NUMBER;
8    fga_policies_enabled    NUMBER;
9    fga_policies_column     NUMBER;
10    fga_policies_condition  NUMBER;
11    fga_policies_handler    NUMBER;
12    fga_policies_DB_trail   NUMBER;
13    fga_policies_XML_trail  NUMBER;
14 BEGIN
15 
16   -- Initialize
17   feature_boolean         := 0;
18   aux_count               := 0;
19   feature_info            := NULL;
20   fga_policies_total      := 0;
21   fga_policies_enabled    := 0;
22   fga_policies_column     := 0;
23   fga_policies_condition  := 0;
24   fga_policies_handler    := 0;
25   fga_policies_DB_trail   := 0;
26   fga_policies_XML_trail  := 0;
27 
28   -- Get FGA policy details from the database
29   FOR item IN (SELECT enabled, policy_text, policy_column, pf_function,
30                       audit_trail FROM DBA_AUDIT_POLICIES)
31   LOOP
32     IF (item.enabled = 'YES') THEN
33       fga_policies_enabled := fga_policies_enabled + 1;
34     END IF;
35 
36     IF (item.policy_text IS NOT NULL) THEN
37       fga_policies_condition := fga_policies_condition + 1;
38     END IF;
39 
40     IF (item.policy_column IS NOT NULL) THEN
41       fga_policies_column := fga_policies_column + 1;
42     END IF;
43 
44     IF (item.pf_function IS NOT NULL) THEN
45       fga_policies_handler := fga_policies_handler + 1;
46     END IF;
47 
48     IF (item.audit_trail LIKE 'DB%') THEN
49       fga_policies_DB_trail := fga_policies_DB_trail + 1;
50     ELSE
51       fga_policies_XML_trail := fga_policies_XML_trail + 1;
52     END IF;
53 
54     fga_policies_total := fga_policies_total + 1;
55   END LOOP;
56 
57   -- If atleast a single FGA policy is enabled, then FGA feature is enabled
58   if (fga_policies_enabled > 0) then
59     feature_boolean := 1;
60   end if;
61 
62   feature_usage := 'Number of FGA policies=' ||
63                        to_char(fga_policies_total) || '; ' ||
64                    'Number of Enabled FGA policies=' ||
65                        to_char(fga_policies_enabled) || '; ' ||
66                    'Number of FGA policies with audit_condition=' ||
67                        to_char(fga_policies_condition) || '; ' ||
68                    'Number of FGA policies with column-level audit=' ||
69                        to_char(fga_policies_column) || '; ' ||
70                    'Number of FGA policies with handler=' ||
71                        to_char(fga_policies_handler) || '; ' ||
72                    'Number of FGA policies with DB audit_trail=' ||
73                        to_char(fga_policies_DB_trail) || '; ' ||
74                    'Number of FGA policies with XML audit_trail=' ||
75                        to_char(fga_policies_XML_trail);
76 
77   feature_info := to_clob(feature_usage);
78 
79 END dbms_feature_fga_audit;