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