{"id":485,"date":"2012-01-30T05:15:28","date_gmt":"2012-01-30T05:15:28","guid":{"rendered":"http:\/\/brain.dailyon.net\/?p=485"},"modified":"2012-01-30T05:15:28","modified_gmt":"2012-01-30T05:15:28","slug":"oracle-ora-28231-%eb%8d%b0%ec%9d%b4%ed%84%b0%ea%b0%80-obfuscation-%ed%88%b4%ed%82%b7%ec%9c%bc%eb%a1%9c-%ec%a0%84%eb%8b%ac%eb%90%98%ec%a7%80-%ec%95%8a%ec%95%98%ec%8a%b5%eb%8b%88%eb%8b%a4","status":"publish","type":"post","link":"https:\/\/blog.goorigo.com\/?p=485","title":{"rendered":"Oracle : ORA-28231: \ub370\uc774\ud130\uac00 obfuscation \ud234\ud0b7\uc73c\ub85c \uc804\ub2ec\ub418\uc9c0 \uc54a\uc558\uc2b5\ub2c8\ub2e4."},"content":{"rendered":"

\uc554\ud638\ud654 \ud560\ub54c \ud574\ub2f9 \ubcc0\uc218\uc5d0 null\uc774 \ub4e4\uc5b4\uac00\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc5d0\ub7ec\ub97c \ub9cc\ub098\uac8c \ub41c\ub2e4.<\/p>\n

SQL> select loginid,loginpwd,CRYPTIT.DECRYPT(LOGINPWD,’key_0610′) from sawon where CRYPTIT.DECRYPT(LOGINPWD,’key_100610′)=’my_pwd’<\/p>\n

ORA-28231: \ub370\uc774\ud130\uac00 obfuscation \ud234\ud0b7\uc73c\ub85c \uc804\ub2ec\ub418\uc9c0 \uc54a\uc558\uc2b5\ub2c8\ub2e4.
\nORA-06512: “SYS.DBMS_OBFUSCATION_TOOLKIT_FFI”, \uc904 0\uc5d0\uc11c
\nORA-06512: “SYS.DBMS_OBFUSCATION_TOOLKIT”, \uc904 75\uc5d0\uc11c
\nORA-06512: “INSA.CRYPTIT”, \uc904 39\uc5d0\uc11c
\nORA-06512: \uc904 1\uc5d0\uc11c<\/p>\n

\uc6d0\uc778
\nnull \uac12\uc774 \uc788\uc73c\uba74 \uc548\ub428.<\/p>\n

\uc870\uce58
\nSQL> select loginid,loginpwd,CRYPTIT.DECRYPT(LOGINPWD,’key_0610′) from sawon where CRYPTIT.DECRYPT(LOGINPWD,’key_100610′)=’my_pwd’ and loginid is not null;<\/p>\n

=> loginid is not null; \ucd94\uac00\ub85c null \uceec\ub7fc \uc870\ud68c \uc5c6\uc774 \ucc98\ub9ac\ud558\uba74\ub428.<\/p>\n

\uac1c \uc694
\n=========
\nOracle 8i Release2(8.1.6)\uc5d0\uc11c\ub294 \ub370\uc774\ud130\ub97c \uc554\ud638\ud654\ud558\uc5ec \uc800\uc7a5\ud560 \uc218 \uc788\ub294 \ud5a5\uc0c1\ub41c
\n\uae30\ub2a5(DES Encryption)\uc744 \uc81c\uacf5\ud55c\ub2e4
\n(Oracle 8i Release3(8.1.7)\uc5d0\uc11c\ub294 Triple DES Encryption)<\/p>\n

\uc989 \uc2e0\uc6a9\uce74\ub4dc\ubc88\ud638, \ud328\uc2a4\uc6cc\ub4dc \ub4f1 \ubcf4\uc548\uc774 \ud544\uc694\ud55c \ub370\uc774\ud130\ub97c \uc554\ud638\ud654\ub41c \ud615\ud0dc\ub85c \uc800\uc7a5\ud558\uc5ec
\n\uae30\uc874\uc758 3rd Party Tool\uc774\ub098, Application Logic\uc73c\ub85c \uad6c\ud604\ud558\ub358 \uc554\ud638\ud654 \uc815\ucc45\uc744
\n\ub370\uc774\ud130\ubca0\uc774\uc2a4 \ucc28\uc6d0\uc5d0\uc11c \uad6c\ud604\ud560 \uc218 \uc788\ub3c4\ub85d \ud574\uc900\ub2e4.<\/p>\n

DBMS_OBFUSCATION_TOOLKIT
\n========================
\n\uc554\ud638\ud654 \uae30\ub2a5\uc744 \uc774\uc6a9\ud558\ub824\uba74 DBMS_OBFUSCATION_TOOLKIT\uc744 \uc774\uc6a9\ud574\uc57c \ud55c\ub2e4.<\/p>\n

\uc774 \ud328\ud0a4\uc9c0\ub294 4\uac1c\uc758 \ud504\ub85c\uc2dc\uc838\ub85c \uc774\ub8e8\uc5b4\uc838 \uc788\ub2e4.
\n– VARCHAR2 \ud0c0\uc785\uc744 Encrypt\/Decrypt\ud560 \uc218 \uc788\ub294 2\uac1c\uc758 \ud504\ub85c\uc2dc\uc838
\n– RAW \ud0c0\uc785\uc744 Encrypt\/Decrypt\ud560 \uc218 \uc788\ub294 2\uac1c\uc758 \ud504\ub85c\uc2dc\uc838
\n(\ub2e4\ub978 \ud0c0\uc785\uc740 \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uc73c\ubbc0\ub85c number\uc778 \uacbd\uc6b0\ub294 to_char \uc774\uc6a9)<\/p>\n

DBMS_OBFUSCATION_TOOLKIT\uc744 \uc774\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 :
\n1) SYS \uc720\uc800\ub85c
\n@$ORACLE_HOME\/rdbms\/admin\/dbmsobtk.sql
\n@$ORACLE_HOME\/rdbms\/admin\/prvtobtk.plb
\n2) grant execute on dbms_obfuscation_toolkit to public;<\/p>\n

