Oracle 8i¿¡¼ µ¥ÀÌÅÍ ¾ÏÈ£È ±â´É (±Û ¿À¶óŬ) | ÀÛ¼ºÀÏ : 2003/11/30 21:22 |
Á¶È¸¼ö : 8663 |
Ãâó : ¿À¶óŬ Technical Bulletins, http://211.106.111.2:8880/bulletin/list.jsp?seq=12036 ------------------------------------------------------------------------ No. 12036 µ¥ÀÌÅÍ ¾ÏÈ£È ±â´É ¼Ò°³(8.1.6 new feature) ========================================= °³ ¿ä ========= Oracle 8i Release2(8.1.6)¿¡¼´Â µ¥ÀÌÅ͸¦ ¾ÏÈ£ÈÇÏ¿© ÀúÀåÇÒ ¼ö ÀÖ´Â Çâ»óµÈ ±â´É(DES Encryption)À» Á¦°øÇÑ´Ù (Oracle 8i Release3(8.1.7)¿¡¼´Â Triple DES Encryption) Áï ½Å¿ëÄ«µå¹øÈ£, Æнº¿öµå µî º¸¾ÈÀÌ ÇÊ¿äÇÑ µ¥ÀÌÅ͸¦ ¾ÏÈ£ÈµÈ ÇüÅ·ΠÀúÀåÇÏ¿© ±âÁ¸ÀÇ 3rd Party ToolÀ̳ª, Application LogicÀ¸·Î ±¸ÇöÇÏ´ø ¾ÏÈ£È Á¤Ã¥À» µ¥ÀÌÅͺ£À̽º Â÷¿ø¿¡¼ ±¸ÇöÇÒ ¼ö ÀÖµµ·Ï ÇØÁØ´Ù. DBMS_OBFUSCATION_TOOLKIT ======================== ¾ÏÈ£È ±â´ÉÀ» ÀÌ¿ëÇÏ·Á¸é DBMS_OBFUSCATION_TOOLKITÀ» ÀÌ¿ëÇØ¾ß ÇÑ´Ù. ÀÌ ÆÐÅ°Áö´Â 4°³ÀÇ ÇÁ·Î½ÃÁ®·Î ÀÌ·ç¾îÁ® ÀÖ´Ù. - VARCHAR2 ŸÀÔÀ» Encrypt/DecryptÇÒ ¼ö ÀÖ´Â 2°³ÀÇ ÇÁ·Î½ÃÁ® - RAW ŸÀÔÀ» Encrypt/DecryptÇÒ ¼ö ÀÖ´Â 2°³ÀÇ ÇÁ·Î½ÃÁ® (´Ù¸¥ ŸÀÔÀº Áö¿øÇÏÁö ¾ÊÀ¸¹Ç·Î numberÀÎ °æ¿ì´Â to_char ÀÌ¿ë) DBMS_OBFUSCATION_TOOLKITÀ» ÀÌ¿ëÇϱâ À§Çؼ´Â : 1) SYS À¯Àú·Î @$ORACLE_HOME/rdbms/admin/dbmsobtk.sql @$ORACLE_HOME/rdbms/admin/prvtobtk.plb 2) grant execute on dbms_obfuscation_toolkit to public; Á¦ ÇÑ »ç Ç× =========== 1) DES(Data Encryption Standard) symmetric key algorithm ¹æ½ÄÀ» ÀÌ¿ë. Áï ¾ÏÈ£ÈÇÒ ¶§ ÀÌ¿ëÇÑ key¸¦ ºÐ½ÇÇßÀ» °æ¿ì µ¥ÀÌÅ͸¦ Çص¶ÇÒ ¹æ¹ýÀÌ ¾ø´Ù. 2) EncryptÇÏ·Á´Â data°¡ 8 bytes ¹è¼ö(8,16,... bytes)À̾î¾ß ÇÑ´Ù. 3) ¹Ì±¹¹«ºÎÀÇ ¾Ïȣȱâ¼ú ¼öÃâÁ¦ÇÑÁ¶Ä¡¿¡ ÀÇÇØ 56-bit key¸¦ »ç¿ë. 4) ¹Ì±¹¹«ºÎÀÇ ¾Ïȣȱâ¼ú ¼öÃâÁ¦ÇÑÁ¶Ä¡¿¡ ÀÇÇØ Çѹø ¾ÏÈ£ÈµÈ µ¥ÀÌÅ͸¦ ¶Ç´Ù½Ã ¾ÏÈ£ÈÇÒ ¼ö ¾ø´Ù. *) ¸¹Àº Å×À̺íÀ» Encrypt/decryptÇÒ °æ¿ì CPU »ç¿ë·®À» Áõ°¡½Ãų ¼ö ÀÖ´Ù. *) ¾Æ·¡ÀÇ ¿¹Á¦´Â UTF8À» »ç¿ëÇÒ °æ¿ì ÇÑ±Û µ¥ÀÌÅ͸¦ ¾ÏÈ£ÈÇÒ ¼ö ¾ø´Ù. (RPADÀÇ Á¦¾àÀ¸·Î) »ç ¿ë ¿¹ ======== 1) encrypt/decrypt¿¡ ÀÌ¿ëÇÒ FUNCTIONÀ» ¸¸µç´Ù. (¸¸¾à input stringÀÌ 8 byte ¹è¼ö°¡ ¾Æ´Ï¸é ÆеùÀ» ÇÑ´Ù) *) 8.1.6¿¡¼´Â key°ªÀÌ 8 byte ÀÌ»óÀ̾î¾ß ÇÔ(8.1.7 ÀÌÈÄ¿¡´Â Á¦ÇѾøÀ½) - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - REM ------------------------------------------------------------------------ REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ CREATE OR REPLACE PACKAGE CryptIT AS FUNCTION encrypt( Str VARCHAR2, hash VARCHAR2 ) RETURN VARCHAR2; FUNCTION decrypt( xCrypt VARCHAR2, hash VARCHAR2 ) RETURN VARCHAR2; END CryptIT; / CREATE OR REPLACE PACKAGE BODY CryptIT AS crypted_string VARCHAR2(2000); FUNCTION encrypt( Str VARCHAR2, hash VARCHAR2 ) RETURN VARCHAR2 AS pieces_of_eight INTEGER := ((FLOOR(LENGTH(Str)/8 + .9)) * 8); BEGIN dbms_obfuscation_toolkit.DESEncrypt( input_string => RPAD( Str, pieces_of_eight ), key_string => RPAD(hash,8,'#'), encrypted_string => crypted_string ); RETURN crypted_string; END; FUNCTION decrypt( xCrypt VARCHAR2, hash VARCHAR2 ) RETURN VARCHAR2 AS BEGIN dbms_obfuscation_toolkit.DESDecrypt( input_string => xCrypt, key_string => RPAD(hash,8,'#'), decrypted_string => crypted_string ); RETURN trim(crypted_string); END; END CryptIT; / - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - 2) EncryptÇÏ¿© µ¥ÀÌÅÍ ÀÔ·Â drop table encrypt_table; create table encrypt_table( id number, passwd varchar(10) ); insert into encrypt_table values( 1, CryptIT.encrypt('tiger', 'key_a')); insert into encrypt_table values( 2, CryptIT.encrypt('tiger', 'key_b')); 3) DecryptÇÏ¿© µ¥ÀÌÅÍ Á¶È¸ SQL> select id, passwd from encrypt_table where passwd = 'tiger'; no rows selected -> ¹°·Ð DecryptÇÏÁö ¾ÊÀ¸¸é ¾ÏÈ£ÈµÈ µ¥ÀÌÅÍ¿Í ºñ±³µÈ´Ù. ÁÖÀÇ) encryptµÈ µ¥ÀÌÅ͸¦ ȸ鿡 Ãâ·ÂÇϸé, terminal emulator°¡ ¿ÀÀÛµ¿ÇÒ ¼ö ÀÖ´Ù. ±×·² °æ¿ì, terminal emulator ÇÁ·Î±×·¥ Á¾·á ÈÄ ´Ù½Ã ½ÃÀÛ. SQL> col passwd format a60 SQL> select id, dump(passwd) passwd from encrypt_table; ID PASSWD ---------- ------------------------------------------------------------- 1 Typ=1 Len=8: 246,27,80,184,227,225,245,31 2 Typ=1 Len=8: 175,231,213,125,85,223,46,133 -> ÀúÀåÀåÄ¡¿¡ EncryptµÈ °ªÀ¸·Î ÀúÀåµÈ´Ù. select id, CryptIT.decrypt(passwd,'key_a') passwd from encrypt_table where CryptIT.decrypt(passwd,'key_a') = 'tiger'; ID PASSWD ---------- ------------------------------------------------------------- 1 tiger select id, CryptIT.decrypt(passwd,'key_b') passwd from encrypt_table where CryptIT.decrypt(passwd,'key_b') = 'tiger'; ID PASSWD ---------- ------------------------------------------------------------ 2 tiger -> EncryptÇÒ ¶§ »ç¿ëÇÑ Key·Î¸¸ DecryptÇÒ ¼ö ÀÖ´Ù. ÁÖÀÇ) Table¿¡ Á¢±Ù ±ÇÇÑÀÌ ÀÖ´Â ´Ù¸¥ À¯Àúµµ Key°ªÀ» ¾Ë¸é DecryptÇÒ ¼ö ÀÖ´Ù. 4) °ü·Ã ORA number ORA error 28231 "Invalid input to Obfuscation toolkit" - input data, key°ªÀÌ NULLÀÏ °æ¿ì ¹ß»ý ORA error 28232 "Invalid input size for Obfuscation toolkit" - input data°¡ 8 bytes ¹è¼ö°¡ ¾Æ´Ò °æ¿ì ¹ß»ý ORA error 28233 "Double encryption not supported by DESEncrypt in Obfuscation toolkit" - encrypt data¸¦ ´Ù½Ã encrypt°æ¿ì ¹ß»ý °ü ·Ã ÀÚ ·á =========== Oracle8i Supplied PL/SQL Packages Reference Release 2 (8.1.6) |
Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ / URL : http://coffeenix.net/board_view.php?bd_code=139 |