|
Ãâó : ¿À¶óŬ 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)
|