마스킹 on Oracle 12c


Data Redaction (마스킹)

Data Redaction을 사용하기 위해서는 먼저 관련 패키지에 대한 실행 권한이 부여되어야 합니다. 그리고 테스트를 위해 scott 유저 상태를 “unlock”으로 변경하고 redac.customers 테이블에 대한 조회 권한을 scott 유저에게 부여합니다.

[oracle@New-Features-12c ~]$ sqlplus sys/oracle_4U@pdb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 28 12:40:04 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> grant execute on dbms_redact to redac;

Grant succeeded.

SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> grant select on redac.customers to scott;

Grant succeeded.

먼저 Redaction 설정이 되지 않은 상태에서 scott 유저로 CUSTOMERS 테이블을 조회해 보겠습니다. 표시 제한 없이 모든 정보가 그대로 출력되고 있습니다.

SQL> connect scott/tiger@pdb1

Connected.

SQL> set line 120

SQL> col customer_id for 9999

SQL> col customer_name for a15

SQL> col credit_card_id for a20

SQL> col security_id for a15

SQL> col birth_year for 99

SQL> col birth_month for 99

SQL> col birth_day for 99

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID

———– ————— — — — ——————– —————

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-4172246

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-4434278

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-4331755

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-3376442

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-1759576

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-4696266

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-2369729

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-3127247

8 rows selected.

REDAC 유저로 접속하여 CUSTOMERS 테이블의

security_id 칼럼 데이터에 대해

7번째부터 13번째까지 값은 * 표시로

REDAC 유저가 아닌 유저에 대해서

부분적인 Redaction을 적용하도록

Redaction Policy를 DBMS_REDACT 패키지를 이용해서 생성합니다.

SQL> connect redac/redac@pdb1

Connected.

SQL> BEGIN

DBMS_REDACT.ADD_POLICY(

object_schema => ‘redac’,

object_name => ‘customers’,

column_name => ‘security_id’,

policy_name => ‘redact_cust_private_info’,

function_type => DBMS_REDACT.PARTIAL,

function_parameters => ‘VVVVVVFVVVVVVV,VVVVVV-VVVVVVV,*,7,13’,

expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) != ”REDAC”’,

policy_description => ‘Partially redacts Security numbers’,

column_description => ‘ssn contains character Social Security numbers’);

END;

/

PL/SQL procedure successfully completed.

이제 SCOTT 유저로 접속하여 CUSTOMERS 테이블의 security_id 칼럼의 값들이 어떻게 출력되는 지 확인합니다.

SQL> connect scott/tiger@pdb1;

Connected.

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID

———– ————— — — — ——————– —————

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-*******

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-*******

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-*******

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-*******

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-*******

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-*******

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-*******

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-*******

8 rows selected.

REDAC 유저로 접속했을 때는 Redaction 없이 모든 정보가 출력되는 것을 알 수 있습니다.

SQL> connect redac/redac@pdb1

Connected.

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID

———– ————— — — — ——————– —————

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-4172246

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-4434278

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-4331755

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-3376442

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-1759576

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-4696266

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-2369729

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-3127247

8 rows selected.

이번에는 credit_card_id 칼럼에 대해서도 ALTER POLICY 프로시저를 이용하여 Redation Policy를 변경하고 해당 정보가 어떻게 출력되는지 확인해 보겠습니다. 이제는 credit_card_id 에 대해서도 부분적인 Redaction이 적용된 것을 알 수 있습니다.

SQL> BEGIN

DBMS_REDACT.ALTER_POLICY(

object_schema => ‘redac’,

object_name => ‘customers’,

policy_name => ‘redact_cust_private_info’,

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;

/

PL/SQL procedure successfully completed.

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID

———– ————— — — — ——————– —————

2 KimJiSeok 77 03 05 2090-9220-99**-**** 770305-*******

3 ShimMunWan 47 08 23 5400-1269-55**-**** 470823-*******

4 KwonJinKon 52 02 14 3477-4924-80**-**** 520214-*******

5 KangMyungDo 56 10 10 2090-9277-50**-**** 561010-*******

6 ParkSunDo 46 04 23 2400-6864-70**-**** 460423-*******

7 ChunKiKoo 66 08 24 9800-8074-60**-**** 660824-*******

8 ParkYoungKoo 41 06 13 9800-9913-91**-**** 410613-*******

9 NaYunSoo 46 02 29 1200-4269-23**-**** 460229-*******

8 rows selected.

생성한 Redaction Policy는 disable 시킬 수도 있습니다. REDAC 유저로 DISABLE_POLICY 프로시저로 앞서 생성한 Policy를 disable 시킵니다. 관련 딕셔너리 뷰 REDACTION_POLICIES 에서도 이를 확인할 수 있습니다.

SQL> connect redac/redac@pdb1

Connected.

SQL> BEGIN

DBMS_REDACT.DISABLE_POLICY (

object_schema => ‘redac’,

object_name => ‘customers’,

policy_name => ‘redact_cust_private_info’);

END;

/

PL/SQL procedure successfully completed.

SQL> col object_name for a20

SQL> col policy_name for a30

SQL> select object_name,policy_name,enable from redaction_policies;

OBJECT_NAME POLICY_NAME ENABLE

——————– —————————— ——-

CUSTOMERS redact_cust_private_info NO

SCOTT 유저로 접속해서 CUSTOMERS 테이블을 조회하면 Redaction이 해제된 알 수 있습니다.

SQL> connect scott/tiger@pdb1

Connected.

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID

———– ————— — — — ——————– —————

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-4172246

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-4434278

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-4331755

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-3376442

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-1759576

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-4696266

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-2369729

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-3127247

8 rows selected.

DROP_POLICY 프로시저로 생성한 Redation Policy를 삭제할 수도 있습니다.

SQL> BEGIN

DBMS_REDACT.DROP_POLICY (

object_schema => ‘redac’,

object_name => ‘customers’,

policy_name => ‘redact_cust_private_info’);

END;

/

PL/SQL procedure successfully completed.


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

마스킹 on Oracle 12c”의 1개의 댓글

  • 2018-09-26 8:04 오전
    Permalink

    Hello there! I could have sworn I’ve been to this website before but after reading through some of the post I realized it’s new to me. Anyways, I’m definitely delighted I found it and I’ll be bookmarking and checking back often!

    댓글달기

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다