{"id":294,"date":"2011-12-14T02:15:24","date_gmt":"2011-12-14T02:15:24","guid":{"rendered":"http:\/\/brain.dailyon.net\/?p=294"},"modified":"2011-12-14T02:15:24","modified_gmt":"2011-12-14T02:15:24","slug":"oracle-%ec%95%94%ed%98%b8%ed%99%94-%ed%8c%a8%ed%82%a4%ec%a7%80-%ec%84%a4%ec%b9%98","status":"publish","type":"post","link":"https:\/\/blog.goorigo.com\/?p=294","title":{"rendered":"ORACLE : \uc554\ud638\ud654 \ud328\ud0a4\uc9c0 \uc124\uce58"},"content":{"rendered":"

http:\/\/aozjffl.tistory.com\/158<\/a><\/p>\n

\uc624\ub77c\ud074 \uc554\ud638\ud654  \ud328\ud0a4\uc9c0 \uc124\uce58\ud558\uae30<\/p>\n

\uc124\uce58\uc5d0 \uc2e4\ud328\ud558\uba74 \uc77c\ub2e8 \ud328\ud0a4\uc9c0\ub97c \uae54\ub054\ud558\uac8c \uc9c0\uc6b0\uc790<\/p>\n

\uc0ad\uc81c\ubc29\ubc95 : drop package \ud328\ud0a4\uc9c0\uba85;<\/p>\n

\uac80\uc0c9 \ubc29\ubc95 : select * from user_source where name = ‘PROC1’;<\/p>\n

 <\/p>\n

DBMS_OBFUSCATION_TOOLKIT \uc758 \uc554\ud638\ud654\/\ubcf5\ud638\ud654
\n<\/span><\/p>\n

1. \uc124 \uba85<\/p>\n

\u261e Bulletin no : 12036 \ucc38\uace0<\/p>\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 
\n\ud5a5\uc0c1\ub41c \uae30\ub2a5(DES Encryption)\uc744 \uc81c\uacf5 \ud569\ub2c8\ub2e4
\n 
\n 
\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\uc90d\ub2c8\ub2e4.
\n 
\n 
\n 
\n\u25c8 DBMS_OBFUSCATION_TOOLKIT<\/p>\n

\uc554\ud638\ud654 \uae30\ub2a5\uc744 \uc774\uc6a9\ud558\ub824\uba74 DBMS_OBFUSCATION_TOOLKIT\uc744 \uc774\uc6a9\ud574\uc57c \ud569\ub2c8\ub2e4.
\n 
\n 
\n\uc774 \ud328\ud0a4\uc9c0\ub294 4\uac1c\uc758 \ud504\ub85c\uc2dc\uc838\ub85c \uc774\ub8e8\uc5b4\uc838 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

– VARCHAR2 \ud0c0\uc785\uc744 Encrypt\/Decrypt\ud560 \uc218 \uc788\ub294 2\uac1c\uc758 \ud504\ub85c\uc2dc\uc838<\/p>\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)
\n 
\n <\/p>\n

DBMS_OBFUSCATION_TOOLKIT\uc744 \uc774\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 :<\/p>\n

1) SYS \uc720\uc800\ub85c \uc544\ub798\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc2e4\ud589 \uc2dc\ud0b5\ub2c8\ub2e4.<\/p>\n

   @$ORACLE_HOME\/rdbms\/admin\/dbmsobtk.sql
\n   @$ORACLE_HOME\/rdbms\/admin\/prvtobtk.plb
\n   
\n2) \uad8c\ud55c\uc744 \ubd80\uc5ec \ud569\ub2c8\ub2e4.<\/p>\n

   SQL>GRANT execute ON dbms_obfuscation_toolkit TO public;<\/p>\n

 <\/p>\n

2. \ud328\ud0a4\uc9c0 \uc2e4\ud589\ud558\uae30<\/p>\n

 <\/p>\n

–> \ud328\ud0a4\uc9c0 \uc120\uc5b8\ubd80 \uc0dd\uc131<\/p>\n

CREATE OR REPLACE PACKAGE CryptIT AS 
\n   FUNCTION encrypt( Str VARCHAR2,  
\n                     hash VARCHAR2 ) RETURN VARCHAR2;<\/p>\n

   FUNCTION decrypt( xCrypt VARCHAR2,
\n                     hash VARCHAR2 ) RETURN VARCHAR2;<\/p>\n

   FUNCTION md5( Str VARCHAR2) RETURN VARCHAR2;<\/p>\n

END CryptIT;
\n\/
\n 
\n 
\n 
\n–> \ud328\ud0a4\uc9c0 \ubcf8\uccb4 \uc0dd\uc131<\/p>\n

