[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_DATA_REDACTION
Source
1 procedure DBMS_FEATURE_DATA_REDACTION
2 (feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage varchar2(1000);
7 num_policies number;
8 num_policies_enabled number;
9 num_full_redaction number;
10 num_partial_redaction number;
11 num_random_redaction number;
12 num_regexp_redaction number;
13
14 begin
15 -- initialize
16 feature_boolean := 0;
17 aux_count := 0;
18 num_policies := 0;
19 num_policies_enabled := 0;
20 num_full_redaction := 0;
21 num_partial_redaction := 0;
22 num_random_redaction := 0;
23 num_regexp_redaction := 0;
24
25 -- check for Data Redaction usage by counting number of policies
26 execute immediate 'select count(*) from REDACTION_POLICIES '
27 into num_policies;
28
29 if (num_policies > 0) then
30 feature_boolean := 1;
31
32 -- check for enable Data Radaction policy usage
33 execute immediate 'select count(*) from REDACTION_POLICIES ' ||
34 'where upper(ENABLE) like ''%YES%'''
35 into num_policies_enabled;
36
37 -- check for Full Data Redaction type usage
38 execute immediate 'select count(*) from REDACTION_COLUMNS ' ||
39 'where FUNCTION_TYPE = ''FULL REDACTION'''
40 into num_full_redaction;
41
42 -- check for Partial Data Redaction type usage
43 execute immediate 'select count(*) from REDACTION_COLUMNS ' ||
44 'where FUNCTION_TYPE = ''PARTIAL REDACTION'''
45 into num_partial_redaction;
46
47 -- check for Random Data Redaction type usage
48 execute immediate 'select count(*) from REDACTION_COLUMNS ' ||
49 'where FUNCTION_TYPE = ''RANDOM REDACTION'''
50 into num_random_redaction;
51
52 -- check for Regexp-based Data Redaction type usage
53 execute immediate 'select count(*) from REDACTION_COLUMNS ' ||
54 'where FUNCTION_TYPE = ''REGEXP REDACTION'''
55 into num_regexp_redaction;
56
57 feature_usage :=
58 'Number of data redaction policies: ' ||
59 to_char(num_policies) ||
60 ', ' || 'Number of enabled policies: ' ||
61 to_char(num_policies_enabled) ||
62 ', ' || 'Number of policies using full redaction: ' ||
63 to_char(num_full_redaction) ||
64 ', ' || 'Number of policies using partial redaction: ' ||
65 to_char(num_partial_redaction) ||
66 ', ' || 'Number of policies using random redaction: ' ||
67 to_char(num_random_redaction) ||
68 ', ' || 'Number of policies using regexp redaction: ' ||
69 to_char(num_regexp_redaction)
70 ;
71 feature_info := to_clob(feature_usage);
72 else
73 feature_info := to_clob('Data Redaction usage not detected');
74 end if;
75
76 end;