{"id":2353,"date":"2018-01-26T16:21:01","date_gmt":"2018-01-26T07:21:01","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2353"},"modified":"2018-01-26T16:30:53","modified_gmt":"2018-01-26T07:30:53","slug":"oracle-tde-%ec%95%94%ed%98%b8%ed%99%94-%eb%a7%88%ec%8a%a4%ed%82%b9-redaction","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2353","title":{"rendered":"[Oracle TDE] \uc554\ud638\ud654 \ub9c8\uc2a4\ud0b9 (Redaction)"},"content":{"rendered":"

<\/a>Data Redaction<\/h4>\n

Data Redaction\uc744 \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 \uba3c\uc800 \uad00\ub828 \ud328\ud0a4\uc9c0\uc5d0 \ub300\ud55c \uc2e4\ud589 \uad8c\ud55c\uc774 \ubd80\uc5ec\ub418\uc5b4\uc57c \ud569\ub2c8\ub2e4. \uadf8\ub9ac\uace0 \ud14c\uc2a4\ud2b8\ub97c \uc704\ud574 scott \uc720\uc800 \uc0c1\ud0dc\ub97c \u201cunlock\u201d\uc73c\ub85c \ubcc0\uacbd\ud558\uace0 redac.customers \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \uc870\ud68c \uad8c\ud55c\uc744 scott \uc720\uc800\uc5d0\uac8c \ubd80\uc5ec\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
[oracle@New-Features-12c ~]$ sqlplus sys\/oracle_4U@pdb1 as sysdba<\/p>\n

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 28 12:40:04 2015<\/p>\n

Copyright (c) 1982, 2014, Oracle. All rights reserved.<\/p>\n

Connected to:<\/p>\n

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production<\/p>\n

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics<\/p>\n

and Real Application Testing options<\/p>\n

SQL> grant execute on dbms_redact to redac;<\/p>\n

Grant succeeded.<\/p>\n

SQL> alter user scott identified by tiger account unlock;<\/p>\n

User altered.<\/p>\n

SQL> grant select on redac.customers to scott;<\/p>\n

Grant succeeded.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\uba3c\uc800 Redaction \uc124\uc815\uc774 \ub418\uc9c0 \uc54a\uc740 \uc0c1\ud0dc\uc5d0\uc11c scott \uc720\uc800\ub85c CUSTOMERS \ud14c\uc774\ube14\uc744 \uc870\ud68c\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \ud45c\uc2dc \uc81c\ud55c \uc5c6\uc774 \ubaa8\ub4e0 \uc815\ubcf4\uac00 \uadf8\ub300\ub85c \ucd9c\ub825\ub418\uace0 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> connect scott\/tiger@pdb1<\/p>\n

Connected.<\/p>\n

SQL> set line 120<\/p>\n

SQL> col customer_id for 9999<\/p>\n

SQL> col customer_name for a15<\/p>\n

SQL> col credit_card_id for a20<\/p>\n

SQL> col security_id for a15<\/p>\n

SQL> col birth_year for 99<\/p>\n

SQL> col birth_month for 99<\/p>\n

SQL> col birth_day for 99<\/p>\n

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID<\/p>\n

———– ————— — — — ——————– —————<\/p>\n

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-4172246<\/p>\n

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-4434278<\/p>\n

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-4331755<\/p>\n

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-3376442<\/p>\n

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-1759576<\/p>\n

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-4696266<\/p>\n

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-2369729<\/p>\n

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-3127247<\/p>\n

8 rows selected.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

REDAC \uc720\uc800\ub85c \uc811\uc18d\ud558\uc5ec CUSTOMERS \ud14c\uc774\ube14\uc758<\/p>\n

security_id \uce7c\ub7fc \ub370\uc774\ud130\uc5d0 \ub300\ud574<\/p>\n

7\ubc88\uc9f8\ubd80\ud130 13\ubc88\uc9f8\uae4c\uc9c0 \uac12\uc740 * \ud45c\uc2dc\ub85c<\/p>\n

REDAC \uc720\uc800\uac00 \uc544\ub2cc \uc720\uc800\uc5d0 \ub300\ud574\uc11c<\/p>\n

\ubd80\ubd84\uc801\uc778 Redaction\uc744 \uc801\uc6a9\ud558\ub3c4\ub85d<\/p>\n

Redaction Policy\ub97c DBMS_REDACT \ud328\ud0a4\uc9c0\ub97c \uc774\uc6a9\ud574\uc11c \uc0dd\uc131\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> connect redac\/redac@pdb1<\/p>\n

Connected.<\/p>\n

SQL> BEGIN<\/p>\n

DBMS_REDACT.ADD_POLICY(<\/p>\n

object_schema => ‘redac’,<\/p>\n

object_name => ‘customers’,<\/p>\n

column_name => ‘security_id’,<\/p>\n

policy_name => ‘redact_cust_private_info’,<\/p>\n

function_type => DBMS_REDACT.PARTIAL,<\/p>\n

function_parameters => ‘VVVVVVFVVVVVVV,VVVVVV-VVVVVVV,*,7,13’,<\/p>\n

expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) != ”REDAC”’,<\/p>\n

policy_description => ‘Partially redacts Security numbers’,<\/p>\n

column_description => ‘ssn contains character Social Security numbers’);<\/p>\n

