1 procedure DBMS_FEATURE_TSDP
2 (feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage varchar2(1000);
7 num_sensitive_cols number;
8 num_policies number;
9 num_columns_protected number;
10 num_sensitive_types number;
11
12 begin
13 -- initialize
14 feature_boolean := 0;
15 aux_count := 0;
16 num_sensitive_cols := 0;
17 num_policies := 0;
18 num_columns_protected := 0;
19 num_sensitive_types := 0;
20
21 -- check if sensitive columns have been identified.
22 execute immediate 'select count(*) from DBA_SENSITIVE_DATA'
23 into num_sensitive_cols;
24
25 -- check if Sensitive Column Types have been created.
26 execute immediate 'select count(*) from DBA_SENSITIVE_COLUMN_TYPES'
27 into num_sensitive_types;
28
29 -- check if TSDP policies have been created.
30 execute immediate 'select count(*) from DBA_TSDP_POLICY_FEATURE'
31 into num_policies;
32
33 -- check for protected sensitive columns.
34 execute immediate 'select count(*) from DBA_TSDP_POLICY_PROTECTION'
35 into num_columns_protected;
36
37 -- Feature_usage information will contain:
38 -- number of Sensitive Column Types created,
39 -- number of TSDP policies created, and
40 -- number of Sensitive Columns protected using TSDP.
41 -- Note: Number of Sensitive Columns identified is not shown here
42 -- as it is a sensitive metric.
43 feature_usage :=
44 'Number of Sensitive Column Types created: ' ||
45 to_char(num_sensitive_types) ||
46 ', ' || 'Number of TSDP policies created: ' ||
47 to_char(num_policies) ||
48 ', ' || 'Number of Sensitive Columns protected using TSDP: ' ||
49 to_char(num_columns_protected)
50 ;
51
52 -- In order to conclude that TSDP is in use, we check if
53 -- atleast one Sensitive Column Type is created,
54 -- OR if atleast one column identified as sensitive,
55 -- OR atleast two TSDP policies exist. (Note that
56 -- REDACT_AUDIT policy is created by default,
57 -- and this policy cannot be dropped).
58 if ((num_sensitive_cols > 0) or (num_sensitive_types > 0)
59 or (num_policies > 1))
60 then
61 feature_boolean := 1;
62 feature_info := to_clob(feature_usage);
63
64 else
65 feature_info := to_clob('Transparent Sensitive Data Protection ' ||
66 'feature not used');
67
68 end if;
69
70 end;
[Home] [Help]