{"id":1339,"date":"2016-08-29T10:50:17","date_gmt":"2016-08-29T01:50:17","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1339"},"modified":"2018-09-11T14:32:44","modified_gmt":"2018-09-11T05:32:44","slug":"oracle-aso-%eb%8d%b0%ec%9d%b4%ed%83%80-%eb%a7%88%ec%8a%a4%ed%82%b9-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1339","title":{"rendered":"ORACLE ASO \ub370\uc774\ud0c0 \ub9c8\uc2a4\ud0b9 \ubc29\ubc95"},"content":{"rendered":"

Data Redaction \/ Display Masking \/ \ud45c\uc2dc\uc81c\ud55c\uc870\uce58<\/p>\n

<\/p>\n

<\/p>\n

<\/p>\n

<\/p>\n


<\/span><\/p>\n

Advanced Security Option\uc5d0 \ucd94\uac00<\/u><\/b><\/span><\/p>\n

<\/p>\n

\ub3d9\uc801 Display Masking \uae30\ub2a5<\/b><\/span><\/p>\n


<\/span><\/p>\n

\u261e \uc0ac\uc6a9\uc790, IP, application context \ub4f1\uc744 \uae30\uc900\uc73c\ub85c \uc911\uc694 \ub370\uc774\ud0c0\uc5d0 <\/span>\ub300\ud55c Redaction\uc744 \uc801\uc6a9\ud558\uc5ec \uc778\uac00\ub418\uc9c0 \uc54a\uc740 \uc0ac\uc6a9\uc790\uac00 \ud574\ub2f9<\/span>\ub370\uc774\ud0c0\ub97c \uc870\ud68c\ud560 \ub54c \ub3d9\uc801\uc73c\ub85c \ubcc0\uc870\ub41c \ub370\uc774\ud0c0\ub97c \ubcf4\uc5ec \uc90d\ub2c8\ub2e4.<\/span><\/b><\/p>\n

\u261e Application\uc758 \uc218\uc815\uc774\ub098 \uad6c\uc131 \ubcc0\uacbd\uc774 \ud544\uc694 \uc5c6\uc2b5\ub2c8\ub2e4. <\/b><\/span><\/p>\n

\u261e Oracle Database\uc5d0\uc11c \uc0ac\uc6a9\ub41c \ub370\uc774\ud0c0 \ud0c0\uc785\uc740 \ubaa8\ub450 \uc9c0\uc6d0 \ub429\ub2c8\ub2e4.<\/b><\/span><\/p>\n

\u261e \ucd5c\uc18c \ubd80\ud558<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

Oracle ASO \ub370\uc774\ud0c0 \ub9c8\uc2a4\ud0b9 <\/span>\uad6c\ud604\ubc29\ubc95 <\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

1. Redaction \uc815\ucc45\uc744 \ud1b5\ud55c \uc2e4\ud589\uacfc \uc911\uc9c0<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

Customers  \ud14c\uc774\ube14\uc758  \uc911\uc694  \uc815\ubcf4  (security_id,  credit_card_id,  birth_year)\ub97c <\/b><\/span>redaction \ucc98\ub9ac. \uc624\uc9c1 customers \ud14c\uc774\ube14\uc758 \uc18c\uc720\uc790 (BMT)\ub9cc \uc624\ub9ac\uc9c0\ub110 \ub370\uc774\ud130 \ud655\uc778<\/span><\/b>DBMS_REDACTION \ud328\ud0a4\uc9c0\ub97c \uc774\uc6a9\ud558\uc5ec <\/b><\/p>\n

\uc218\ud589 <\/b>Redaction \uc0dd\uc131 \ubc0f \uc2e4\ud589\uc744 \uc704\ud55c DBMS_REDACT(ADD, ALTER, DISABLE, DROP, <\/b>ENABLE, UPDATE \ud3ec\ud568) \ud328\ud0a4\uc9c0\ub97c<\/b><\/p>\n

 bmt\uc720\uc800\uc5d0\uac8c \uad8c\ud55c \ud560\ub2f9\uc744 \ud574\uc90d\ub2c8\ub2e4.<\/b><\/p>\n


<\/b><\/p>\n


<\/b><\/p>\n


<\/b><\/p>\n

conn \/ as sysdba<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

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

create user bmt identified by bmt;<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

grant connect, resource to bmt;<\/span><\/b><\/span><\/p>\n

grant select on redaction_policies to bmt;<\/span><\/b><\/span><\/p>\n

grant select on redaction_columns to bmt;<\/span><\/b><\/span><\/p>\n

grant execute on dbms_redact to bmt;<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

conn bmt\/bmt<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

create table customers (<\/span><\/b><\/span><\/p>\n

   customer_id       number(10),<\/span><\/b><\/span><\/p>\n

   customer_name     varchar2(20),<\/span><\/b><\/span><\/p>\n

   birth_year        varchar2(2),<\/span><\/b><\/span><\/p>\n

   birth_month       varchar2(2),<\/span><\/b><\/span><\/p>\n

   birth_day         varchar2(2),<\/span><\/b><\/span><\/p>\n

   security_id       varchar2(14),<\/span><\/b><\/span><\/p>\n

   card_company_id   varchar2(4),<\/span><\/b><\/span><\/p>\n

   credit_card_id    varchar2(20),<\/span><\/b><\/span><\/p>\n

   phone1            varchar2(15),<\/span><\/b><\/span><\/p>\n

   phone2            varchar2(15),<\/span><\/b><\/span><\/p>\n

   zip               varchar2(10),<\/span><\/b><\/span><\/p>\n

   address01         varchar2(256),<\/span><\/b><\/span><\/p>\n

   address02         varchar2(256)<\/span><\/b><\/span><\/p>\n<\/p>\n

);<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

\ud14c\uc2a4\ud2b8 \ub370\uc774\ud130 \uc0dd\uc131<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

conn \/as sysdba<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n<\/p>\n

grant select on bmt.customers to scott;<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

2. <\/span><\/b><\/span>Redaction Policy \uc0dd\uc131<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

Customers \ud14c\uc774\ube14\uc758 \uc911\uc694 \uc815\ubcf4 (security_id, credit_card_id, birth_year)\ub97c \uc704\ud55c <\/b><\/span>redaction policy\ub97c \uc0dd\uc131\ud569\ub2c8\ub2e4.<\/b><\/p>\n


<\/b><\/p>\n


<\/b><\/span><\/p>\n

conn bmt\/bmt<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

–============================================================================<\/span><\/b><\/span><\/p>\n

–Redaction \uc801\uc6a9 1 => security_id                 *BMT<\/span><\/b><\/span><\/p>\n

–=========================================================================================<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

BEGIN<\/span><\/b><\/span><\/p>\n

 DBMS_REDACT.ADD_POLICY(<\/span><\/b><\/span><\/p>\n

   object_schema       => ‘bmt’, <\/span><\/b><\/span><\/p>\n

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

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

   policy_name         => ‘redact_cust_ssns2’, <\/span><\/b><\/span><\/p>\n

   <\/span><\/b><\/span><\/p>\n

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

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

   expression          => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) NOT LIKE ”BMT”’,<\/span><\/b><\/span><\/p>\n

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

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

END;<\/span><\/b><\/span><\/p>\n

\/<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

–=========================================================================================<\/span><\/b><\/span><\/p>\n

–Redaction \uc801\uc6a9 2 => CREDIT_CARD_ID              *BMT<\/span><\/b><\/span><\/p>\n

–=========================================================================================<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

BEGIN<\/span><\/b><\/span><\/p>\n

 DBMS_REDACT.ALTER_POLICY(<\/span><\/b><\/span><\/p>\n

   object_schema       => ‘bmt’, <\/span><\/b><\/span><\/p>\n

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

   policy_name         => ‘redact_cust_ssns2’, <\/span><\/b><\/span><\/p>\n

   action              => dbms_redact.ADD_COLUMN,<\/span><\/b><\/span><\/p>\n

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

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

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

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

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

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

END;<\/span><\/b><\/span><\/p>\n

\/<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

–=========================================================================================<\/span><\/b><\/span><\/p>\n

–Redaction \uc801\uc6a9 3 => BIRTH_YEAR               *BMT<\/span><\/b><\/span><\/p>\n

–=========================================================================================<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

BEGIN<\/span><\/b><\/span><\/p>\n

 DBMS_REDACT.ALTER_POLICY(<\/span><\/b><\/span><\/p>\n

   object_schema       => ‘bmt’, <\/span><\/b><\/span><\/p>\n

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

   policy_name         => ‘redact_cust_ssns2’, <\/span><\/b><\/span><\/p>\n

   action              => dbms_redact.ADD_COLUMN,<\/span><\/b><\/span><\/p>\n

   column_name         => ‘BIRTH_YEAR’,<\/span><\/b><\/span><\/p>\n

   function_type       => DBMS_REDACT.RANDOM,<\/span><\/b><\/span><\/p>\n

   expression          => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) NOT LIKE ”BMT”’<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

);<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

END;<\/span><\/b><\/span><\/p>\n<\/p>\n

\/<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

3. Redaction \uc801\uc6a9 table \uc870\ud68c<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

\uc2e4\uc81c\ub85c \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud130\uac00 redaction\ub418\uc5b4 \uc870\ud68c\ub418\ub294\uc9c0\ub97c \ud655\uc778\ud569\ub2c8\ub2e4.<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

BMT\uacc4\uc815\uc73c\ub85c \uc870\ud68c<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

conn bmt\/bmt<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

set line 200<\/span><\/b><\/span><\/p>\n

col customer_id for 9999<\/span><\/b><\/span><\/p>\n

col customer_name for a15<\/span><\/b><\/span><\/p>\n

col credit_card_id for a20<\/span><\/b><\/span><\/p>\n

col security_id for a15<\/span><\/b><\/span><\/p>\n

col birth_year for 99<\/span><\/b><\/span><\/p>\n

col birth_month for 99<\/span><\/b><\/span><\/p>\n

col birth_day for 99<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n<\/p>\n

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


<\/b><\/span><\/p>\n

<\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

SCOTT \uacc4\uc815\uc73c\ub85c \uc870\ud68c<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

<\/p>\n

conn scott\/tiger<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

set line 200<\/span><\/b><\/span><\/p>\n

col customer_id for 9999<\/span><\/b><\/span><\/p>\n

col customer_name for a15<\/span><\/b><\/span><\/p>\n

col credit_card_id for a20<\/span><\/b><\/span><\/p>\n

col security_id for a15<\/span><\/b><\/span><\/p>\n

col birth_year for 99<\/span><\/b><\/span><\/p>\n

col birth_month for 99<\/span><\/b><\/span><\/p>\n

col birth_day for 99<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n<\/p>\n

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


<\/b><\/span><\/p>\n

<\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

4. Redaction Policy Disable\/Enable\/Drop<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

\ub2e4\uc74c\uacfc \uac19\uc774, \uc0dd\uc131\ub41c Redaction policy\ub97c Disable\/Enable\/Drop \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/b><\/span><\/p>\n

<\/p>\n

conn bmt\/bmt<\/span><\/b><\/span><\/p>\n

BEGIN<\/span><\/b><\/span><\/p>\n

  DBMS_REDACT.DISABLE_POLICY (<\/span><\/b><\/span><\/p>\n

    object_schema  => ‘bmt’,<\/span><\/b><\/span><\/p>\n

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

    policy_name    => ‘redact_cust_ssns2’);<\/span><\/b><\/span><\/p>\n

END;<\/span><\/b><\/span><\/p>\n<\/p>\n

\/<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

conn bmt\/bmt<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

BEGIN<\/span><\/b><\/span><\/p>\n

  DBMS_REDACT.ENABLE_POLICY (<\/span><\/b><\/span><\/p>\n

    object_schema  => ‘bmt’,<\/span><\/b><\/span><\/p>\n

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

    policy_name    => ‘redact_cust_ssns2’);<\/span><\/b><\/span><\/p>\n

END;<\/span><\/b><\/span><\/p>\n<\/p>\n

\/<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

conn bmt\/bmt<\/span><\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n

BEGIN<\/span><\/b><\/span><\/p>\n

DBMS_REDACT.drop_policy(object_schema => ‘BMT’<\/span><\/b><\/span><\/p>\n

,object_name => ‘CUSTOMERS’<\/span><\/b><\/span><\/p>\n

,policy_name => ‘redact_cust_ssns2’<\/span><\/b><\/span><\/p>\n

);<\/span><\/b><\/span><\/p>\n

END;<\/span><\/b><\/span><\/p>\n<\/p>\n

\/<\/span><\/b><\/span><\/p>\n

<\/div>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n


<\/b><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Data Redaction \/ Display Masking \/ \ud45c\uc2dc\uc81c\ud55c\uc870\uce58 Advanced Security Option\uc5d0 \ucd94\uac00 \ub3d9\uc801 Display Masking \uae30\ub2a5 \u261e \uc0ac\uc6a9\uc790, IP, application context \ub4f1\uc744 \uae30\uc900\uc73c\ub85c \uc911\uc694 \ub370\uc774\ud0c0\uc5d0 \ub300\ud55c Redaction\uc744 \uc801\uc6a9\ud558\uc5ec \uc778\uac00\ub418\uc9c0 \uc54a\uc740 \uc0ac\uc6a9\uc790\uac00 \ud574\ub2f9\ub370\uc774\ud0c0\ub97c \uc870\ud68c\ud560 \ub54c \ub3d9\uc801\uc73c\ub85c \ubcc0\uc870\ub41c \ub370\uc774\ud0c0\ub97c \ubcf4\uc5ec \uc90d\ub2c8\ub2e4. \u261e Application\uc758 \uc218\uc815\uc774\ub098 \uad6c\uc131 \ubcc0\uacbd\uc774 \ud544\uc694 \uc5c6\uc2b5\ub2c8\ub2e4.  \u261e Oracle Database\uc5d0\uc11c \uc0ac\uc6a9\ub41c \ub370\uc774\ud0c0 \ud0c0\uc785\uc740 \ubaa8\ub450 \uc9c0\uc6d0 \ub429\ub2c8\ub2e4. \u261e […]<\/p>\n","protected":false},"author":1,"featured_media":1340,"comment_status":"closed","ping_status":"closed","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":[766,767,768,769,770,771,772,773],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile10.uf.226D384E57C412CB0DC3F7.jpg?fit=1000%2C173","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1339"}],"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=1339"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1339\/revisions"}],"predecessor-version":[{"id":4153,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1339\/revisions\/4153"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/1340"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1339"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}