\uc81c \ud55c \uc0ac \ud56d
\n===========
\n1) DES(Data Encryption Standard) symmetric key algorithm \ubc29\uc2dd\uc744 \uc774\uc6a9.
\n\uc989 \uc554\ud638\ud654\ud560 \ub54c \uc774\uc6a9\ud55c key\ub97c \ubd84\uc2e4\ud588\uc744 \uacbd\uc6b0 \ub370\uc774\ud130\ub97c \ud574\ub3c5\ud560 \ubc29\ubc95\uc774 \uc5c6\ub2e4.<\/p>\n

2) Encrypt\ud558\ub824\ub294 data\uac00 8 bytes \ubc30\uc218(8,16,… bytes)\uc774\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

3) \ubbf8\uad6d\ubb34\ubd80\uc758 \uc554\ud638\ud654\uae30\uc220 \uc218\ucd9c\uc81c\ud55c\uc870\uce58\uc5d0 \uc758\ud574 56-bit key\ub97c \uc0ac\uc6a9. <\/p>\n

4) \ubbf8\uad6d\ubb34\ubd80\uc758 \uc554\ud638\ud654\uae30\uc220 \uc218\ucd9c\uc81c\ud55c\uc870\uce58\uc5d0 \uc758\ud574 \ud55c\ubc88 \uc554\ud638\ud654\ub41c \ub370\uc774\ud130\ub97c
\n\ub610\ub2e4\uc2dc \uc554\ud638\ud654\ud560 \uc218 \uc5c6\ub2e4.<\/p>\n

*) \ub9ce\uc740 \ud14c\uc774\ube14\uc744 Encrypt\/decrypt\ud560 \uacbd\uc6b0 CPU \uc0ac\uc6a9\ub7c9\uc744 \uc99d\uac00\uc2dc\ud0ac \uc218 \uc788\ub2e4.<\/p>\n

\uc0ac \uc6a9 \uc608
\n========
\n1) encrypt\/decrypt\uc5d0 \uc774\uc6a9\ud560 FUNCTION\uc744 \ub9cc\ub4e0\ub2e4.
\n(\ub9cc\uc57d input string\uc774 8 bytes \ubc30\uc218\uac00 \uc544\ub2c8\uba74 \ud328\ub529\uc744 \ud55c\ub2e4)<\/p>\n

– – – – – – – – – – – – – – – Code begins here – – – – – – – – – – – – – – –<\/p>\n

CREATE OR REPLACE PACKAGE CryptIT AS
\nFUNCTION encrypt( Str VARCHAR2,
\nhash VARCHAR2 ) RETURN VARCHAR2;
\nFUNCTION decrypt( xCrypt VARCHAR2,
\nhash VARCHAR2 ) RETURN VARCHAR2;
\nEND CryptIT;
\n\/<\/p>\n

CREATE OR REPLACE PACKAGE BODY CryptIT AS
\ncrypted_string VARCHAR2(2000);<\/p>\n

