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. |
Hi there, after reading this amazing post i am as well delighted to share my experience here with friends.