Oracle Data Redaction
---------------------
Its mainly used for data masking during run time,
the masked data is only at the run time and it will not be stored in blocks, instead of application now
it can be done from the oracle database itself.
The Data Redaction policy applies to the querying
user, depending on this user's SYS_CONTEXT values.
This dynamic masking can be done based on the
username, ip-address, application context etc.
If the querying user has the EXEMPT REDACTION POLICY
system privilege, redaction will not be performed. If the user does not have the
EXEMPT REDACTION POLICY system privilege, the policy expression will be evaluated
in the current user's environment. If the policy expression evaluates to TRUE, then
redaction will be performed, otherwise no redaction will be performed.
Four type of redaction
Full ->
entire column data will be redacted with constant value based on the column type.
Eg.
Original Redacted
01/07/83 01/01/01
Partial ->
the Specified portion of data will be redacted.
Eg
Original Redacted
DY125768MY DY*****MY
Random ->
Data will be redacted based on some random values.
Eg
Original Redacted
DY125768MY HYDIDIKMUJ
Regular expression -> Data will be replaced with
some expression.
Eg:-
Original Redacted
DY125768MY **-4######
EG.
How to redact?
->> identify schema / object / column_name
->> Identify the method which should be used
along with any required parameters. Only one column can be used while creating the policy. Later
you can add more columns to the policy.
->> Condition for which redaction should be
applied, eg. you can specify like wherever the data is selected
apart from certain ip addrss then it should policy should be applied.
Restriction
Cannot redact sys / system schema objects.
Cannot redact virtual columns
Some data types
VPD policies can be applied only to the column which
is not redacted.
Package used - dbms_redact
Eg:-
SQL> create table manzoor.emp as select * from
scott.emp where 1=2;
Table created.
SQL> desc emp;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EMPNO NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
SQL> begin
for i
in 1..1000 loop
insert into emp values (i, dbms_random.string('U',10), 'DEVELOPER',2341,
sysdate-i, trunc(dbms_random.value(10000,20000)),2222,12);
end
loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select * from emp where rownum < 10;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
1
IYHJTLCTPB DEVELOPER 2341 05-SEP-15 14289 2222 12
2
HCYCOLPAHA DEVELOPER 2341
04-SEP-15 11921 2222 12
3
MJICOPLYWZ DEVELOPER 2341
03-SEP-15 15043 2222 12
4
OMIXTWDKZI DEVELOPER 2341
02-SEP-15 17673 2222 12
5
GUIMRTIGXD DEVELOPER 2341
01-SEP-15 10937 2222 12
6
QBWYEJZSCP DEVELOPER 2341
31-AUG-15 19054 2222 12
7
QARYRVOJBG DEVELOPER 2341
30-AUG-15 19654 2222 12
8
UWZWWVIBZQ DEVELOPER 2341
29-AUG-15 15522 2222 12
9
LLNHSPFAXZ DEVELOPER 2341
28-AUG-15 17850 2222 12
9 rows selected.
We will create a policy emp_policy , and mask with
full redact to sal column.
SQL> grant select, update, delete on manzoor.emp
to ahamed;
Grant succeeded.
begin
dbms_redact.add_policy
(
object_schema => 'MANZOOR',
object_name
=> 'EMP',
policy_name
=> 'EMP_POLICY',
column_name
=> 'SAL',
function_type =>
DBMS_REDACT.FULL,
expression =>
'SYS_CONTEXT(''USERENV'',''SESSION_USER'') !=''MANZOOR''',
policy_description => 'Emp table redact',
column_description => 'Salry column masking'
);
end;
/
PL/SQL procedure successfully completed.
Function_type :-
Defines what type of redaction we are going to apply
it could be either none/full/partital/reg_exp ..etc.
Expression :-
Defines the condition for which the redact policy
has to be applied. Here we have set the policy as the sal value should be
masked for all the users except manzoor.
SQL> select view_name from dba_views where
view_name like '%REDAC%';
VIEW_NAME
--------------------------------------------------------------------------------------------------------------------------------
REDACTION_POLICIES
REDACTION_COLUMNS
REDACTION_VALUES_FOR_TYPE_FULL
col EXPRESSION for a40;
col POLICY_DESCRIPTION for a50;
set lines 200 pages 200;
col object_owner for a20;
col object_name for a15;
col policy_name for a10;
SQL> select * from REDACTION_POLICIES;
OBJECT_OWNER
OBJECT_NAME POLICY_NAM
EXPRESSION
ENABLE POLICY_DESCRIPTION
-------------------- --------------- ----------
---------------------------------------- -------
--------------------------------------------------
MANZOOR
EMP EMP_POLICY
SYS_CONTEXT('USERENV','SESSION_USER') !='MANZOOR' YES Emp table redact
SQL> conn ahamed/manzoor
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> select * from manzoor.emp where rownum <
10;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
1
IYHJTLCTPB DEVELOPER 2341
05-SEP-15 0 2222 12
2
HCYCOLPAHA DEVELOPER 2341
04-SEP-15 0 2222 12
3
MJICOPLYWZ DEVELOPER 2341
03-SEP-15 0 2222 12
4
OMIXTWDKZI DEVELOPER 2341
02-SEP-15 0 2222 12
5
GUIMRTIGXD DEVELOPER 2341
01-SEP-15 0 2222 12
6
QBWYEJZSCP DEVELOPER 2341
31-AUG-15 0 2222 12
7
QARYRVOJBG DEVELOPER 2341
30-AUG-15 0 2222
12
8
UWZWWVIBZQ DEVELOPER 2341
29-AUG-15 0 2222 12
9
LLNHSPFAXZ DEVELOPER 2341
28-AUG-15 0 2222 12
9 rows selected.
Now we could see that the SAL value is not
displaying when the rows are selected by ahamed user.
You can view the REDACTION_VALUES_FOR_TYPE_FULL view
to view the default value which will be visible for FULL redaction for each
data type.
SQL> desc REDACTION_VALUES_FOR_TYPE_FULL;
Name Null? Type
-------------------------------------- -------- ---------------------------
NUMBER_VALUE NOT NULL NUMBER
BINARY_FLOAT_VALUE NOT NULL BINARY_FLOAT
BINARY_DOUBLE_VALUE NOT NULL BINARY_DOUBLE
CHAR_VALUE VARCHAR2(1)
VARCHAR_VALUE VARCHAR2(1)
NCHAR_VALUE NCHAR(1)
NVARCHAR_VALUE NVARCHAR2(1)
DATE_VALUE NOT NULL DATE
TIMESTAMP_VALUE NOT NULL TIMESTAMP(6)
TIMESTAMP_WITH_TIME_ZONE_VALUE NOT NULL TIMESTAMP(6) WITH TIME ZONE
BLOB_VALUE BLOB
CLOB_VALUE CLOB
NCLOB_VALUE NCLOB
SQL> select number_value from
REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
0
Thus 0 will be visbile SAL column. We can change the
default value to some other using the below procedure.
SQL> exec
dbms_redact.UPDATE_FULL_REDACTION_VALUES (NUMBER_VAL => 9);
PL/SQL procedure successfully completed.
SQL> select number_value from
REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
9
-- Database bounce is required to take effect.
SQL> select * from manzoor.emp where rownum <
10;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
1
IYHJTLCTPB DEVELOPER 2341
05-SEP-15 9 2222 12
2
HCYCOLPAHA DEVELOPER 2341
04-SEP-15 9 2222 12
3
MJICOPLYWZ DEVELOPER 2341
03-SEP-15 9 2222 12
4
OMIXTWDKZI DEVELOPER 2341
02-SEP-15 9 2222 12
5
GUIMRTIGXD DEVELOPER 2341
01-SEP-15 9 2222 12
6
QBWYEJZSCP DEVELOPER 2341
31-AUG-15 9 2222 12
7
QARYRVOJBG DEVELOPER 2341
30-AUG-15 9 2222 12
8
UWZWWVIBZQ DEVELOPER 2341
29-AUG-15 9 2222 12
9
LLNHSPFAXZ DEVELOPER 2341
28-AUG-15 9 2222
12
9 rows selected.
-- Partital Redaction
SQL> alter table emp add (pan_card varchar2(10));
Table altered.
SQL> truncate table emp;
Table truncated.
SQL> begin
for i in 1..1000 loop
insert into emp values (i,
dbms_random.string('U',10), 'DEVELOPER',2341, sysdate-i,
trunc(dbms_random.value(10000,20000)),2222,12,dbms_random.string('U',2)||trunc(dbms_random.value(100000,999999))||dbms_random.string('U',2));
end
loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select * from manzoor.emp where rownum <
10;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO PAN_CARD
---------- ---------- --------- ---------- ---------
---------- ---------- ---------- ----------------
336
UVSVEEEXXP DEVELOPER 2341
05-OCT-14 9 2222 12 TQ974342FY
337
NSOGZZVAEN DEVELOPER 2341
04-OCT-14 9 2222 12 DR486440GL
338
TEMNLPCVGF DEVELOPER 2341
03-OCT-14 9 2222 12 YR909461PW
339
DUZKTTXZIL DEVELOPER 2341
02-OCT-14 9 2222 12 VP494990IQ
340
URGBOHUTYV DEVELOPER 2341
01-OCT-14 9 2222
12 RW485144WH
341
HSOKEDKCQL DEVELOPER 2341
30-SEP-14 9 2222 12 AA499696MA
342
XMAFFJPBPE DEVELOPER 2341
29-SEP-14 9 2222 12 QE795223GQ
343
DNFUPTSKDO DEVELOPER 2341 28-SEP-14 9 2222 12 OT317912UV
344
HFWCUMIXWP DEVELOPER 2341
27-SEP-14 9 2222 12 JX695292SU
-- We will mask the PAN_CARD , its totally 10 char,
we will hide only the number starting position from 3.
begin
dbms_redact.ALTER_POLICY
(
OBJECT_SCHEMA
=> 'MANZOOR',
OBJECT_NAME
=> 'EMP',
POLICY_NAME
=> 'EMP_POLICY',
ACTION => DBMS_REDACT.ADD_COLUMN,
COLUMN_NAME
=> 'PAN_CARD',
FUNCTION_TYPE
=> DBMS_REDACT.PARTIAL,
FUNCTION_PARAMETERS =>
'VVVVVVVVVV,VVVVVVVVVV,X,3,8',
EXPRESSION
=> 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') !=''MANZOOR'''
);
end;
/
PL/SQL procedure successfully completed.
Action => we are adding one more column to the
existing policy , (ADD_COLUMN).
Function_parameter => Syntax
(input,output,character to which the orginal be replaced,starting position, end
poisiton). Input value -> V refres character and F denotes the format, here
in our example we dont have format.
Eg: for format
Let say we have stored the pan card as "TQ
974342 FY" in this format in the db.
we wanted to replace space with # then
FUNCTION_PARAMETER =>
'VVFVVVVVVFVV,VV#VVVVVV#VV,X,3,8'
The redacted value will be as TQ#XXXXXX#FY
For Numbers.
function_parameters - defines how the data should be
masked, refer Pl/sql reference for detailed differnet methods. In this we
mentioned 9,1,4 which is the number value should be replaced by 9 and 2nd is
the starting and 3rd is the end position. eg. value with 17850 will be replaced
as 99990
SQL> select OBJECT_NAME, COLUMN_NAME,
FUNCTION_TYPE, FUNCTION_PARAMETERS from REDACTION_COLUMNS;
OBJECT_NAME
COLUMN_NAM FUNCTION_TYPE
FUNCTION_PARAMETERS
--------------- ----------
--------------------------- ----------------------------------------
EMP
PAN_CARD PARTIAL REDACTION VVVVVVVVVV,VVVVVVVVVV,X,3,8
EMP
SAL FULL REDACTION
SQL> show user;
USER is "AHAMED"
SQL> select * from manzoor.emp where rownum <
10;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO PAN_CARD
---------- ---------- --------- ---------- ---------
---------- ---------- ---------- ----------
336
UVSVEEEXXP DEVELOPER 2341
05-OCT-14 9 2222 12 TQXXXXXXFY
337
NSOGZZVAEN DEVELOPER 2341
04-OCT-14 9 2222 12 DRXXXXXXGL
338
TEMNLPCVGF DEVELOPER 2341
03-OCT-14 9 2222 12 YRXXXXXXPW
339
DUZKTTXZIL DEVELOPER 2341
02-OCT-14 9 2222 12 VPXXXXXXIQ
340
URGBOHUTYV DEVELOPER 2341
01-OCT-14 9 2222 12 RWXXXXXXWH
341
HSOKEDKCQL DEVELOPER 2341
30-SEP-14 9 2222 12 AAXXXXXXMA
342
XMAFFJPBPE DEVELOPER 2341
29-SEP-14 9 2222 12 QEXXXXXXGQ
343
DNFUPTSKDO DEVELOPER 2341
28-SEP-14 9 2222 12 OTXXXXXXUV
344
HFWCUMIXWP DEVELOPER 2341
27-SEP-14 9 2222 12 JXXXXXXXSU
9 rows selected.
We could see that the policy has been taken effect.
Droping the policy.
SQL> select OBJECT_NAME, COLUMN_NAME,
FUNCTION_TYPE, FUNCTION_PARAMETERS from REDACTION_COLUMNS;
OBJECT_NAME
COLUMN_NAM FUNCTION_TYPE
FUNCTION_PARAMETERS
--------------- ----------
--------------------------- ----------------------------------------
EMP
PAN_CARD PARTIAL REDACTION VVVVVVVVVV,VVVVVVVVVV,X,3,8
EMP
SAL FULL REDACTION
SQL> exec
dbms_redact.drop_policy('MANZOOR','EMP','EMP_POLICY');
PL/SQL procedure successfully completed.
SQL> select OBJECT_NAME, COLUMN_NAME,
FUNCTION_TYPE, FUNCTION_PARAMETERS from REDACTION_COLUMNS;
no rows selected
Things to consider:-
a) Redact only selected columns / or the columns
which actually required to be redacted.
b) Keep policy expression simpler as much as
possible.
c) You cannot perfrom ctas when any of the column is
redacted.
d) Partial and full redact generally provides better
performance then regular expression policy since it has to compile every time.
e) You can apply only one policy on a table or view.
hi.. how to use redact functionality for each row
ReplyDelete