12C DATA Redaction Policy-数据遮罩保护

Kamus的文章：http://www.dbform.com/html/2013/2116.html

官方文档：http://docs.oracle.com/cd/E16655_01/network.121/e17729/redaction_config.htm#CEGDAIAB

DATA Redaction Policy，SQL Developer给出的翻译是遮罩保护，虽然翻译有点别扭，但是个人觉得翻译得挺好的，DATA Redaction可以将列的数据不显示，或者显示其他字符。

做个简单DATA Redaction的实验。
首先要回收DBA权限，DBA权限可以看到保护的数据。

SQL> GRANT SELECT ON sys.redaction_policies TO c##awen;

SQL> GRANT SELECT ON sys.redaction_columns TO c##awen;

SQL> GRANT EXECUTE ON dbms_redact TO c##awen;

SQL> REVOKE dba FROM C##AWEN;

SQL> GRANT CONNECT, resource TO C##AWEN;

测试表CTAS from dba_objects:

SQL> desc n_obj;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)

原始数据：
SQL> select OWNER,OBJECT_ID,OBJECT_NAME from n_obj where rownum<5;

OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
SYS 49 I_COL2
SYS 3 I_OBJ#
SYS 28 CON$
SYS 53 I_CDEF1

添加策略1，FULL，数字显示为0，将字符显示为空格，
BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_REDACT',
column_name => 'OBJECT_ID',
function_type => DBMS_REDACT.FULL,
expression => '1=1',
enable => TRUE
);
END;
/

SQL> select OWNER,OBJECT_ID,OBJECT_NAME from n_obj where rownum<5;

OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
SYS 0 I_COL2
SYS 0 I_OBJ#
SYS 0 CON$
SYS 0 I_CDEF1

改为随机，字符数字随机显示
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_REDACT',
column_name => 'OBJECT_ID',
action => DBMS_REDACT.MODIFY_COLUMN,
function_type => DBMS_REDACT.RANDOM
);
END;
/

SQL> /

OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
SYS 46 I_COL2
SYS 2 I_OBJ#
SYS 12 CON$
SYS 25 I_CDEF1

改为部分函数，修改字段中的部分数据，8，1，5表示从第一位到第五位用数字8代替，另字段值有多少位显示多少位。

BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_REDACT',
column_name => 'OBJECT_ID',
action => DBMS_REDACT.MODIFY_COLUMN,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => '8,1,5'
);
END;
/

SQL> /

OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
SYS 88 I_COL2
SYS 8 I_OBJ#
SYS 88 CON$
SYS 88 I_CDEF1

添加列
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_REDACT',
column_name => 'OWNER',
action => DBMS_REDACT.MODIFY_COLUMN,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,2'
);
END;
/

SQL> select OWNER,OBJECT_ID,OBJECT_NAME from n_obj where rownum<5;

OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
**S 88 I_COL2
**S 8 I_OBJ#
**S 88 CON$
**S 88 I_CDEF1

另外可以根据会话，角色，IP等环境设置策略，详细的可以参照官方文档。
例如：如果遮罩保护策略只对会话用户不为C##AWEN的用户生效。
BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'C##AWEN',
object_name => 'IN_TT',
policy_name => 'J_REDCAT',
column_name => 'ADDR',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) !=''C##AWEN''',
enable => TRUE
);
END;
/


启用遮罩保护策略
BEGIN
DBMS_REDACT.ENABLE_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_REDACT'
);
END;
/

禁用遮罩保护策略
BEGIN
DBMS_REDACT.DISABLE_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_REDACT'
);
END;
/

删除遮罩保护策略
BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'C##AWEN',
object_name => 'N_OBJ',
policy_name => 'N_OBJ_REDACTION'
);
END;
/