[20181018]Oracle Database 12c: Data Redaction.txt
--//简单测试Data Redaction.Data Redaction主要目的对敏感信息的保护,随着对个人隐私的保护力度以及法律的加强,
--//这方面也许越来越重要.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
create table empx as select * from emp;
Avalible Redaction Methods:
Type Description
None No redaction is performed.
Full Columns are redacted to constant values based on the column data type.
Partial User-specified positions are replaced by a user-specified character.
Random Data type is preserved and different values are output each time.
Regular Expression A "match and replace" is performed based on parameters
--//具体细节还给看看文档.
SCOTT@test01p> select * from scott.empx where rownum<=3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
--//另外注意不能对sys和system用户进行数据的redact。因为他们都有EXP_FULL_DATABASE这个角色,而这个角色又包含了EXEMPT
--//REDACTION POLICY系统权限。同理,也不能直接赋予用户dba权限,dba自动包含EXP_FULL_DATABASE角色。
SYSTEM@test01p> revoke dba from scott;
Revoke succeeded.
2.测试:
--//define Redaction Policy,以system用户执行:
begin
DBMS_REDACT.ADD_POLICY
(policy_name => 'EMPSAL_POLICY',
object_schema => 'SCOTT',
object_name => 'EMPX',
column_name => 'SAL',
expression => '1=1',
function_type => DBMS_REDACT.FULL);
end;
/
--//以scott用户登录执行:
SCOTT@test01p> select * from scott.empx where rownum<=3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 0 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 0 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 0 500 30
--//sal=0
--//Modifiying Redaction Policy:
begin
DBMS_REDACT.ALTER_POLICY(
policy_name => 'EMPSAL_POLICY',
object_schema => 'SCOTT',
object_name => 'EMPX',
column_name => 'SAL',
action => DBMS_REDACT.MODIFY_column,
function_type => DBMS_REDACT.partial,
function_parameters => '9,1,10'
);
end;
/
SCOTT@test01p> select * from scott.empx where rownum<=3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 999 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 9999 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 9999 500 30
--//全部换成999
--//Drop a redact policy:
BEGIN
DBMS_REDACT.DROP_POLICY(
object_schema => 'SCOTT',
object_name => 'EMPX',
policy_name => 'EMPSAL_POLICY'
);
END;
/
SCOTT@test01p> select * from scott.empx where rownum<=3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
3.还有许多功能,不测试了.
--//收尾.
SYSTEM@test01p> grant dba to scott;
Grant succeeded.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2216853/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2216853/