{"id":2423,"date":"2018-01-27T20:00:55","date_gmt":"2018-01-27T11:00:55","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2423"},"modified":"2021-10-29T22:59:51","modified_gmt":"2021-10-29T13:59:51","slug":"db-security-%ea%b3%b5%ec%a7%9c%eb%a1%9c-db-%ec%95%94%ed%98%b8%ed%99%94-%ed%95%98%eb%8a%94-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2423","title":{"rendered":"[DB Security] \uacf5\uc9dc\ub85c DB \uc554\ud638\ud654 \ud558\ub294 \ubc29\ubc95"},"content":{"rendered":"

\ud0c0 \uc0ac\uc774\ud2b8\uc5d0\uc11c \uc0ac\uc6a9 \ud588\ub358 \uc624\ub77c\ud074 DB\uc758 \uae30\ubcf8 \uc81c\uacf5 \uc554\ud638\ud654 \ud568\uc218\ub97c \uc774\uc6a9\ud574\uc11c<\/p>\n

\ucd94\uac00 \uc554\ud638\ud654 \uc194\ub8e8\uc158 \uad6c\ub9e4 \uc5c6\uc774 \uac1c\uc778\uc815\ubcf4 \ub370\uc774\ud130 \ub97c\u00a0 \uceec\ub7fc \ub2e8\uc704 \uc554\ud638\ud654 \ud558\ub294\u00a0 \ubc29\ubc95 \uc785\ub2c8\ub2e4.<\/span><\/p>\n

\uc591\ubc29\ud5a5 \uc554\ud638\ud654<\/span> <\/strong><\/p>\n

< \uc694\uc57d ><\/p>\n

\uc544\ub798 \ubc29\uc2dd\uc73c\ub85c \ud0a4 \ud14c\uc774\ube14\uacfc \uc554\ud638\ud654 \/ \ubcf5\ud638\ud654 \ud568\uc218\ub97c \ub9cc\ub4e0 \ub2e4\uc74c \uac1c\uc778\uc815\ubcf4 \ub370\uc774\ud130\ub97c<\/p>\n

\uc785\ub825 , \uc218\uc815 , \uc870\ud68c \ud558\ub294 \ubd80\ubd84\uc744 \ubaa8\ub450 \ubcc0\uacbd ( SQL , SP , java \ub4f1\ub4f1 ) \ud574\uc57c \ud569\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

< \ubc29\uc2dd\uc124\uba85 ><\/p>\n

1.\u00a0\u00a0\u00a0 Key \ud14c\uc774\ube14\uc744 \uc77c\ub2e8 \ub9cc\ub4e4\uace0 key \ub370\uc774\ud130\ub97c \ud55c\uac74 \ub123\uc2b5\ub2c8\ub2e4. ( \uc784\uc758\ub85c )<\/p>\n

2.\u00a0\u00a0\u00a0 Encode param \ud568\uc218 \uc640 decode param \ud568\uc218\ud30c\uc77c\uc744 \ubcf4\uba74 \uc624\ub77c\ud074\uc758 \uae30\ubcf8 \ud568\uc218\ub97c \uc774\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n

3.\u00a0\u00a0\u00a0 Encode param \ud568\uc218\ub294 \uc554\ud638\ud654 \ud560 \uceec\ub7fc \ub370\uc774\ud130\ub97c \uc785\ub825 \ub610\ub294 \uc218\uc815\ud558\ub294 \ucffc\ub9ac\uc5d0 \uc0ac\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n

4.\u00a0\u00a0\u00a0 Decode param \ud568\uc218\ub294 \uc554\ud638\ud654 \ud55c \uceec\ub7fc \ub370\uc774\ud130\ub97c \uc870\ud68c \ud558\ub294\ub370 \uc0ac\uc6a9 \ud558\ub294 \ucffc\ub9ac\uc5d0 \uc0ac\uc6a9 \ud569\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

< \uc8fc\uc758 ><\/p>\n

\ub9cc\uc57d \uc0ac\ub0b4 \ubcf4\uc548 \uaddc\uce59\uc0c1 \ud0a4 \ud14c\uc774\ube14\uc744 \uac19\uc740 \uba38\uc2e0\uc5d0 \ub193\uc73c\uba74 \uc548\ub418\ub294 \uacbd\uc6b0 \ub514\ube44\ub9c1\ud06c\ub97c \uc368\uc11c \ub2e4\ub978 DB\uc5d0 \ub123\uace0 \uc870\ud68c \ud558\uac8c \ub429\ub2c8\ub2e4.
\n(\uc8fc\uc758 : \ud2b8\ub798\ud53d\uc774 \ubab0\ub9b4\uacbd\uc6b0 \uc131\ub2a5 \ubcf4\uc7a5\uc774 \uc548\ub429\ub2c8\ub2e4. )<\/p>\n

 <\/p>\n

1. Key \ud14c\uc774\ube14 \uc0dd\uc131 \ubc0f Key Data Insert<\/p>\n

CREATE TABLE SY_SECURE_KEY
\n(
\nKEY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LONG RAW
\n)
\nTABLESPACE USERS
\nPCTFREE 10
\nPCTUSED 0
\nINITRANS 1
\nMAXTRANS 255
\nSTORAGE
\n(
\nINITIAL 65536
\nMINEXTENTS 1
\nMAXEXTENTS UNLIMITED
\nBUFFER_POOL DEFAULT
\n)
\nLOGGING ;<\/p>\n

 <\/p>\n

INSERT INTO SY_SECURE_KEY VALUES (\u20184E5065664D73337546314\u2026\u2026\u2026..0000 \uc784\uc758 \ud0a4\uac12 \uc785\ub825 <\/span>\u2019) ;<\/p>\n

 <\/p>\n

2. \uc554\ud638\ud654 \ud568\uc218 \uc0dd\uc131<\/p>\n

CREATE OR REPLACE function encodeParam
\n(
\np_in\u00a0\u00a0\u00a0 in varchar2
\n)
\nreturn varchar2 is
\nl_return_val varchar2(2048) ;
\nl_enc_val raw (2048);
\nl_mod\u00a0\u00a0\u00a0\u00a0 number := DBMS_CRYPTO.ENCRYPT_AES256
\n+ DBMS_CRYPTO.CHAIN_ECB
\n+ DBMS_CRYPTO.PAD_PKCS5;
\np_key raw (65) ;
\nbegin<\/p>\n

if p_in is not null and (isEncoded(p_in)=0) then<\/p>\n

select key into p_key from sy_secure_key ;<\/p>\n

l_enc_val := DBMS_CRYPTO.encrypt( UTL_RAW.cast_to_raw(p_in), l_mod, p_key );
\nl_return_val := UTL_RAW.cast_to_varchar2( utl_encode.base64_encode(l_enc_val) ) ;<\/p>\n

else
\nl_return_val := p_in ;
\nend if ;<\/p>\n

return l_return_val;<\/p>\n

exception<\/p>\n

when others then
\nl_return_val := p_in ;<\/p>\n

return l_return_val;
\nend;
\n\/<\/p>\n

 <\/p>\n

3. \ubcf5\ud638\ud654 \uc554\ud638 \ud568\uc218<\/p>\n

CREATE OR REPLACE function decodeParam
\n(
\np_in\u00a0\u00a0\u00a0 in varchar2
\n)
\nreturn varchar2 is
\nl_return_val varchar2(2048) ;
\nl_dec_val raw (2048);
\nl_mod\u00a0\u00a0\u00a0\u00a0 number := DBMS_CRYPTO.ENCRYPT_AES256
\n+ DBMS_CRYPTO.CHAIN_ECB
\n+ DBMS_CRYPTO.PAD_PKCS5;
\np_key raw (65) ;
\nbegin<\/p>\n

if p_in is not null and (isEncoded(p_in)>0) then<\/p>\n

select key into p_key from sy_secure_key ;<\/p>\n

l_dec_val := utl_encode.base64_decode(utl_raw.cast_to_raw(p_in)) ;
\nl_dec_val := DBMS_CRYPTO.decrypt( l_dec_val, l_mod, p_key );
\nl_return_val := UTL_RAW.cast_to_varchar2( l_dec_val) ;<\/p>\n

else
\nl_return_val := p_in ;
\nend if ;<\/p>\n

return l_return_val;<\/p>\n

exception<\/p>\n

when others then
\nl_return_val := p_in ;<\/p>\n

return l_return_val;
\nend;
\n\/<\/p>\n

 <\/p>\n

 <\/p>\n

4. \uc554\ud638\ud654 \ud14c\uc774\ube14 \ub370\uc774\ud130 \uc870\ud68c \uc2dc ( \ubcf5\ud638\ud654 \uacfc\uc815 )<\/p>\n

select\u00a0 decodeParam(COL1), decodeParam(COL2)
\nfrom \uac1c\uc778\uc815\ubcf4\ud14c\uc774\ube14
\nwhere ID=’haisins’;<\/p>\n

 <\/p>\n

5. \uc554\ud638\ud654 \ud14c\uc774\ube14 \ub370\uc774\ud130 \ucd94\uac00 \uc2dc ( \uc554\ud638\ud654 \uacfc\uc815 )<\/p>\n

Insert into \uac1c\uc778\uc815\ubcf4\ud14c\uc774\ube14\u00a0 values (encodeParam(\u2018\uc8fc\ubbfc\ubc88\ud638\u2019) ;<\/p>\n

 <\/p>\n

 <\/p>\n

*.\ucc38\uace0<\/p>\n

DBMS_CRYPTO Encryption Algorithms<\/span><\/p>\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n
Name<\/span><\/th>\nDescription<\/span><\/th>\n<\/tr>\n<\/thead>\n
\n

\n

<span style="font-size: 8.7pt;">ENCRYPT_DES<\/span><\/pre>\n<\/p>\n<\/td>\n
\n

Data Encryption Standard. Block cipher. Uses key length of 56 bits.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\n

<span style="font-size: 8.7pt;">ENCRYPT_3DES_2KEY<\/span><\/pre>\n<\/p>\n<\/td>\n
\n

Data Encryption Standard. Block cipher. Operates on a block 3 times with 2 keys. Effective key length of 112 bits.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\n

<span style="font-size: 8.7pt;">ENCRYPT_3DES<\/span><\/pre>\n<\/p>\n<\/td>\n
\n

Data Encryption Standard. Block cipher. Operates on a block 3 times.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

ENCRYPT_AES128<\/span><\/p>\n<\/td>\n

\n

Advanced Encryption Standard. Block cipher. Uses 128-bit key size.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

ENCRYPT_AES192<\/span><\/p>\n<\/td>\n

\n

Advanced Encryption Standard. Block cipher. Uses 192-bit key size.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

ENCRYPT_AES256<\/span><\/p>\n<\/td>\n

\n

Advanced Encryption Standard. Block cipher. Uses 256-bit key size.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\n

<span style="font-size: 8.7pt;">ENCRYPT_RC4<\/span><\/pre>\n<\/p>\n<\/td>\n
\n

Stream cipher. Uses a secret, randomly generated key unique to each session.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

https:\/\/docs.oracle.com\/cd\/E18283_01\/appdev.112\/e16760\/d_crypto.htm<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

\ud0c0 \uc0ac\uc774\ud2b8\uc5d0\uc11c \uc0ac\uc6a9 \ud588\ub358 \uc624\ub77c\ud074 DB\uc758 \uae30\ubcf8 \uc81c\uacf5 \uc554\ud638\ud654 \ud568\uc218\ub97c \uc774\uc6a9\ud574\uc11c \ucd94\uac00 \uc554\ud638\ud654 \uc194\ub8e8\uc158 \uad6c\ub9e4 \uc5c6\uc774 \uac1c\uc778\uc815\ubcf4 \ub370\uc774\ud130 \ub97c\u00a0 \uceec\ub7fc \ub2e8\uc704 \uc554\ud638\ud654 \ud558\ub294\u00a0 \ubc29\ubc95 \uc785\ub2c8\ub2e4. \uc591\ubc29\ud5a5 \uc554\ud638\ud654 < \uc694\uc57d > \uc544\ub798 \ubc29\uc2dd\uc73c\ub85c \ud0a4 \ud14c\uc774\ube14\uacfc \uc554\ud638\ud654 \/ \ubcf5\ud638\ud654 \ud568\uc218\ub97c \ub9cc\ub4e0 \ub2e4\uc74c \uac1c\uc778\uc815\ubcf4 \ub370\uc774\ud130\ub97c \uc785\ub825 , \uc218\uc815 , \uc870\ud68c \ud558\ub294 \ubd80\ubd84\uc744 \ubaa8\ub450 \ubcc0\uacbd ( SQL , SP […]<\/p>\n","protected":false},"author":1,"featured_media":2428,"comment_status":"closed","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":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/Bull-cyber-Data-encryption-1.jpg?fit=538%2C303","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2423"}],"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=2423"}],"version-history":[{"count":3,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2423\/revisions"}],"predecessor-version":[{"id":4510,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2423\/revisions\/4510"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2428"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2423"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}