FUNCTION encrypt( Str VARCHAR2,
\nhash VARCHAR2 ) RETURN VARCHAR2 AS
\npieces_of_eight INTEGER := ((FLOOR(LENGTH(Str)\/8 + .9)) * 8);<\/p>\n

BEGIN<\/p>\n

dbms_obfuscation_toolkit.DESEncrypt(
\ninput_string => RPAD( Str, pieces_of_eight ),
\nkey_string => RPAD(hash,8,’#’),
\nencrypted_string => crypted_string );
\nRETURN crypted_string;
\nEND;<\/p>\n

FUNCTION decrypt( xCrypt VARCHAR2,
\nhash VARCHAR2 ) RETURN VARCHAR2 AS
\nBEGIN
\ndbms_obfuscation_toolkit.DESDecrypt(
\ninput_string => xCrypt,
\nkey_string => RPAD(hash,8,’#’),
\ndecrypted_string => crypted_string );
\nRETURN trim(crypted_string);
\nEND;
\nEND CryptIT;
\n\/<\/p>\n

– – – – – – – – – – – – – – – Code ends here – – – – – – – – – – – – – – – <\/p>\n

2) Encrypt\ud558\uc5ec \ub370\uc774\ud130 \uc785\ub825<\/p>\n

drop table encrypt_table;
\ncreate table encrypt_table( id number, passwd varchar(10) );<\/p>\n

insert into encrypt_table values( 1, CryptIT.encrypt(‘tiger’, ‘key_a’));
\ninsert into encrypt_table values( 2, CryptIT.encrypt(‘tiger’, ‘key_b’));<\/p>\n

3) Decrypt\ud558\uc5ec \ub370\uc774\ud130 \uc870\ud68c<\/p>\n

SQL> select id, passwd from encrypt_table where passwd = ‘tiger’;<\/p>\n

no rows selected<\/p>\n

-> \ubb3c\ub860 Decrypt\ud558\uc9c0 \uc54a\uc73c\uba74 \uc554\ud638\ud654\ub41c \ub370\uc774\ud130\uc640 \ube44\uad50\ub41c\ub2e4.<\/p>\n

\uc8fc\uc758) encrypt\ub41c \ub370\uc774\ud130\ub97c \ud654\uba74\uc5d0 \ucd9c\ub825\uba74, terminal emulator\uac00 \uc624\uc791\ub3d9\ud560 \uc218 \uc788\ub2e4.
\nterminal emulator \ud504\ub85c\uadf8\ub7a8 \uc885\ub8cc \ud6c4 \ub2e4\uc2dc \uc2dc\uc791.<\/p>\n

SQL> col passwd format a60
\nSQL> select id, dump(passwd) passwd from encrypt_table;<\/p>\n

ID PASSWD
\n———- ————————————————————-
\n1 Typ=1 Len=8: 246,27,80,184,227,225,245,31
\n2 Typ=1 Len=8: 175,231,213,125,85,223,46,133<\/p>\n

-> \uc800\uc7a5\uc7a5\uce58\uc5d0 Encrypt\ub41c \uac12\uc73c\ub85c \uc800\uc7a5\ub41c\ub2e4.<\/p>\n

select id, CryptIT.decrypt(passwd,’key_a’) passwd
\nfrom encrypt_table
\nwhere CryptIT.decrypt(passwd,’key_a’) = ‘tiger’;<\/p>\n

ID PASSWD
\n———- ————————————————————-
\n1 tiger<\/p>\n

select id, CryptIT.decrypt(passwd,’key_b’) passwd
\nfrom encrypt_table
\nwhere CryptIT.decrypt(passwd,’key_b’) = ‘tiger’;<\/p>\n

ID PASSWD
\n———- ————————————————————
\n2 tiger<\/p>\n

-> Encrypt\ud560 \ub54c \uc0ac\uc6a9\ud55c Key\ub85c\ub9cc Decrypt\ud560 \uc218 \uc788\ub2e4.<\/p>\n

\uc8fc\uc758) Table\uc5d0 \uc811\uadfc \uad8c\ud55c\uc774 \uc788\ub294 \ub2e4\ub978 \uc720\uc800\ub3c4 Key\uac12\uc744 \uc54c\uba74 Decrypt\ud560 \uc218 \uc788\ub2e4.<\/p>\n

4) \uad00\ub828 ORA number
\nORA error 28231 “Invalid input to Obfuscation toolkit”
\n– input data, key\uac12\uc774 NULL\uc77c \uacbd\uc6b0 \ubc1c\uc0dd<\/p>\n