CREATE OR REPLACE PACKAGE BODY CryptIT AS 
\n   crypted_string VARCHAR2(2000);
\n 
\n   FUNCTION encrypt( Str VARCHAR2,  
\n                     hash VARCHAR2 ) RETURN VARCHAR2 AS
\n   pieces_of_eight INTEGER := ((FLOOR(LENGTH(Str)\/8 + .9)) * 8);
\n 
\n   BEGIN
\n 
\n      dbms_obfuscation_toolkit.DESEncrypt(
\n               input_string     => RPAD( Str, pieces_of_eight ),
\n               key_string       => RPAD(hash,8,’#’), 
\n               encrypted_string => crypted_string );
\n      RETURN crypted_string;
\n   END;
\n 
\n   FUNCTION decrypt( xCrypt VARCHAR2,
\n                     hash VARCHAR2 ) RETURN VARCHAR2 AS 
\n   BEGIN
\n      dbms_obfuscation_toolkit.DESDecrypt(
\n               input_string     => xCrypt, 
\n               key_string       => RPAD(hash,8,’#’), 
\n               decrypted_string => crypted_string );
\n      RETURN trim(crypted_string);
\n   END;
\n 
\n   FUNCTION md5( Str VARCHAR2) RETURN VARCHAR2 AS<\/p>\n

   BEGIN
\n      dbms_obfuscation_toolkit.MD5(
\n               input_string => :input_string,
\n               checksum_string => :checksum_string);
\n      RETURN trim(crypted_string);
\n   END;
\nEND CryptIT;
\n\/<\/p>\n

 <\/p>\n

\n3. \uc2e4\ud589 \uc608\uc81c<\/p>\n

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

— \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14\uc744 \uc0dd\uc131 \ud569\ub2c8\ub2e4.<\/p>\n

SQL>create table encrypt_table( id number, passwd varchar(20) );<\/p>\n

\n 
\n— \ud14c\uc2a4\ud2b8 \ub370\uc774\ud2b8\ub7f4 \uc785\ub825 \ud569\ub2c8\ub2e4. 
\n— CryptIT.encrypt(\ube44\ubc00\ubc88\ud638, \ud0a4\uac12)<\/p>\n

SQL>INSERT INTO encrypt_table VALUES( 1, CryptIT.encrypt(‘1234’, ‘storm’));
\n1 \uac1c\uc758 \ud589\uc774 \ub9cc\ub4e4\uc5b4\uc84c\uc2b5\ub2c8\ub2e4.<\/p>\n

 
\nSQL>INSERT INTO encrypt_table VALUES( 2, CryptIT.encrypt(‘5678’, ‘oramaster’));
\n1 \uac1c\uc758 \ud589\uc774 \ub9cc\ub4e4\uc5b4\uc84c\uc2b5\ub2c8\ub2e4.<\/p>\n

 
\n 
\n2) Decrypt\ud558\uc5ec \ub370\uc774\ud130 \uc870\ud68c
\n 
\n–> Decrypt\ud558\uc9c0 \uc54a\uc73c\uba74 \uc554\ud638\ud654\ub41c \ub370\uc774\ud130\uc640 \ube44\uad50\ub418\uc11c \uacb0\uacfc\uac12\uc774 \ucd9c\ub825\ub418\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.
\nSQL> select id, passwd from encrypt_table where passwd = ‘1234’;
\n 
\n\uc120\ud0dd\ub41c \ub808\ucf54\ub4dc\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.
\n 
\n 
\n–> \uc800\uc7a5\uc7a5\uce58\uc5d0 Encrypt\ub41c \uac12\uc73c\ub85c \uc800\uc7a5 \ub429\ub2c8\ub2e4.<\/p>\n

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

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

\n–> Encrypt\ud560 \ub54c \uc0ac\uc6a9\ud55c Key\ub85c\ub9cc Decrypt\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.
\n 
\nSQL>SELECT id, CryptIT.decrypt(passwd,’storm’) passwd
\n       FROM encrypt_table 
\n       WHERE CryptIT.decrypt(passwd,’storm’) = ‘1234’;
\n 
\n        ID PASSWD
\n———- ———–
\n         1 1234
\n 
\n 
\nSQL>SELECT id, CryptIT.decrypt(passwd,’oramaster’) passwd
\n    FROM encrypt_table 
\n    WHERE CryptIT.decrypt(passwd,’oramaster’) = ‘5678’;
\n 
\n        ID PASSWD
\n———- ———–
\n         2 5678
\n 
\n 
\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\uc2b5\ub2c8\ub2e4.
\n 
\n 
\n 
\n4) \uad00\ub828 ORA error number
\n 
\nORA error 28231 “Invalid input to Obfuscation toolkit”
\n– input data, key\uac12\uc774 NULL\uc77c \uacbd\uc6b0 \ubc1c\uc0dd
\n 
\nORA error 28232 “Invalid input size for Obfuscation toolkit”
\n– input data\uac00 8 bytes \ubc30\uc218\uac00 \uc544\ub2d0 \uacbd\uc6b0 \ubc1c\uc0dd
\n 
\nORA error 28233 “Double encryption not supported by DESEncrypt in Obfuscation toolkit”
\n– encrypt data\ub97c \ub2e4\uc2dc encrypt\uacbd\uc6b0 \ubc1c\uc0dd
\n <\/p>\n

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

 <\/p>\n

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

http:\/\/aozjffl.tistory.com\/158 \uc624\ub77c\ud074 \uc554\ud638\ud654  \ud328\ud0a4\uc9c0 \uc124\uce58\ud558\uae30 \uc124\uce58\uc5d0 \uc2e4\ud328\ud558\uba74 \uc77c\ub2e8 \ud328\ud0a4\uc9c0\ub97c \uae54\ub054\ud558\uac8c \uc9c0\uc6b0\uc790 \uc0ad\uc81c\ubc29\ubc95 : drop package \ud328\ud0a4\uc9c0\uba85; \uac80\uc0c9 \ubc29\ubc95 : select * from user_source where name = ‘PROC1’;   DBMS_OBFUSCATION_TOOLKIT \uc758 \uc554\ud638\ud654\/\ubcf5\ud638\ud654 1.… <\/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\/294"}],"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=294"}],"version-history":[{"count":0,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts\/294\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}