oracle 12c redact

1. redact 

  사용자 데이터중 민감한 정보를 masking 하고 싶을때..

  grant execute on sys.dbms_redact to test;

1) add policy  ( redact 정책 추가) 

BEGIN

   DBMS_REDACT.ADD_POLICY(
     object_schema        => 'hr',                                              -- 계정
     object_name          => 'employees',                                    -- 테이블 이름
     column_name          => 'employee_id',                                -- 컬럼
     policy_name          => 'mask_emp_id_nums',                         -- policy 이름
     function_type        => DBMS_REDACT.PARTIAL,                      -- redact func
     function_parameters  => '7,1,5',                                           -- type이 partial이고 컬럼이 숫자일 경우 1~ 5까지 숫자를 7로 변환
     expression           => '1=1');                                               -- 항상 적용
END;

* function_type이 DBMS_REDACT.FULL일 경우 default 값 조회

SET LINESIZE 250

COLUMN char_value FORMAT A10

COLUMN varchar_value FORMAT A10

COLUMN nchar_value FORMAT A10

COLUMN nvarchar_value FORMAT A10

COLUMN timestamp_value FORMAT A27

COLUMN timestamp_with_time_zone_value FORMAT A32

COLUMN blob_value FORMAT A20

COLUMN clob_value FORMAT A10

COLUMN nclob_value FORMAT A10

SELECT *

FROM   redaction_values_for_type_full;

* redact policy 조회

SET LINESIZE 200

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN policy_name FORMAT A30

COLUMN expression FORMAT A30

COLUMN policy_description FORMAT A20

SELECT object_owner,

       object_name,

       policy_name,

       expression,

       enable,

       policy_description

FROM   redaction_policies

ORDER BY 1, 2, 3;

* redact 컬럼 조회

SET LINESIZE 300 VERIFY OFF

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN column_name FORMAT A30

COLUMN function_parameters FORMAT A30

COLUMN regexp_pattern FORMAT A30

COLUMN regexp_replace_string FORMAT A30

COLUMN column_description FORMAT A20

SELECT object_owner,

       object_name,

       column_name,

       function_type,

       function_parameters,

       regexp_pattern,

       regexp_replace_string,

       regexp_position,

       regexp_occurrence,

       regexp_match_parameter,

       column_description

FROM   redaction_columns

WHERE  object_owner = DECODE(UPPER('&1'), 'ALL', object_owner, UPPER('&1'))

AND    object_name  = DECODE(UPPER('&2'), 'ALL', object_name, UPPER('&2'))

ORDER BY 1, 2, 3;

2) modify and drop

BEGIN

   DBMS_REDACT.alter_policy (

    object_schema => 'hr',

    object_name   => 'employees',

    policy_name   => 'mask_emp_id_nums'

    action              => DBMS_REDACT.modify_column,

    column_name         => 'card_no',

    function_type       => DBMS_REDACT.partial,

    function_parameters => '1,1,12'

  );

END;

/

   

BEGIN

  DBMS_REDACT.alter_policy (

    object_schema => 'hr',

    object_name   => 'employees',

    policy_name   => 'mask_emp_id_nums'

    action              => DBMS_REDACT.modify_expression,

    column_name         => 'card_no',

    expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''TEST'''

  );

END;

/

BEGIN

  DBMS_REDACT.drop_policy (

    object_schema => 'hr',

    object_name   => 'employees',

    policy_name   => 'mask_emp_id_nums'

  );

END;

/