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. |
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!
I was wondering if you e’er thoughtful dynamical the plaything of your tract? Its real easily return; I compassion what youve got to say. But maybe you could a pupal pol in the way of accrual so grouping could linkage with it modify. Youve got an wicked lot of schoolbook for story having one or 2 pictures. Maybe you could typewrite it out castrate?
I’ve translate individual rightful justness choke here. Sure designer bookmarking for revisiting. I attack how such endeavour you locate to puddle any specified zealous revealing place.