Oracle Data Redaction 12c
Question: What does the Oracle data redaction feature do in Oracle 12c?
Answer: The data redaction feature, first introduced in Oracle 12c, allows for the masking of sensitive data from the end-user layer. Prior to Oracle 12c, you had to create views to "hide" sensitive column (pay rate, social_security_number, credit card numbers, etc.), but in 12c and beyond you can use the data redaction feature.
Any online user has experienced data redaction, which amounts to the replacement of sensitive data with asterisk list or other descriptive "masked" data.
Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries. The masking takes place in real time. The Data Redaction policy applies to the querying user, depending on this user's SYS_CONTEXT values. Oracle Database redacts only the data for the rows specified by the user's query, not the data for the entire column. The redaction takes place immediately before the data is returned to the querying user or application.
Oracle Advanced Security Data Redaction provides selective, on-the-fly redaction of sensitive data in SQL query results prior to display by applications so that unauthorized users cannot view the sensitive data. It enables consistent redaction of database columns across application modules accessing the same database information. Data Redaction minimizes changes to applications because it does not alter actual data in internal database buffers, caches, or storage, and it preserves the original data type and formatting when transformed data is returned to the application. Data Redaction has no impact on database operational activities such as backup and restore, upgrade and patch, and high availability clusters. Unlike historical approaches that require making changes to applications or proxying components of the database, Data Redaction policies are enforced directly in the database kernel, resulting in tighter security and better performance.
Declarative redaction policies can apply different data transformations such as partial, random, and full redaction. Data Redaction can be applied conditionally, based on different factors that are tracked by the database or passed to the database by applications such as user identifiers, application identifiers, or client IP addresses. A redaction format library provides pre-configured column templates to choose from for common types of sensitive information such as credit card numbers and national identification numbers. Once enabled, redaction polices are enforced immediately, even for active sessions.
Data Redaction fully supports Oracle Multitenant. When moving a pluggable database (PDB) that has redaction policies, the policies transfer directly to the new multitenant container database as part of the PDB. Redaction resumes its normal operation after the PDB has been plugged in and configured.
sateesh@PDB1>
Setup.sql
create table emp
( empno number,
ename varchar2(20),
salary number,
hire_date date,
email_id varchar2(30) ) ;
insert into emp(empno,ename,salary,hire_date,email_id)
values(1,'John',1013,sysdate,'John@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(2,'Miller',1578,sysdate-2,'miller@example.com');
insert into emp(empno,ename,salary,hire_date,email_id)
values(3,'Kim',1398,sysdate-3,'Kim@example.com');
commit;
sateesh@PDB1> select * from emp;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- -------------------- ---------- ----------------------- ----------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
sateesh@PDB1>
When the owner of the table "EMP" selects data, the results displays the values intact - but when any other user selects data from the table, the data must be masked.
Column
|
Description of redaction
|
Salary
|
replace with some random numbers and don't show actual values
|
Hire_date
|
show only the day and month year should be replaced with 1990
|
Email_id
|
Keep the domain name and rest others should be masked
|
sateesh@PDB1> grant select on emp to scott;
Grant succeeded.
sateesh@PDB1> begin
2 dbms_redact.add_policy
3 ( object_schema=>user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 column_name=>'SALARY',
7 function_type=>dbms_redact.random,
8 expression=> q'|sys_context('userenv','current_user')!= 'SATEESH'|'
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
sateesh@PDB1>
sateesh@PDB1> begin
2 dbms_redact.alter_policy
3 ( object_schema=> user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 action=>dbms_redact.add_column,
7 column_name=>'HIRE_DATE',
8 function_type=>dbms_redact.partial,
9 function_parameters=>'MDy1990' );
10 end;
11 /
PL/SQL procedure successfully completed.
sateesh@PDB1> begin
2 dbms_redact.alter_policy
3 ( object_schema=> user,
4 object_name=>'EMP',
5 policy_name=>'EMP_REDACT',
6 action=>dbms_redact.add_column,
7 column_name=>'EMAIL_ID',
8 function_type=>dbms_redact.regexp,
9 regexp_pattern=>dbms_redact.RE_PATTERN_EMAIL_ADDRESS,
10 regexp_replace_string=>dbms_redact.RE_REDACT_EMAIL_NAME,
11 regexp_match_parameter=>dbms_redact.RE_CASE_INSENSITIVE );
12 end;
13 /
PL/SQL procedure successfully completed.
sateesh@PDB1>
sateesh@PDB1> select * from emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- ---------- ---------- ----------------------- --------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
sateesh@PDB1>
scott@PDB1> select * from sateesh.emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- -------------------- ---------- ----------------------- -------------------
1 John 796 28-DEC-1990 04:10:34 PM xxxx@example.com
2 Miller 694 26-DEC-1990 04:10:34 PM xxxx@example.com
3 Kim 79 25-DEC-1990 04:10:34 PM xxxx@example.com
3 rows selected.
scott@PDB1>
Information's on redaction policies and policy columns are maintained in REDACTION_POLICIES and REDACTION_COLUMNS data dictionary.
sateesh@PDB1> @printtbl ' select * from redaction_policies '
OBJECT_OWNER : "SATEESH"
OBJECT_NAME : "EMP"
POLICY_NAME : "EMP_REDACT"
EXPRESSION : "sys_context('userenv','current_user')!= 'SATEESH'"
ENABLE : "YES "
POLICY_DESCRIPTION : ""
-----------------
PL/SQL procedure successfully completed.
sateesh@PDB1>
sateesh@PDB1>
sateesh@PDB1>
sateesh@PDB1> @printtbl ' select * from redaction_columns '
OBJECT_OWNER : "SATEESH"
OBJECT_NAME : "EMP"
COLUMN_NAME : "SALARY"
FUNCTION_TYPE : "RANDOM REDACTION"
FUNCTION_PARAMETERS : ""
REGEXP_PATTERN : ""
REGEXP_REPLACE_STRING : ""
REGEXP_POSITION : "0"
REGEXP_OCCURRENCE : "0"
REGEXP_MATCH_PARAMETER : ""
COLUMN_DESCRIPTION : ""
-----------------
OBJECT_OWNER : "SATEESH"
OBJECT_NAME : "EMP"
COLUMN_NAME : "EMAIL_ID"
FUNCTION_TYPE : "REGEXP REDACTION"
FUNCTION_PARAMETERS : ""
REGEXP_PATTERN : "([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"
REGEXP_REPLACE_STRING : "xxxx@\2"
REGEXP_POSITION : "1"
REGEXP_OCCURRENCE : "0"
REGEXP_MATCH_PARAMETER : "i"
COLUMN_DESCRIPTION : ""
-----------------
OBJECT_OWNER : "SATEESH"
OBJECT_NAME : "EMP"
COLUMN_NAME : "HIRE_DATE"
FUNCTION_TYPE : "PARTIAL REDACTION"
FUNCTION_PARAMETERS : "MDy1990"
REGEXP_PATTERN : ""
REGEXP_REPLACE_STRING : ""
REGEXP_POSITION : "0"
REGEXP_OCCURRENCE : "0"
REGEXP_MATCH_PARAMETER : ""
COLUMN_DESCRIPTION : ""
-----------------
PL/SQL procedure successfully completed.
sateesh@PDB1>
However If the querying user has the EXEMPT REDACTION POLICY system privilege, redaction will not be performed.
sateesh@PDB1> connect sys/oracle@pdb1 as sysdba
Connected.
sys@PDB1> grant exempt redaction policy to scott;
Grant succeeded.
sys@PDB1> set timing off
sys@PDB1>
sys@PDB1>
sys@PDB1> grant exempt redaction policy to scott;
Grant succeeded.
sys@PDB1> connect scott/tiger@pdb1
Connected.
scott@PDB1>
scott@PDB1> select * from session_privs ;
PRIVILEGE
----------------------------------------
CREATE SESSION
EXEMPT REDACTION POLICY
2 rows selected.
scott@PDB1>
scott@PDB1> select * from sateesh.emp ;
EMPNO ENAME SALARY HIRE_DATE EMAIL_ID
---------- -------------------- ---------- ----------------------- -------------------
1 John 1013 28-DEC-2013 04:10:34 PM John@example.com
2 Miller 1578 26-DEC-2013 04:10:34 PM miller@example.com
3 Kim 1398 25-DEC-2013 04:10:34 PM Kim@example.com
3 rows selected.
scott@PDB1>
===============================================================================
Comments
Post a Comment
Oracle DBA Information