END;<\/p>\n

\/<\/p>\n

PL\/SQL procedure successfully completed.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\uc774\uc81c SCOTT \uc720\uc800\ub85c \uc811\uc18d\ud558\uc5ec CUSTOMERS \ud14c\uc774\ube14\uc758 security_id \uce7c\ub7fc\uc758 \uac12\ub4e4\uc774 \uc5b4\ub5bb\uac8c \ucd9c\ub825\ub418\ub294 \uc9c0 \ud655\uc778\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> connect scott\/tiger@pdb1;<\/p>\n

Connected.<\/p>\n

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID<\/p>\n

———– ————— — — — ——————– —————<\/p>\n

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-*******<\/p>\n

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-*******<\/p>\n

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-*******<\/p>\n

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-*******<\/p>\n

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-*******<\/p>\n

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-*******<\/p>\n

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-*******<\/p>\n

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-*******<\/p>\n

8 rows selected.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

REDAC \uc720\uc800\ub85c \uc811\uc18d\ud588\uc744 \ub54c\ub294 Redaction \uc5c6\uc774 \ubaa8\ub4e0 \uc815\ubcf4\uac00 \ucd9c\ub825\ub418\ub294 \uac83\uc744 \uc54c \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> connect redac\/redac@pdb1<\/p>\n

Connected.<\/p>\n

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID<\/p>\n

———– ————— — — — ——————– —————<\/p>\n

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-4172246<\/p>\n

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-4434278<\/p>\n

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-4331755<\/p>\n

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-3376442<\/p>\n

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-1759576<\/p>\n

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-4696266<\/p>\n

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-2369729<\/p>\n

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-3127247<\/p>\n

8 rows selected.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\uc774\ubc88\uc5d0\ub294 credit_card_id \uce7c\ub7fc\uc5d0 \ub300\ud574\uc11c\ub3c4 ALTER POLICY \ud504\ub85c\uc2dc\uc800\ub97c \uc774\uc6a9\ud558\uc5ec Redation Policy\ub97c \ubcc0\uacbd\ud558\uace0 \ud574\ub2f9 \uc815\ubcf4\uac00 \uc5b4\ub5bb\uac8c \ucd9c\ub825\ub418\ub294\uc9c0 \ud655\uc778\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \uc774\uc81c\ub294 credit_card_id \uc5d0 \ub300\ud574\uc11c\ub3c4 \ubd80\ubd84\uc801\uc778 Redaction\uc774 \uc801\uc6a9\ub41c \uac83\uc744 \uc54c \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> BEGIN<\/p>\n

DBMS_REDACT.ALTER_POLICY(<\/p>\n

object_schema => ‘redac’,<\/p>\n

object_name => ‘customers’,<\/p>\n

policy_name => ‘redact_cust_private_info’,<\/p>\n

action => dbms_redact.add_column,<\/p>\n

column_name => ‘CREDIT_CARD_ID’,<\/p>\n

function_type => dbms_redact.partial,<\/p>\n

function_parameters => ‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,11,16’,<\/p>\n

expression => ‘1=1’,<\/p>\n

policy_description => ‘Partially redacts Card numbers’,<\/p>\n

column_description => ‘card contains character Card numbers’);<\/p>\n

END;<\/p>\n

\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID<\/p>\n

———– ————— — — — ——————– —————<\/p>\n

2 KimJiSeok 77 03 05 2090-9220-99**-**** 770305-*******<\/p>\n

3 ShimMunWan 47 08 23 5400-1269-55**-**** 470823-*******<\/p>\n

4 KwonJinKon 52 02 14 3477-4924-80**-**** 520214-*******<\/p>\n

5 KangMyungDo 56 10 10 2090-9277-50**-**** 561010-*******<\/p>\n

6 ParkSunDo 46 04 23 2400-6864-70**-**** 460423-*******<\/p>\n

7 ChunKiKoo 66 08 24 9800-8074-60**-**** 660824-*******<\/p>\n

8 ParkYoungKoo 41 06 13 9800-9913-91**-**** 410613-*******<\/p>\n

