Data Redaction

• Data Masking : 실제 데이터 가공
• Data Redaction : 데이터 자체는 변경시키지 않고 단지 보여주는 데이타만 가공한다.
• 11.2.0.4 에서도 사용할 수 있다
• Oracle Redaction : Oracle Advanced Security

Data Redaction 시나리오

-- 적용 전
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     Higgins    12008 SHIGGINS  515.123.8080
        206 William    Gletz       8300 WGIETZ    515.123.8181

-- 적용 후
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     H******        0 ,T"/&      ***.***.***
        206 William    G****          0 lii4rw>N   ***.***.***

CREATE TABLE HR.RDTEST1 AS SELECT * FROM HR.EMPLOYEES;

-- CLERK 롤
CREATE ROLE CLERK;
GRANT CLERK TO SCOTT;
GRANT SELECT ON HR.RDTEST1 TO SCOTT;

Redaction Policy 생성

BEGIN DBMS_REDACT.ADD_POLICY(
  OBJECT_SCHEMA => 'HR'
  OBJECT_NAME => 'RDTEST1'
  COLUMN_NAME => 'SALARY'
  POLICY_NAME => 'REDACT_RDTEST1'
  POLICY_DESCRIPTION => 'REDACT_RDTEST1 PII'
  FUNCTION_TYPE => DBMS_REDACT.FULL,
  EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'' ) = ''FALSE''');
END;
/

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, EMAIL, PHONE_NUMBER
  FROM HR.RDTEST1
 WHERE EMPLOYEE_ID IN ( 205, 206 );

-- SALARY 적용
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     Higgins        0 SHIGGINS  515.123.8080
        206 William    Gletz          0 WGIETZ    515.123.8181

BEGIN DBMS_REDACT.ALTER_POLICY(
  OBJECT_SCHEMA => 'HR',
  OBJECT_NAME => 'RDTEST1',
  COLUMN_NAME => 'LAST_NAME',
  POLICY_NAME => 'REDACT_RDTEST1',
  POLICY_DESCRIPTION => 'REDACT_RDTEST1 PII',
  ACTION => DBMS_REDACT.ADD_COLUMN, -- 추가
  FUNCTION_TYPE => DBMS_REDACT.PARTIAL,
  FUNCTION_PARAMETERS => 'VVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVV,*,2,25',
  EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'' ) = ''FALSE''');
END;
/

-- LAST_NAME 적용
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     H******        0 SHIGGINS  515.123.8080
        206 William    G****          0 WGIETZ    515.123.8181

BEGIN DBMS_REDACT.ALTER_POLICY(
  OBJECT_SCHEMA => 'HR',
  OBJECT_NAME => 'RDTEST1',
  COLUMN_NAME => 'EMAIL',
  POLICY_NAME => 'REDACT_RDTEST1',
  POLICY_DESCRIPTION => 'REDACT_RDTEST1 PII',
  ACTION => DBMS_REDACT.ADD_COLUMN,
  FUNCTION_TYPE => DBMS_REDACT.PARTIAL,
  FUNCTION_PARAMETERS => DBMS_REDACT.RANDOM, --
  EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'' ) = ''FALSE''');
END;
/

-- EMAIL 적용
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     H******        0 30fjm2os  515.123.8080
        206 William    G****          0 aafaf3-dk 515.123.8181

BEGIN DBMS_REDACT.ALTER_POLICY(
  OBJECT_SCHEMA => 'HR',
  OBJECT_NAME => 'RDTEST1',
  COLUMN_NAME => 'PHONE_NUMBER',
  POLICY_NAME => 'REDACT_RDTEST1',
  POLICY_DESCRIPTION => 'REDACT_RDTEST1 PII',
  ACTION => DBMS_REDACT.ADD_COLUMN,
  FUNCTION_TYPE => DBMS_REDACT.REGEXP,
  EXPRESSION => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CLERK'' ) = ''FALSE''');
  REGEXP_PATTERN => ('(\d\d\d).(\d\d\d).(\d\d\d\d)',
  REGEXP_REPLACE_STRING => '***.***.****',
  REGEXP_POSITION => 1,
  REGEXP_OCCURRENCE => 0, REGEXP_MATCH_PARAMETER => 'i' );
END;
/

-- PHONE_NUMBER 적용
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     H******        0 30fjm2os  ***.***.****
        206 William    G****          0 aafaf3-dk ***.***.****

Data Redaction과 뷰

-- Data Redaction 적용된 테이블 : HR.RDTEST1
CREATE OR REPLACE VIEW HR.V1
AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
       SALARY, EMAIL, PHONE_NUMBER
  FROM HR.RDTEST1;

BEGIN DBMS_REDACT.ALTER_POLICY(
  OBJECT_SCHEMA => 'HR',
  OBJECT_NAME => 'V1',
  COLUMN_NAME => 'PHONE_NUMBER',
  POLICY_NAME => 'NO_RD_PHONE_NUM',
  FUNCTION_TYPE => DBMS_REDACT.NONE,
  EXPRESSION => '1=1');
END;
/

-- HR 유저로 다음 쿼리를 수행해서 Data Redaction이 취소된 것을 확인한다.
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY EMAIL     PHONE_NUMBER
----------- ---------- --------- ------ --------- ------------
        205 Shelly     H******        0 30fjm2os  515.123.8080
        206 William    G****          0 aafaf3-dk 515.123.8181

• CTAS 로 Redaction된 컬럼을 가진 테이블을 복제한다면 Redaction 속성은 상속받지 않지만 편집된 데이터로 해당 컬럼 값이 생성된다. ( == insert select )