ORACLE ASO 데이타 마스킹 방법

Data Redaction / Display Masking / 표시제한조치

Advanced Security Option에 추가

동적 Display Masking 기능

☞ 사용자, IP, application context 등을 기준으로 중요 데이타에 대한 Redaction을 적용하여 인가되지 않은 사용자가 해당데이타를 조회할 때 동적으로 변조된 데이타를 보여 줍니다.

☞ Application의 수정이나 구성 변경이 필요 없습니다. 

☞ Oracle Database에서 사용된 데이타 타입은 모두 지원 됩니다.

☞ 최소 부하

Oracle ASO 데이타 마스킹 구현방법 

1. Redaction 정책을 통한 실행과 중지

Customers  테이블의  중요  정보  (security_id,  credit_card_id,  birth_year)를 redaction 처리. 오직 customers 테이블의 소유자 (BMT)만 오리지널 데이터 확인DBMS_REDACTION 패키지를 이용하여 

수행 Redaction 생성 및 실행을 위한 DBMS_REDACT(ADD, ALTER, DISABLE, DROP, ENABLE, UPDATE 포함) 패키지를

 bmt유저에게 권한 할당을 해줍니다.

conn / as sysdba

alter user scott identified by tiger account unlock;

create user bmt identified by bmt;

grant connect, resource to bmt;

grant select on redaction_policies to bmt;

grant select on redaction_columns to bmt;

grant execute on dbms_redact to bmt;

conn bmt/bmt

create table customers (

   customer_id       number(10),

   customer_name     varchar2(20),

   birth_year        varchar2(2),

   birth_month       varchar2(2),

   birth_day         varchar2(2),

   security_id       varchar2(14),

   card_company_id   varchar2(4),

   credit_card_id    varchar2(20),

   phone1            varchar2(15),

   phone2            varchar2(15),

   zip               varchar2(10),

   address01         varchar2(256),

   address02         varchar2(256)

);

테스트 데이터 생성

conn /as sysdba

grant select on bmt.customers to scott;

2. Redaction Policy 생성

Customers 테이블의 중요 정보 (security_id, credit_card_id, birth_year)를 위한 redaction policy를 생성합니다.

conn bmt/bmt

--============================================================================

--Redaction 적용 1 => security_id                 *BMT

--=========================================================================================

BEGIN

 DBMS_REDACT.ADD_POLICY(

   object_schema       => 'bmt', 

   object_name         => 'customers', 

   column_name         => 'security_id',

   policy_name         => 'redact_cust_ssns2', 

   

   function_type       => DBMS_REDACT.PARTIAL,

   function_parameters => 'VVVVVVFVVVVVVV,VVVVVV-VVVVVVV,*,7,13',

   expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') NOT LIKE ''BMT''',

   policy_description  => 'Partially redacts Security numbers',

   column_description  => 'ssn contains character Social Security numbers');

END;

/

--=========================================================================================

--Redaction 적용 2 => CREDIT_CARD_ID              *BMT

--=========================================================================================

BEGIN

 DBMS_REDACT.ALTER_POLICY(

   object_schema       => 'bmt', 

   object_name         => 'customers', 

   policy_name         => 'redact_cust_ssns2', 

   action              => dbms_redact.ADD_COLUMN,

   column_name         => 'CREDIT_CARD_ID',

   function_type       => DBMS_REDACT.PARTIAL,

   function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,11,16',

   expression          => '1=1',

   policy_description  => 'Partially redacts Card numbers',

   column_description  => 'card contains character Card numbers');

END;

/

--=========================================================================================

--Redaction 적용 3 => BIRTH_YEAR               *BMT

--=========================================================================================

BEGIN

 DBMS_REDACT.ALTER_POLICY(

   object_schema       => 'bmt', 

   object_name         => 'customers', 

   policy_name         => 'redact_cust_ssns2', 

   action              => dbms_redact.ADD_COLUMN,

   column_name         => 'BIRTH_YEAR',

   function_type       => DBMS_REDACT.RANDOM,

   expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') NOT LIKE ''BMT'''

);

END;

/

3. Redaction 적용 table 조회

실제로 테이블에 데이터가 redaction되어 조회되는지를 확인합니다.

BMT계정으로 조회

conn bmt/bmt

set line 200

col customer_id for 9999

col customer_name for a15

col credit_card_id for a20

col security_id for a15

col birth_year for 99

col birth_month for 99

col birth_day for 99

select customer_id, customer_name, birth_year, birth_month, birth_day, credit_card_id, security_id from customers where customer_id<10 ;

SCOTT 계정으로 조회

conn scott/tiger

set line 200

col customer_id for 9999

col customer_name for a15

col credit_card_id for a20

col security_id for a15

col birth_year for 99

col birth_month for 99

col birth_day for 99

select customer_id, customer_name, birth_year, birth_month, birth_day, credit_card_id, security_id from bmt.customers where customer_id < 10 ;

4. Redaction Policy Disable/Enable/Drop

다음과 같이, 생성된 Redaction policy를 Disable/Enable/Drop 할 수 있습니다.

conn bmt/bmt

BEGIN

  DBMS_REDACT.DISABLE_POLICY (

    object_schema  => 'bmt',

    object_name    => 'customers',

    policy_name    => 'redact_cust_ssns2');

END;

/

conn bmt/bmt

BEGIN

  DBMS_REDACT.ENABLE_POLICY (

    object_schema  => 'bmt',

    object_name    => 'customers',

    policy_name    => 'redact_cust_ssns2');

END;

/

conn bmt/bmt

BEGIN

DBMS_REDACT.drop_policy(object_schema => 'BMT'

,object_name => 'CUSTOMERS'

,policy_name => 'redact_cust_ssns2'

);

END;

/