9 NaYunSoo 46 02 29 1200-4269-23**-**** 460229-*******<\/p>\n

8 rows selected.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\uc0dd\uc131\ud55c Redaction Policy\ub294 disable \uc2dc\ud0ac \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4. REDAC \uc720\uc800\ub85c DISABLE_POLICY \ud504\ub85c\uc2dc\uc800\ub85c \uc55e\uc11c \uc0dd\uc131\ud55c Policy\ub97c disable \uc2dc\ud0b5\ub2c8\ub2e4. \uad00\ub828 \ub515\uc154\ub108\ub9ac \ubdf0 REDACTION_POLICIES \uc5d0\uc11c\ub3c4 \uc774\ub97c \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> connect redac\/redac@pdb1<\/p>\n

Connected.<\/p>\n

SQL> BEGIN<\/p>\n

DBMS_REDACT.DISABLE_POLICY (<\/p>\n

object_schema => ‘redac’,<\/p>\n

object_name => ‘customers’,<\/p>\n

policy_name => ‘redact_cust_private_info’);<\/p>\n

END;<\/p>\n

\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> col object_name for a20<\/p>\n

SQL> col policy_name for a30<\/p>\n

SQL> select object_name,policy_name,enable from redaction_policies;<\/p>\n

OBJECT_NAME POLICY_NAME ENABLE<\/p>\n

——————– —————————— ——-<\/p>\n

CUSTOMERS redact_cust_private_info NO<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

SCOTT \uc720\uc800\ub85c \uc811\uc18d\ud574\uc11c CUSTOMERS \ud14c\uc774\ube14\uc744 \uc870\ud68c\ud558\uba74 Redaction\uc774 \ud574\uc81c\ub41c \uc54c \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> connect scott\/tiger@pdb1<\/p>\n

Connected.<\/p>\n

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

CUSTOMER_ID CUSTOMER_NAME BI BI BI CREDIT_CARD_ID SECURITY_ID<\/p>\n

———– ————— — — — ——————– —————<\/p>\n

2 KimJiSeok 77 03 05 2090-9220-9931-7156 770305-4172246<\/p>\n

3 ShimMunWan 47 08 23 5400-1269-5566-6856 470823-4434278<\/p>\n

4 KwonJinKon 52 02 14 3477-4924-8034-3893 520214-4331755<\/p>\n

5 KangMyungDo 56 10 10 2090-9277-5065-6140 561010-3376442<\/p>\n

6 ParkSunDo 46 04 23 2400-6864-7017-4783 460423-1759576<\/p>\n

7 ChunKiKoo 66 08 24 9800-8074-6037-8236 660824-4696266<\/p>\n

8 ParkYoungKoo 41 06 13 9800-9913-9122-6702 410613-2369729<\/p>\n

9 NaYunSoo 46 02 29 1200-4269-2393-7732 460229-3127247<\/p>\n

8 rows selected.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

DROP_POLICY \ud504\ub85c\uc2dc\uc800\ub85c \uc0dd\uc131\ud55c Redation Policy\ub97c \uc0ad\uc81c\ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
SQL> BEGIN<\/p>\n

DBMS_REDACT.DROP_POLICY (<\/p>\n

object_schema => ‘redac’,<\/p>\n

object_name => ‘customers’,<\/p>\n

policy_name => ‘redact_cust_private_info’);<\/p>\n

END;<\/p>\n

\/<\/p>\n

PL\/SQL procedure successfully completed.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

Data Redaction Data Redaction\uc744 \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 \uba3c\uc800 \uad00\ub828 \ud328\ud0a4\uc9c0\uc5d0 \ub300\ud55c \uc2e4\ud589 \uad8c\ud55c\uc774 \ubd80\uc5ec\ub418\uc5b4\uc57c \ud569\ub2c8\ub2e4. \uadf8\ub9ac\uace0 \ud14c\uc2a4\ud2b8\ub97c \uc704\ud574 scott \uc720\uc800 \uc0c1\ud0dc\ub97c \u201cunlock\u201d\uc73c\ub85c \ubcc0\uacbd\ud558\uace0 redac.customers \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \uc870\ud68c \uad8c\ud55c\uc744 scott \uc720\uc800\uc5d0\uac8c \ubd80\uc5ec\ud569\ub2c8\ub2e4. [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 […]<\/p>\n","protected":false},"author":1,"featured_media":2338,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[336],"tags":[760,761,824,762],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/oracle-cloud-logo.png?fit=300%2C194","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2353"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2353"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2353\/revisions"}],"predecessor-version":[{"id":2355,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2353\/revisions\/2355"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2338"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2353"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}