12c Data redaction
Data redaction
当低权限的用户查询列中的数据时，oracle redaction会对返回给用户的数据稍作掩饰，以保证机密数据的安全。对于列中的数据有以下几种redaction方式：
1.Full redaction. 对列中的数据全部redact，number类型的列将全部返回为0，character类型的列将全部返回为空格，日期类型返回为2001-01-01。
2.Partial redaction. 对列中的一部分数据进行redact，比如，可以对社会保险号的前几位设置返回为*，剩下的几位保持不变。只有列中的数据为固定宽度时才能使用这种方式，如果列中存储的是email地址，每个email地址的宽度不尽相同，此时要使用Regular expressions。
3.Regular expressions. You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.
4.Random redaction. The redacted data presented to the querying user appears as randomly generated values each time it is displayed, depending on the data type of the column.
5.No redaction. This option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.
不能对sys和system用户进行数据的redact。因为他们都有EXP_FULL_DATABASE这个角色, 而这个角色又包含了EXEMPT REDACTION POLICY系统权限。同理，也不能直接赋予用户dba权限，dba自动包含EXP_FULL_DATABASE角色。
redaction类型由dbms_redact.add_policy中的function_type来控制，默认为full redaction
function_type=>dbms_redact.full;function_type=>dbms_redact.partial;function_type=>dbms_redact.random;
SQL> create table employee(id number,name varchar2(10),salary number,jobdate date,mobile varchar2(20));
Table created.
SQL> insert into employee values(1,'tom',6000,to_date('01-07-2012','dd-mm-yyyy'),'135-2009-1146');
1 row created.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE
---------- ---------- ---------- --------- --------------------
1 tom 6000 01-JUL-12 135-2009-1146
SQL> insert into employee values(2,'mary',9000,to_date('01-07-2013','dd-mm-yyyy'),'135-2009-1111');
1 row created.
SQL> commit;
Commit complete.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE
---------- ---------- ---------- --------- --------------------
1 tom 6000 01-JUL-12 135-2009-1146
2 mary 9000 01-JUL-13 135-2009-1111
full redaction的验证（number）
SQL> begin dbms_redact.add_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'salary',
6 function_type=>dbms_redact.full,
7 enable=>true,
8 expression=>'1=1');
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE
---------- ---------- ---------- --------- --------------------
1 tom 0 01-JUL-12 135-2009-1146
2 mary 0 01-JUL-13 135-2009-1111
full redaction的验证（char）
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'name',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.full,
8 expression=>'1=1');
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE
---------- ---------- ---------- --------- --------------------
1 0 01-JUL-12 135-2009-1146
2 0 01-JUL-13 135-2009-1111
full redaction的验证（date）
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'jobdate',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.full,
8 expression=>'1=1');
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE
---------- ---------- ---------- --------- --------------------
1 0 01-JAN-01 135-2009-1146
2 0 01-JAN-01 135-2009-1111
partial redaction的验证（char）
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'mobile',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.partial,
8 expression=>'1=1',
9 function_parameters=>'VVVFVVVVFVVVV,VVV-VVVV-VVVV,*,1,8');
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE
---------- ---------- ---------- --------- --------------------
1 0 01-JAN-01 ***-****-*146
2 0 01-JAN-01 ***-****-*111
partial redaction的验证（number）
SQL> alter table employee add num number(38);
Table altered.
SQL> update employee set num=12345 where id=1;
1 row updated.
SQL> update employee set num=67890 where id=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 01-JAN-01 ***-****-*146 12345
2 0 01-JAN-01 ***-****-*111 67890
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'num',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.partial,
8 expression=>'1=1',
9 function_parameters=>'9,1,3');
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 01-JAN-01 ***-****-*146 99945
2 0 01-JAN-01 ***-****-*111 99990
partial redaction的验证（date）
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'jobdate',
6 action=>dbms_redact.drop_column,
7 expression=>'1=1')
8 ;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 01-JUL-12 ***-****-*146 99945
2 0 01-JUL-13 ***-****-*111 99990
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'jobdate',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.partial,
8 expression=>'1=1',
9 function_parameters=>'Md15YHMS');----Md15YHMS:month day year hour minute second
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 ***-****-*146 99945
2 0 15-JUL-13 ***-****-*111 99990
partial redaction的验证
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'mobile',
6 action=>dbms_redact.drop_column);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'num',
6 action=>dbms_redact.drop_column);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 135-2009-1146 12345
2 0 15-JUL-13 135-2009-1111 67890
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'mobile',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.random,
8 expression=>'1=1');
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'num',
6 action=>dbms_redact.add_column,
7 function_type=>dbms_redact.random,
8 expression=>'1=1');
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 )3;&xt]Y1;C.! 9903
2 0 15-JUL-13 "Rf(LML)*Zn0T 18940
SQL> select *from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 NHP*iNGYVPX2q 8443
2 0 15-JUL-13 pA,s<<i+xI4{D 41125
用户可以对employee表进行dml操作，但是不能基于employee表进行ctas操作。如下：
SQL> insert into employee values(3,'mouse',10000,to_date('01-08-2013','dd-mm-yyyy'),'135-2009-1126',12345);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 <EC0vY8"DMK8~ 11779
2 0 15-JUL-13 @adG3r.LHilO; 20119
3 0 15-AUG-13 T]@7MM(2eH?U 9883
SQL> create table test as select * from employee;
create table test as select * from employee
*
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.
修改policy使得其他用户可以访问到真实数据
当前用户是test
SQL> show user
USER is "TEST"
SQL> select *from ysf.employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 =!'<[j9.E)/Dc 12012
2 0 15-JUL-13 b6JNe.`?j>RVm 44494
3 0 15-AUG-13 /.v~m-Gt76~'u 4632
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 action=>dbms_redact.modify_expression,
6 expression=>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''TEST''');
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select *from ysf.employee;
ID NAME SALARY JOBDATE MOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 tom 6000 01-JUL-12 135-2009-1146 12345
2 mary 9000 01-JUL-13 135-2009-1111 67890
3 mouse 10000 01-AUG-13 135-2009-1126 12345
让test用户看到真实数据的话，在policy中不能只针对某一列，这样的话不会生效。
SQL> begin dbms_redact.alter_policy(
2 object_schema=>'ysf',
3 object_name=>'employee',
4 policy_name=>'p1',
5 column_name=>'mobile',
6 action=>dbms_redact.modify_expression,
7 expression=>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''TEST''');
8 end;
9 /
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28746920/viewspace-2134771/ ，如需转载，请注明出处，否则将追究法律责任。
