1 PACKAGE dbms_redact AUTHID CURRENT_USER AS
2
3 -- Values for "function_type" parameter of DBMS_REDACT.add_policy
4 -- and DBMS_REDACT.alter_policy API.
5 NONE CONSTANT BINARY_INTEGER := 0;
6 FULL CONSTANT BINARY_INTEGER := 1;
7 PARTIAL CONSTANT BINARY_INTEGER := 2;
8 FORMAT_PRESERVING CONSTANT BINARY_INTEGER := 3;
9 RANDOM CONSTANT BINARY_INTEGER := 4;
10 REGEXP CONSTANT BINARY_INTEGER := 5;
11
12 -- Values for "action" parameter of DBMS_REDACT.alter_policy API.
13 ADD_COLUMN CONSTANT BINARY_INTEGER := 1;
14 DROP_COLUMN CONSTANT BINARY_INTEGER := 2;
15 MODIFY_EXPRESSION CONSTANT BINARY_INTEGER := 3;
16 MODIFY_COLUMN CONSTANT BINARY_INTEGER := 4;
17 SET_POLICY_DESCRIPTION CONSTANT BINARY_INTEGER := 5;
18 SET_COLUMN_DESCRIPTION CONSTANT BINARY_INTEGER := 6;
19
20 -- Preset values for "function_parameters" parameter for use in
21 -- partial redaction (function_type := dbms_redact.PARTIAL)
22 REDACT_US_SSN_F5 CONSTANT VARCHAR2(29) :=
23 'VVVFVVFVVVV,VVV-VV-VVVV,X,1,5';
24 REDACT_US_SSN_L4 CONSTANT VARCHAR2(29) :=
25 'VVVFVVFVVVV,VVV-VV-VVVV,X,6,9';
26 REDACT_US_SSN_ENTIRE CONSTANT VARCHAR2(29) :=
27 'VVVFVVFVVVV,VVV-VV-VVVV,X,1,9';
28 REDACT_NUM_US_SSN_F5 CONSTANT VARCHAR2(5) := '9,1,5';
29 REDACT_NUM_US_SSN_L4 CONSTANT VARCHAR2(5) := '9,6,9';
30 REDACT_NUM_US_SSN_ENTIRE CONSTANT VARCHAR2(5) := '9,1,9';
31 REDACT_ZIP_CODE CONSTANT VARCHAR2(17) :=
32 'VVVVV,VVVVV,X,1,5';
33 REDACT_NUM_ZIP_CODE CONSTANT VARCHAR2(5) := '9,1,5';
34 REDACT_CCN16_F12 CONSTANT VARCHAR2(46) :=
35 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12';
36 REDACT_DATE_MILLENNIUM CONSTANT VARCHAR2(9) :=
37 'm1d1y2000';
38 REDACT_DATE_EPOCH CONSTANT VARCHAR2(9) :=
39 'm1d1y1970';
40
41 -- Preset values for "regexp_pattern" parameter for use in
42 -- regular expression redaction (function_type := dbms_redact.REGEXP)
43 -- In general, this value determines what to recognize and replace.
44 RE_PATTERN_US_SSN CONSTANT VARCHAR2(26) :=
45 '(\d\d\d)-(\d\d)-(\d\d\d\d)';
46 RE_PATTERN_CC_L6_T4 CONSTANT VARCHAR2(33) :=
47 '(\d\d\d\d\d\d)(\d\d\d*)(\d\d\d\d)';
48 RE_PATTERN_ANY_DIGIT CONSTANT VARCHAR2(2) := '\d';
49 RE_PATTERN_US_PHONE CONSTANT VARCHAR2(39) :=
50 '(\(\d\d\d\)|\d\d\d)-(\d\d\d)-(\d\d\d\d)';
51 RE_PATTERN_EMAIL_ADDRESS CONSTANT VARCHAR2(51) :=
52 '([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})';
53 RE_PATTERN_IP_ADDRESS CONSTANT VARCHAR2(36) :=
54 '(\d{1,3}\.\d{1,3}\.\d{1,3})\.\d{1,3}';
55
56 -- Preset values for "regexp_replace_string" parameter for use in
57 -- regular expression redaction (function_type := dbms_redact.REGEXP)
58 -- In general, this value determines how to redact the element found.
59 --
60 -- Common pairings might include the following:
61 --
62 -- RE_PATTERN_ANY_DIGIT RE_REDACT_WITH_SINGLE_X
63 -- Replaces any digit found with the 'X' character.
64 -- RE_PATTERN_ANY_DIGIT RE_REDACT_WITH_SINGLE_1
65 -- Replaces any digit found with the '1' character.
66 -- RE_PATTERN_CC_L6_T4 RE_REDACT_CC_MIDDLE_DIGITS
67 -- Finds any credit card which could have 6 leading and
68 -- 4 trailing digits left as actual data and redacts the
69 -- middle digits.
70 -- RE_PATTERN_US_PHONE RE_REDACT_US_PHONE_L7
71 -- Finds any US phone number and redacts the last 7 digits.
72 -- RE_PATTERN_EMAIL_ADDRESS RE_REDACT_EMAIL_NAME
73 -- Finds any email address and redacts the email name.
74 -- RE_PATTERN_EMAIL_ADDRESS RE_REDACT_EMAIL_DOMAIN
75 -- Finds any email address and redacts the email domain.
76 -- RE_PATTERN_EMAIL_ADDRESS RE_REDACT_EMAIL_ENTIRE
77 -- Finds any email address and redacts the entire email.
78 --
79 RE_REDACT_CC_MIDDLE_DIGITS CONSTANT VARCHAR2(10) := '\1XXXXXX\3';
80 RE_REDACT_WITH_SINGLE_X CONSTANT VARCHAR2(1) := 'X';
81 -- We use 1 here because redacting a numeric field with 0 can lead
82 -- to ambiguity caused by truncation of leading zeroes.
83 RE_REDACT_WITH_SINGLE_1 CONSTANT VARCHAR2(1) := '1';
84 RE_REDACT_US_PHONE_L7 CONSTANT VARCHAR2(11) := '\1-XXX-XXXX';
85 RE_REDACT_EMAIL_NAME CONSTANT VARCHAR2(7) := 'xxxx@\2';
86 RE_REDACT_EMAIL_DOMAIN CONSTANT VARCHAR2(12) :=
87 '\[email protected]';
88 RE_REDACT_EMAIL_ENTIRE CONSTANT VARCHAR2(14) :=
89 '[email protected]';
90 RE_REDACT_IP_L3 CONSTANT VARCHAR2(6) :=
91 '\1.999';
92
93 -- Preset value for "regexp_position" parameter for use in
94 -- regular expression redaction (function_type := dbms_redact.REGEXP)
95 RE_BEGINNING CONSTANT BINARY_INTEGER := 1;
96
97 -- Preset values for "regexp_occurrence" parameter for use in
98 -- regular expression redaction (function_type := dbms_redact.REGEXP)
99 RE_ALL CONSTANT BINARY_INTEGER := 0;
100 RE_FIRST CONSTANT BINARY_INTEGER := 1;
101
102 -- Preset values for "regexp_match_parameter" parameter for use in
103 -- regular expression redaction (function_type := dbms_redact.REGEXP)
104 --
105 -- There is one constant for each individual option. Since more than one
106 -- option can be specified, specify multiple options with concatenation
107 --
108 -- For example, to specify case insensitive matching which ignores whitespace,
109 -- use regexp_match_parameter => RE_CASE_INSENSITIVE || RE_IGNORE_WHITESPACE
110 RE_CASE_SENSITIVE CONSTANT VARCHAR2(1) := 'c';
111 RE_CASE_INSENSITIVE CONSTANT VARCHAR2(1) := 'i';
112 RE_MULTIPLE_LINES CONSTANT VARCHAR2(1) := 'm';
113 RE_NEWLINE_WILDCARD CONSTANT VARCHAR2(1) := 'n';
114 RE_IGNORE_WHITESPACE CONSTANT VARCHAR2(1) := 'x';
115
116 PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);
117
118 -- ------------------------------------------------------------------------
119 -- add_policy - define a Data Redaction policy on an object (table/view)
120 --
121 -- INPUT PARAMETERS
122 -- object_schema - schema owning the object, current user if NULL
123 -- object_name - name of object
124 -- policy_name - name of policy to be added
125 -- policy_description - policy description to set (optional)
126 -- column_name - name of the column (optional)
127 -- column_description - column description to set (optional)
128 -- function_type - the type of redaction function to use
129 -- function_parameters - parameters to the redaction function
130 -- expression - the Policy Expression for the object
131 -- enable - TRUE: policy is enabled when defined,
132 -- FALSE: policy is disabled when defined.
133 -- (default: TRUE)
134 --
135 -- The following parameters are for redacting using a Regular Expression,
136 -- and must be specified only if the function_type is dbms_redact.REGEXP,
137 -- otherwise they must be omitted:
138 --
139 -- regexp_pattern - Regular Expression pattern (up to 512 bytes).
140 -- regexp_replace_string - Replacement string (up to 4000 characters in
141 -- length) with up to 500 back-references to
142 -- subexpressions in the form \n, (where n is
143 -- a number from 1 to 9).
144 -- regexp_position - integer counting from 1, giving the position
145 -- where the search should begin.
146 -- regexp_occurrence - Either 0 (to replace all occurrences of the
147 -- match), or a positive integer n (to replace
148 -- the nth occurrence of the match).
149 -- regexp_match_parameter - to change the default matching behavior,
150 -- possible values are a combination of 'i',
151 -- 'c', 'n', 'm', 'x', see the documentation
152 -- of the match_parameter in the REGEXP_REPLACE
153 -- section of the SQL reference manual.
154
155 PROCEDURE add_policy
156 (object_schema IN VARCHAR2 := NULL
157 ,object_name IN VARCHAR2
158 ,policy_name IN VARCHAR2
159 ,policy_description IN VARCHAR2 := NULL
160 ,column_name IN VARCHAR2 := NULL
161 ,column_description IN VARCHAR2 := NULL
162 ,function_type IN BINARY_INTEGER := dbms_redact.FULL
163 ,function_parameters IN VARCHAR2 := NULL
164 ,expression IN VARCHAR2
165 ,enable IN BOOLEAN := TRUE
166 ,regexp_pattern IN VARCHAR2 := NULL
167 ,regexp_replace_string IN VARCHAR2 := NULL
168 ,regexp_position IN BINARY_INTEGER := 1
169 ,regexp_occurrence IN BINARY_INTEGER := 0
170 ,regexp_match_parameter IN VARCHAR2 := NULL
171 );
172
173 -- ------------------------------------------------------------------------
174 -- drop_policy - drop a Data Redaction policy
175 --
176 -- INPUT PARAMETERS
177 -- object_schema - schema owning the object, current user if NULL
178 -- object_name - name of object
179 -- policy_name - name of policy to be dropped
180
181 PROCEDURE drop_policy
182 (object_schema IN VARCHAR2 := NULL
183 ,object_name IN VARCHAR2
184 ,policy_name IN VARCHAR2
185 );
186
187 -- ------------------------------------------------------------------------
188 -- alter_policy - alter a Data Redaction policy for an object (table/view)
189 --
190 -- INPUT PARAMETERS
191 -- object_schema - schema owning the object, current user if NULL
192 -- object_name - name of object
193 -- policy_name - name of policy to be altered
194 -- action - action to take
195 -- (default: add redaction on a column)
196 -- column_name - name of the column
197 -- function_type - the type of redaction function to use
198 -- function_parameters - parameters to the redaction function
199 -- expression - the Policy Expression for the object
200 --
201 -- The following parameters are for redacting using a Regular Expression,
202 -- and must be specified only if the function_type is dbms_redact.REGEXP,
203 -- otherwise they must be omitted:
204 --
205 -- regexp_pattern - Regular Expression pattern (up to 512 bytes).
206 -- regexp_replace_string - Replacement string (up to 4000 characters in
207 -- length) with up to 500 back-references to
208 -- subexpressions in the form \n, (where n is
209 -- a number from 1 to 9).
210 -- regexp_position - integer counting from 1, giving the position
211 -- where the search should begin.
212 -- regexp_occurrence - Either 0 (to replace all occurrences of the
213 -- match), or a positive integer n (to replace
214 -- the nth occurrence of the match).
215 -- regexp_match_parameter - to change the default matching behavior,
216 -- possible values are a combination of 'i',
217 -- 'c', 'n', 'm', 'x', see the documentation
218 -- of the match_parameter in the REGEXP_REPLACE
219 -- section of the SQL reference manual.
220 --
221 -- The following parameter only needs to be set if the action is
222 -- dbms_redact.SET_POLICY_DESCRIPTION otherwise it may be omitted:
223 -- policy_description - Policy description to set
224 --
225 -- The following parameter only needs to be set if the action is
226 -- dbms_redact.SET_COLUMN_DESCRIPTION otherwise it may be omitted:
227 -- column_description - Column description to set
228
229 PROCEDURE alter_policy
230 (object_schema IN VARCHAR2 := NULL
231 ,object_name IN VARCHAR2
232 ,policy_name IN VARCHAR2
233 ,action IN BINARY_INTEGER := dbms_redact.ADD_COLUMN
234 ,column_name IN VARCHAR2 := NULL
235 ,function_type IN BINARY_INTEGER := dbms_redact.FULL
236 ,function_parameters IN VARCHAR2 := NULL
237 ,expression IN VARCHAR2 := NULL
238 ,regexp_pattern IN VARCHAR2 := NULL
239 ,regexp_replace_string IN VARCHAR2 := NULL
240 ,regexp_position IN BINARY_INTEGER := 1
241 ,regexp_occurrence IN BINARY_INTEGER := 0
242 ,regexp_match_parameter IN VARCHAR2 := NULL
243 ,policy_description IN VARCHAR2 := NULL
244 ,column_description IN VARCHAR2 := NULL
245 );
246
247 -- ------------------------------------------------------------------------
248 -- disable_policy - disable a Data Redaction policy
249 --
250 -- INPUT PARAMETERS
251 -- object_schema - schema owning the object, current user if NULL
252 -- object_name - name of object
253 -- policy_name - name of policy to be disabled
254
255 PROCEDURE disable_policy
256 (object_schema IN VARCHAR2 := NULL
257 ,object_name IN VARCHAR2
258 ,policy_name IN VARCHAR2
259 );
260
261 -- ------------------------------------------------------------------------
262 -- enable_policy - enable a Data Redaction policy
263 --
264 -- INPUT PARAMETERS
265 -- object_schema - schema owning the object, current user if NULL
266 -- object_name - name of object
267 -- policy_name - name of policy to be enabled
268
269 PROCEDURE enable_policy
270 (object_schema IN VARCHAR2 := NULL
271 ,object_name IN VARCHAR2
272 ,policy_name IN VARCHAR2
273 );
274
275 -- ------------------------------------------------------------------------
276 -- fpm_mask - apply Format-preserving Data Redaction on the input
277 --
278 -- INPUT PARAMETERS
279 -- input_format - input format
280 -- output_format - output format
281 -- input_value - actual value to apply the mask to
282 -- masking_key - the FPM key, or the string 'wallet' indicating
283 -- that the key is available in the wallet.
284
285 PROCEDURE fpm_mask
286 (input_format IN VARCHAR2
287 ,output_format IN VARCHAR2
288 ,input_value IN VARCHAR2
289 ,masking_key IN VARCHAR2
290 );
291
292 -- ------------------------------------------------------------------------
293 -- fpm_unmask - remove Format-preserving Data Redaction from the input
294 --
295 -- INPUT PARAMETERS
296 -- input_format - input format
297 -- output_format - output format
298 -- input_value - value to unmask
299 -- masking_key - the FPM key, or the string 'wallet' indicating
300 -- that the key is available in the wallet.
301
302 PROCEDURE fpm_unmask
303 (input_format IN VARCHAR2
304 ,output_format IN VARCHAR2
305 ,input_value IN VARCHAR2
306 ,masking_key IN VARCHAR2
307 );
308
309 -- ------------------------------------------------------------------------
310 -- update_full_redaction_values - Update replacements for full redaction
311 --
312 -- INPUT PARAMETERS
313 -- number_val - value for NUMBER columns
314 -- binfloat_val - value for BINARY_FLOAT columns
315 -- bindouble_val - value for BINARY_DOUBLE columns
316 -- char_val - value for CHAR columns
317 -- varchar_val - value for VARCHAR2 columns
318 -- nchar_val - value for NCHAR columns
319 -- nvarchar_val - value for NVARCHAR2 columns
320 -- date_val - value for DATE columns
321 -- ts_val - value for TIMESTAMP columns
322 -- tswtz_val - value for TIMESTAMP WITH TIME ZONE columns
323 -- blob_val - value for BLOB columns
324 -- clob_val - value for CLOB columns
325 -- nclob_val - value for NCLOB columns
326
327 PROCEDURE update_full_redaction_values
328 (number_val IN NUMBER := NULL
329 ,binfloat_val IN BINARY_FLOAT := NULL
330 ,bindouble_val IN BINARY_DOUBLE := NULL
331 ,char_val IN CHAR := NULL
332 ,varchar_val IN VARCHAR2 := NULL
333 ,nchar_val IN NCHAR := NULL
334 ,nvarchar_val IN NVARCHAR2 := NULL
335 ,date_val IN DATE := NULL
336 ,ts_val IN TIMESTAMP := NULL
337 ,tswtz_val IN TIMESTAMP WITH TIME ZONE := NULL
338 ,blob_val IN BLOB := NULL
339 ,clob_val IN CLOB := NULL
340 ,nclob_val IN NCLOB := NULL
341 );
342
343 END dbms_redact;
[Home] [Help]