[20181018]Oracle Database 12c: Data Redaction.txt

[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.