_____________________________________________________________________________________________________________________
Let’s first
create a sample test table 'TEST_DATA' under REDACT schema.
SQL> grant EXECUTE ON sys.dbms_redact TO REDACT;
Grant succeeded.
SQL> grant connect, resource to REDACT;
Grant succeeded.
Sample table created with following data
SQL> col CARD_NO for 9999999999999999
CUST_ID CARD_NO
CUS_EMAIL CARD_EXP_
---------- -----------------
------------------------------ ---------
10001 2345234523452345
xxxxxxx@abc.com 20-OCT-24
10002 7800780078007800
xxxxxxx@xyz.com 24-JAN-22
10003 6543654365436543
xxxxxxx@hyx.com 30-APR-19
Different
types of redactions in tabular format
|
Type
|
Stored
Data
|
Redacted
Form
|
|
FULL
|
10/12/2014
|
1/1/2010
|
|
PARTIAL
|
345-4321-5462
|
XXX-XXXX-5462
|
|
REGULAR EXP
|
[HIDE]@xyz.com
|
|
|
RANDOM
|
301101601201
|
100110210310
|
1.
FULL
REDACTION
In
full redaction, the card number modified to 0 after applying the policy.
Fuction_type parameter is highlighted.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'REDACT',
object_name =>
'TEST_DATA',
column_name =>
'CARD_NO',
policy_name =>
'test_card_no',
function_type => DBMS_REDACT.FULL,
expression => '5=5');
END;
/
SQL> SELECT cust_id, card_no, cus_email FROM
TEST_DATA;
CUST_ID CARD_NO
CUS_EMAIL
---------- -----------------
------------------------------
10001 0 scott@abc.com
10002 0 kester@xyz.com
10003 0 peter@hyx.com
2.
PARTIAL
REDACTION
Example
1: Partial Redaction on number values
In Partial Redaction, the first 12
digits of the credit card numbers are masked to number 4. We can mention the
function_parameters to achieve this results.
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'REDACT',
object_name =>
'TEST_DATA',
column_name =>
'CARD_NO',
policy_name =>
'test_card_no',
action =>
DBMS_REDACT.modify_column,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => '4,1,12');
END;
/
PL/SQL procedure successfully completed.
SQL> COLUMN card_no FORMAT 9999999999999999
SQL> SELECT cust_id, card_no, cus_email FROM
TEST_DATA;
CUST_ID CARD_NO
CUS_EMAIL
---------- -----------------
------------------------------
10001 4444444444442345 scott@abc.com
10002 4444444444447800 kester@xyz.com
10003 4444444444446543 peter@hyx.com
Example
2 – Partial Redaction to date data types
In this
example the card expiry dates are modified to 15th Dec along with actual year
of expiry.
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'REDACT',
object_name =>
'TEST_DATA',
column_name =>
'CARD_EXP_DT',
policy_name =>
'test_card_no',
action =>
DBMS_REDACT.add_column,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'm12d15Y');
END;
/
PL/SQL procedure successfully completed.
SQL> select * from test_data;
CUST_ID CARD_NO
CUS_EMAIL CARD_EXP_
---------- -----------------
------------------------------ ---------
10001 4444444444442345
scott@abc.com 15-DEC-24
10002 4444444444447800
kester@xyz.com 15-DEC-22
10003 4444444444446543
peter@hyx.com 15-DEC-19
3.
RANDOM
REDACTION
In Random Redaction the card numbers are transformed to
entirely different card numbers as random number.
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'REDACT',
object_name =>
'TEST_DATA',
column_name =>
'CARD_NO',
policy_name =>
'test_card_no',
action =>
DBMS_REDACT.modify_column,
function_type => DBMS_REDACT.random);
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select * from test_data;
CUST_ID CARD_NO
CUS_EMAIL CARD_EXP_
---------- -----------------
------------------------------ ---------
10001 387894244881085 scott@abc.com 15-DEC-24
10002 2331653517751192 kester@xyz.com 15-DEC-22
10003 3856235911291399 peter@hyx.com 15-DEC-19
4.
REGULAR
EXPRESSION
In this example the customer email address is getting
transformed to xxxxxx@domain.com using the
regular expression redaction.
BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'REDACT',
object_name =>
'TEST_DATA',
policy_name =>
'test_card_no',
column_name =>
'CUS_EMAIL',
function_type =>
DBMS_REDACT.REGEXP,
expression =>
'1=1',
enable => TRUE,
regexp_pattern => '(.){2,}\@([[:alnum:]])',
regexp_replace_string => 'xxxxxxx@\2',
regexp_position => '1',
regexp_occurrence => '0',
regexp_match_parameter => 'i'
);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TEST_DATA;
SQL> COL CUS_EMAIL FOR A30
SQL> /
CUST_ID CARD_NO
CUS_EMAIL CARD_EXP_
---------- -----------------
------------------------------ ---------
10001 2345234523452345 xxxxxxx@abc.com 20-OCT-24
10002 7800780078007800 xxxxxxx@xyz.com 24-JAN-22
10003 6543654365436543 xxxxxxx@hyx.com 30-APR-19
_____________________________________________________________________________________________________________________
0 comments:
Post a comment