Oracle 12c新特性--数据编写（Redaction）
在Oracle数据库中，当低权限的用户查询列中的敏感数据时，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角色。
案例分析：
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> insert into employee values(2,'mary',9000,to_date('01-07-2013','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
1、完全编写
full redaction的验证（number）
建立编写策略
sql> begin dbms_redact.add_policy(
2 object_schema=>'scott',
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 001-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=>‘scottf',
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 001-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=>'scott',
5 column_name=>'jobdate',
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
2、部分编写
partial redaction的验证（char）
sql> begin dbms_redact.alter_policy(
2 object_schema=>'scott',
5 column_name=>'mobile',
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 JOBDATEMOBILE
---------- ---------- ---------- --------- --------------------
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=>'scott',
5 column_name=>'num',
9 function_parameters=>'9,3');
10 end;
11 /
PL/sql procedure successfully completed.
sql> select *from employee;
ID NAME SALARY JOBDATE MOBILENUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 01-JAN-01 ***-****-*14699945
2 0 01-JAN-01 ***-****-*11199990
partial redaction的验证（date）
sql> begin dbms_redact.alter_policy(
2 object_schema=>'scott',
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=>'scott',
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=>'scott',
6 action=>dbms_redact.drop_column);
7 end;
8 /
PL/sql procedure successfully completed.
sql> begin dbms_redact.alter_policy(
2 object_schema=>'scott',
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=>'scott',
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=>'scott',
8 expression=>'1=1');
9 end;
10 /
PL/sql procedure successfully completed.
sql> select *from employee;
ID NAME SALARY JOBDATEMOBILENUM
---------- ---------- ---------- --------- -------------------- ----------
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 JOBDATEMOBILE NUM
---------- ---------- ---------- --------- -------------------- ----------
1 0 15-JUL-12 NHP*iNGYVPX2q 8443
2 0 15-JUL-13 pA,s<
用户可以对employee表进行dml操作，但是不能基于employee表进行ctas操作。如下：
sql> insert into employee values(3,'mouse',10000,to_date('01-08-2013','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
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.
当前用户是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中不能只针对某一列，这样的话不会生效。
4、查看redaction信息：
8:00:04 SYS@ orcl> select * from redaction_policies
OBJECT_OWN OBJECT_NAME POLICY_NAME EXPRESSION ENABLE POLICY_DESCRIPTION
---------- -------------------- -------------------- -------------------- ------- --------------------------------------------------
SCOTT employee p1 SYS_CONTEXT('USERENV YES
','SESSION_USER') !=
'TOM'
Elapsed: 00:00:00.00
18:04:28 SYS@ orcl>col COLUMN_NAME for a20
18:04:47 SYS@ orcl>col FUNCTION_PARAMETERS for a40
18:05:02 SYS@ orcl>select object_owner,object_name,column_name,function_type,function_parameters
2* from redaction_columns
OBJECT_OWN OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS
---------- -------------------- -------------------- --------------------------- ----------------------------------------
SCOTT employee JOBDATE PARTIAL REDACTION Md15YHMS
SCOTT employee NUM PARTIAL REDACTION 9,3
SCOTT employee MOBILE PARTIAL REDACTION VVVFVVVVFVVVV,8
SCOTTemployee NAME FULL REDACTION
SCOTT employee SALARY FULL REDACTION
17:18:14 SCOTT@ orcl>select * from t1;
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
Elapsed: 00:00:00.02