ORA error 28232 “Invalid input size for Obfuscation toolkit”
\n– input data\uac00 8 bytes \ubc30\uc218\uac00 \uc544\ub2d0 \uacbd\uc6b0 \ubc1c\uc0dd
\nORA error 28233 “Double encryption not supported by DESEncrypt in Obfuscation toolkit”
\n– encrypt data\ub97c \ub2e4\uc2dc encrypt\uacbd\uc6b0 \ubc1c\uc0dd<\/p>\n

\uad00 \ub828 \uc790 \ub8cc
\n===========
\nOracle8i Supplied PL\/SQL Packages Reference Release 2 (8.1.6)<\/p>\n

\ubcc0\uacbd Procedure
\n=======================================================================================================
\nCREATE OR REPLACE PROCEDURE sawon_update IS
\nCURSOR CSR IS
\nSELECT
\n*
\nFROM
\nsawon
\nWHERE LOGINID IS NOT NULL;<\/p>\n

REC CSR%ROWTYPE; –\ub808\ucf54\ub4dc \uc800\uc7a5
\nV_TMP VARCHAR2(8);
\nBEGIN<\/p>\n

IF NOT CSR%ISOPEN THEN
\n OPEN CSR;
\nEND IF;<\/p>\n

LOOP
\n FETCH CSR INTO REC;
\n IF CSR%NOTFOUND THEN
\n EXIT;
\n END IF;
\n V_TMP:=REC.LOGINPWD;
\n UPDATE SAWON SET
\n LOGINPWD=CryptIT.encrypt(V_TMP,’key_0610′) WHERE SABUN=REC.SABUN;
\nEND LOOP;<\/p>\n

IF CSR%ISOPEN THEN
\n CLOSE CSR;
\nEND IF;
\n\/<\/p>\n

SQL> select loginid,loginpwd,CRYPTIT.DECRYPT(LOGINPWD,’key_0610′) from sawon where CRYPTIT.DECRYPT(LOGINPWD,’key_100610′)=’my_pwd’<\/p>\n

ORA-28231: \ub370\uc774\ud130\uac00 obfuscation \ud234\ud0b7\uc73c\ub85c \uc804\ub2ec\ub418\uc9c0 \uc54a\uc558\uc2b5\ub2c8\ub2e4.
\nORA-06512: “SYS.DBMS_OBFUSCATION_TOOLKIT_FFI”, \uc904 0\uc5d0\uc11c
\nORA-06512: “SYS.DBMS_OBFUSCATION_TOOLKIT”, \uc904 75\uc5d0\uc11c
\nORA-06512: “INSA.CRYPTIT”, \uc904 39\uc5d0\uc11c
\nORA-06512: \uc904 1\uc5d0\uc11c<\/p>\n

\uc6d0\uc778
\nnull \uac12\uc774 \uc788\uc73c\uba74 \uc548\ub428.<\/p>\n

\uc870\uce58
\nSQL> select loginid,loginpwd,CRYPTIT.DECRYPT(LOGINPWD,’key_0610′) from sawon where CRYPTIT.DECRYPT(LOGINPWD,’key_100610′)=’my_pwd’ and loginid is not null;<\/p>\n

=> loginid is not null; \ucd94\uac00\ub85c null \uceec\ub7fc \uc870\ud68c \uc5c6\uc774 \ucc98\ub9ac\ud558\uba74\ub428.<\/p>\n

\ucd9c\ucc98 : \uceec\ub7fc\uc5d0 \uc554\ud638 \uc815\uc758
\nhttp:\/\/iotn.co.kr\/power\/zboard.php?tab_num=1&id=9_1_bbs&no=562<\/p>\n","protected":false},"excerpt":{"rendered":"

\uc554\ud638\ud654 \ud560\ub54c \ud574\ub2f9 \ubcc0\uc218\uc5d0 null\uc774 \ub4e4\uc5b4\uac00\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc5d0\ub7ec\ub97c \ub9cc\ub098\uac8c \ub41c\ub2e4. SQL> select loginid,loginpwd,CRYPTIT.DECRYPT(LOGINPWD,’key_0610′) from sawon where CRYPTIT.DECRYPT(LOGINPWD,’key_100610′)=’my_pwd’ ORA-28231: \ub370\uc774\ud130\uac00 obfuscation \ud234\ud0b7\uc73c\ub85c \uc804\ub2ec\ub418\uc9c0 \uc54a\uc558\uc2b5\ub2c8\ub2e4. ORA-06512: “SYS.DBMS_OBFUSCATION_TOOLKIT_FFI”, \uc904 0\uc5d0\uc11c ORA-06512: “SYS.DBMS_OBFUSCATION_TOOLKIT”,… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts\/485"}],"collection":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=485"}],"version-history":[{"count":0,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts\/485\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=485"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=485"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=485"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}