Tina Admin

Oracle 데이터암호화 Crypto_Package 유니원아이앤씨 DB 사업부이대혁 2014 년 12 월 19 일 문서정보 프로젝트명 ORACLE 암호화 서브시스템명 버전 1.0 문서명 ORACLE 암호화 작성일 2014-12-19 작성자 DB사업부이대혁사원 최종수정일 2014-12-19 문서번호 UNIONE-201402060432-LDH.

재개정이력 일자내용수정인버전 문서배포이력 발신자수신자배포목적일자비고 2/31

Table of Contents 1 테스트환경... 4 2 ORACLE 암호화... 7 3 DBMS_CRYPTO... 8 3.1 DBMS_CRYPTO 설정... 8 3.1.1 Package 생성... 8 3.1.2 권한부여... 9 3.1.3 Function 생성... 9 3.1.4 샘플데이터생성... 11 3.2 DBMS_CRYPTO 를이용한암호화... 13 3.3 Decryt Function 을사용한복호화방법... 14 3.4 Crypto insert 작업... 15 4 FUNCTION, PACKAGE... 18 5 WRAP Utility 를이용한 PL/SQL Source 암호화... 20 5.1 Function 암호화후 Function 조회... 22 6 WRAP Utility 를이용한 Package 암호화... 25 6.1 현재 Crypt Package 내용검색... 25 6.2 Package Wrap... 25 6.3 Wrap 을이용한 Package 암호화... 27 6.4 Wrap 되어진 Package 내용확인... 29 7 암호화컬럼의 Index 활용... 30 3/31

1 테스트환경 Server : VM-ware server # dmidecode grep Product Product Name: VMware Virtual Platform Product Name: 440BX Desktop Reference Platform O/S : RHEL 5.2 64bit # cat /etc/redhat-release Enterprise Linux Enterprise Linux Server release 5.2 (Carthage) CPU 정보 # cat /proc/cpuinfo more processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Core(TM) i3-3220 CPU @ 3.30GHz stepping : 9 4/31

cpu MHz : 3292.410 cache size fpu fpu_exception : 3072 KB : yes : yes cpuid level : 13 wp flags : yes : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc up pni cx16 popcnt lahf_lm bogomips : 6591.09 clflush size : 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual power management: Memory 정보 # cat /proc/meminfo more MemTotal: MemFree: Buffers: Cached: 1503640 kb 30732 kb 165888 kb 925140 kb 5/31

SwapCached: Active: Inactive: HighTotal: HighFree: LowTotal: LowFree: SwapTotal: SwapFree: Dirty: Writeback: AnonPages: Mapped: Slab: PageTables: NFS_Unstable: Bounce: CommitLimit: 0 kb 712940 kb 624848 kb 0 kb 0 kb 1503640 kb 30732 kb 4192956 kb 4192956 kb 4 kb 0 kb 246752 kb 262804 kb 73504 kb 37204 kb 0 kb 0 kb 4944776 kb Committed_AS: 2143132 kb VmallocTotal: 34359738367 kb 6/31

VmallocUsed: 264988 kb VmallocChunk: 34359472887 kb HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 Hugepagesize: 2048 kb Target Oracle Vesion : Oracle 10.2.0.5 EE 64bit Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production 2 ORACLE 암호화 테이블의컬럼을암호화하기위한상용소프트웨어는다양하지만비용이발생할수밖에없습니다. Oracle 에서제공하는 Package를통해테이블의컬럼을암호화할수있는기능을소개합니다. Oracle 9i 까지는 DBMS_OBFUSCATION_TOOLKIT 으로, 10g 부터는추가로 DBMS_CRYPTO 를이용하여테이블의컬럼을암호화할수있습니다. 7/31

3 DBMS_CRYPTO 3.1 DBMS_CRYPTO 설정 3.1.1 Package 생성 SQL> @?/rdbms/admin/dbmsobtk.sql Library created. No errors. Library created. No errors. Package created. Synonym created. Package created. No errors. Synonym created. Grant succeeded. No errors. Package created. No errors. Synonym created. SQL> @?/rdbms/admin/prvtobtk.plb Package created. Package body created. 8/31

Package body created. Package created. No errors. Package body created. No errors. Package body created. No errors. Package body created. No errors. 3.1.2 권한부여 SQL> GRANT execute ON sys.dbms_crypto TO public; SQL> GRANT execute ON sys.dbms_crypto TO test; 3.1.3 Function 생성 National Character Set 확인 SQL> select * from nls_database_parameter where parameter= NLS_NCHAR_CHARACTERSET ; PARAMETER VALUE ------------------------------- --------------------------------------------------------------------------------------- NLS_NCHAR_CHARACTERSET AL16UTF16 Encrypt Function 생성 SQL> create or replace function encrypt(v_string in varchar2) return varchar2 is 9/31

2 encrypted_raw RAW (2000); 3 encryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_DES + 4 SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5; 5 v_key raw(128) := utl_i18n.string_to_raw('unione', 'AL16UTF16' ); 6 begin 7 encrypted_raw := DBMS_CRYPTO.ENCRYPT 8 ( 9 src => UTL_I18N.STRING_TO_RAW (v_string,'al16utf16'), 10 typ => encryption_type, 11 key => v_key 12 ); 13 return RAWTOHEX(encrypted_raw); 14 end encrypt; 15 / Function created. Decrypt Function 생성 SQL> create or replace function decrypt(v_str in varchar2) return varchar2 is 2 decrypted_raw raw(2000); 3 encryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_DES + 4 SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5; 10/31

5 v_key raw(128) := utl_i18n.string_to_raw( 'unione', 'AL16UTF16' ); 6 begin 7 decrypted_raw := DBMS_CRYPTO.Decrypt 8 ( 9 src => HEXTORAW(v_str), 10 typ => encryption_type, 11 key => v_key 12 ); 13 return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL16UTF16'); 14 end decrypt; 15 / Function created. 3.1.4 샘플데이터생성 SQL> create table crypto_test( 번호 number, 성명 varchar2(10), 주민등록번호 varchar2(14)) ; Table created. SQL> desc crypto_test 11/31

Name Null Type ----------------------------------------- -------- ---------------------------- 번호 성명 주민등록번호 NUMBER VARCHAR2(10) VARCHAR2(14) SQL> INSERT into crypto_test VALUES(1,' 이대혁 ','111111-1111111'); 1 row created. SQL> INSERT into crypto_test VALUES(2,' 김성한 ','222222-2222222'); 1 row created. SQL> INSERT into crypto_test VALUES(3,' 권순기 ','333333-3333333'); 1 row created. SQL> INSERT into crypto_test VALUES(4,' 유명수 ','444444-4444444'); 1 row created. SQL> INSERT into crypto_test VALUES(5,' 심현승 ','555555-5555555'); 12/31

1 row created. SQL> select * from crypto_test; 번호성명 주민등록번호 ---------- ---------- -------------- 1 이대혁 111111-1111111 2 김성한 222222-2222222 3 권순기 333333-3333333 4 유명수 444444-4444444 5 심현승 555555-5555555 3.2 DBMS_CRYPTO 를이용한암호화 SQL> update crypto_test set 주민등록번호 =encrypt( 주민등록번호 ); update crypto_test set 주민등록번호 =encrypt( 주민등록번호 ) * ERROR at line 1: ORA-12899: value too large for column "SYS"."CRYPTO_TEST"." 주민등록번호 " (actual: 64, maximum: 14) 컬럼사이즈부족으로 Error 발생 SQL> ALTER TABLE crypto_test MODIFY 주민등록번호 VARCHAR2(100); 13/31

Table altered. SQL> update crypto_test set 주민등록번호 =encrypt( 주민등록번호 ); 5 rows updated. SQL> select * from crypto_teset; 번호성명 주민등록번호 ---------- ---------- ---------------------------------------------------------------------------------------------------- 1 이대혁 632D19693C9AA9BC86DC844C9BF01041BFBB3C005F10DF0C6F13F1CBE74D34BA 2 김성한 61F0D8EB431962C1D00744690FBD3177D83B6F42DD1B6CBE1624EB93EB35E9F0 3 권순기 998E23E7B9F32F70DE115CDB8126ED1E02DE4DA995B880AD9FF451F78422E527 4 유명수 0FAA3ED3F07E6A00A0EB0F270E883F43D88F87812E1085DDD16864C6B79C9BB3 5 심현승 B7B012A42729ADB0194C424197849C2EEF79B6A64D487C9969527680645C608C 5 rows selected. 3.3 Decryt Function 을사용한복호화방법 SQL> select 번호, 성명, Decrypt( 주민등록번호 ) from crypto_test ; 14/31

번호성명 DECRYPT( 주민등록번호 ) ---------- ---------- -------------------------------------------------- 1 이대혁 111111-1111111 2 김성한 222222-2222222 3 권순기 333333-3333333 4 유명수 444444-4444444 5 심현승 555555-5555555 3.4 Crypto insert 작업 SQL> INSERT into crypto_test VALUES(6,' 임동규 ',ENCRYPT('555555-5555555')); 1 row created. SQL> select * from crypto_test; 번호성명 주민등록번호 ---------- ---------- ---------------------------------------------------------------------------------------------------- 1 이대혁 632D19693C9AA9BC86DC844C9BF01041BFBB3C005F10DF0C6F13F1CBE74D34BA 2 김성한 61F0D8EB431962C1D00744690FBD3177D83B6F42DD1B6CBE1624EB93EB35E9F0 3 권순기 998E23E7B9F32F70DE115CDB8126ED1E02DE4DA995B880AD9FF451F78422E527 15/31

4 유명수 0FAA3ED3F07E6A00A0EB0F270E883F43D88F87812E1085DDD16864C6B79C9BB3 5 심현승 B7B012A42729ADB0194C424197849C2EEF79B6A64D487C9969527680645C608C 6 임동규 B7B012A42729ADB0194C424197849C2EEF79B6A64D487C9969527680645C608C 6 번과 5 번이같은주민번호 (555555-5555555) 를입력하게되면안호화된값이동일하다는것을알수있습니다. dbms_crypto 는같은값의데이터는암호화된값도같게됩니다. 주민번호는같을수없으므로문제가없지만다른컬럼암호화시 주의를해야하는사항이됩니다. SQL> update crypto_test set 주민등록번호 = ENCRYPT( 주민등록번호 ) 2 where 성명 =' 임동규 '; 1 row updated. SQL> / 번호성명 주민등록번호 ---------- -------------------- -------------------------------------------------------------------------------- 1 이대혁 632D19693C9AA9BC86DC844C9BF01041BFBB3C005F10DF0C6F13F1CBE74D34BA 2 김성한 61F0D8EB431962C1D00744690FBD3177D83B6F42DD1B6CBE1624EB93EB35E9F0 3 권순기 998E23E7B9F32F70DE115CDB8126ED1E02DE4DA995B880AD9FF451F78422E527 4 유명수 0FAA3ED3F07E6A00A0EB0F270E883F43D88F87812E1085DDD16864C6B79C9BB3 16/31

5 심현승 B7B012A42729ADB0194C424197849C2EEF79B6A64D487C9969527680645C608C 6 임동규 4E3B23DE9282EE57CA2EC4B866EAE3BFA87EA2EE5653F78C6A8B804B58C2C0906204D76115FD0047 33E29EAF61ECEAF4826FCD55E81261DD076DC5508D94D0C1B9B7A3AF5CE46327C19382BC7AE9D2F3 A9D5E51AD1B20833A17594F6BCD72A006BE94C37A3C7AB94EE20468B81122286A1316F96F6D0E74E 53C230B9CAA4FBFFCBEC45870A99DF51 6 rows selected. 암호화된컬럼을또다시암호화를할경우, 컬럼에필요한값이증가하게되고, 2중암호화가됩니다. 2중암호화된컬럼을복호화를하면처음암호화했을때의값으로돌아오게됩니다. 암호화한테이타에다시암호화를수행하면테이타가 2중암호화가되며, 2중암호화가되면사이즈가더늘어나게됩니다. 2중암호화를복호화하기위해서는 2중복호화를수행해야합니다. 암호화방식이기존의데이터에암호화데이타가추가로삽입이되므로같은값의데이터를암호화하게되면암호화된값은같아집니다. SQL> update crypto_test set 주민등록번호 = DECRYPT( 주민등록번호 ) 2 where 성명 =' 임동규 '; 1 row updated. SQL> select * from crypto_test ; 번호성명 주민등록번호 17/31

---------- -------------------- -------------------------------------------------------------------------------- 1 이대혁 632D19693C9AA9BC86DC844C9BF01041BFBB3C005F10DF0C6F13F1CBE74D34BA 2 김성한 61F0D8EB431962C1D00744690FBD3177D83B6F42DD1B6CBE1624EB93EB35E9F0 3 권순기 998E23E7B9F32F70DE115CDB8126ED1E02DE4DA995B880AD9FF451F78422E527 4 유명수 0FAA3ED3F07E6A00A0EB0F270E883F43D88F87812E1085DDD16864C6B79C9BB3 5 심현승 B7B012A42729ADB0194C424197849C2EEF79B6A64D487C9969527680645C608C 6 임동규 B7B012A42729ADB0194C424197849C2EEF79B6A64D487C9969527680645C608C 6 rows selected. 4 FUNCTION, PACKAGE 암호화 / 복호화는 function의 name만으로작업이가능하며, function의 name을알고있는사람은누구가암호화된값을복호화해서조회가가능하게됩니다. 따라서 function을암호화하는방안을시도해보도록하겠습니다. SQL> select * from user_source where name='encrypt' or name='decrypt'; NAME TYPE LINE TEXT ------------------------------ ------------ ---------- ---------------------------------------------------------------------- DECRYPT FUNCTION 1 function decrypt(v_str in varchar2) return varchar2 is DECRYPT FUNCTION 2 decrypted_raw raw(2000); DECRYPT FUNCTION 3 encryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_DES + DECRYPT FUNCTION 4 SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5; 18/31

DECRYPT FUNCTION 5 v_key raw(128) := utl_i18n.string_to_raw( 'unione', 'AL16UTF16' ); DECRYPT FUNCTION 6 begin DECRYPT FUNCTION 7 decrypted_raw := DBMS_CRYPTO.Decrypt DECRYPT FUNCTION 8 ( DECRYPT FUNCTION 9 src => HEXTORAW(v_str), DECRYPT FUNCTION 10 typ => encryption_type, DECRYPT FUNCTION 11 key => v_key DECRYPT FUNCTION 12 ); DECRYPT FUNCTION 13 return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL16UTF16'); DECRYPT FUNCTION 14 end decrypt; ENCRYPT FUNCTION 1 function encrypt(v_string in varchar2) return varchar2 is ENCRYPT FUNCTION 2 encrypted_raw RAW (2000); ENCRYPT FUNCTION 3 encryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_DES + ENCRYPT FUNCTION 4 SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5; ENCRYPT FUNCTION 5 v_key raw(128) := utl_i18n.string_to_raw('unione', 'AL16UTF16' ); ENCRYPT FUNCTION 6 begin ENCRYPT FUNCTION 7 encrypted_raw := DBMS_CRYPTO.ENCRYPT ENCRYPT FUNCTION 8 ( ENCRYPT FUNCTION 9 src => UTL_I18N.STRING_TO_RAW (v_string,'al16utf16'), ENCRYPT FUNCTION 10 typ => encryption_type, ENCRYPT FUNCTION 11 key => v_key ENCRYPT FUNCTION 12 ); ENCRYPT FUNCTION 13 return RAWTOHEX(encrypted_raw); 19/31

ENCRYPT FUNCTION 14 end encrypt; 28 rows selected. 5 WRAP Utility 를이용한 PL/SQL Source 암호화 Vi 를이용해서 Encrypt Function 의 Source encrypt.sql 파일을작성합니다. Vi 를이용해서 Decrypt Function 의 Source decrypt.sql 파일을작성합니다. OS 프롬프트에서다음의명령어로암호화된코드를생성합니다. ( 단위에서생성한 encrypt.sql 과 decrypt.sql 파일이있는위치에서수행합니다. ) - Wrap iname=encrypt.sql oname=encrypt2.sql - Wrap iname=decrypt.sql oname=decrypt2.sql SQL 로접속후 encrypt2.sql 과 decrypt2.sql 을실행시킵니다. /oracle $wrap iname=encrypt.sql oname=encrypt2.sql PL/SQL Wrapper: Release 10.2.0.5.0-64bit Production on Wed Jan 29 00:12:53 2014 Copyright (c) 1993, 2004, Oracle. All rights reserved. Processing decrypt.sql to decrypt2.sql /oracle $wrap iname=decrypt.sql oname=decrypt2.sql 20/31

PL/SQL Wrapper: Release 10.2.0.5.0-64bit Production on Wed Jan 29 00:13:20 2014 Copyright (c) 1993, 2004, Oracle. All rights reserved. Processing encrypt.sql to encrypt2.sql /oracle $sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 29 00:15:04 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @encrypt2.sql Function created. SQL> @decrypt2.sql 21/31

Function created. 5.1 Function 암호화후 Function 조회 SQL> select * from user_source where type = 'FUNCTION'; NAME TYPE LINE TEXT ---------- -------- ---------- --------------------------------------------------------------------------- DECRYPT FUNCTION 1 function decrypt wrapped a000000 34e 22/31

8 1bd 185 D/YhBYfTCm7yUqyy/+9E/BDqnq4wgwHIf65qfC8CWHPVSAxRYMzT9OVmxQngD14KlfR+Scgp OX3dCS+sN6rQeeln66/Z7U4Smu1VDlONkKyc607G/vgXevEtqxqsLTzPDx5l+/CfUKodyEC9 XSP8Yp4lge9TH5xCQUmUhJQ2wVqf/POSjKgmVmFN7kiZyWVYERx0AjvRE7GhDjeUuuXR3MUG sgc9pxk8b3csu747tfpbbugizejtlik07/rgjgw/bcnimf/h1c8qka+a/7awak0ibx6vurgy NsuYryvllVz/waqPVsJ1iNc4EigSz8KQxLKNbD7HW/g6UayOqUIisDZpiwaLYKp/kG0X0oHm zzvsnauslhyww3gkt2y/lw== ENCRYPT FUNCTION 1 function encrypt wrapped a000000 34e 23/31

8 1c2 179 ipymktihk3papm7pkzlfg8yw1ggwgzli2q5qfi4cwhpvuw+a8feu01zpwxtgul4kry1+scgp y00ddgfnofkanxfcngs5wczbz/6ki5wqio52spjbxlafew4yyou5gqw3ilwx6oov57etzdla DEYGyXfcdxG0YkZR6acBUkJBMFz1jG8Oum0JrbPnHeq6S4wuHvzUBjQ9Y7Ay2AIClJPc7U/D hdxlcg+c7d1nkgihp5hd1gqdrndtlnz+i7frr3jfc85btb9n3tj7ahrcxhy34w06rb+yz6q3 soki7z0g1zzowz5r26vlbr6qeo+c210tgg4vt2bto123ykm5+e4o24xujtmj4gvt2pidj8tt Im2JH7taPyP9 위와같이 function의 source가암호화되었습니다. 그러나 function의 name은암호화가되지않습니다. 암호화 / 복호화작업시 function의 source 내용과무관하게 function의 name만으로암호화 / 복호화작업이가능하므로 function source의암호화는의미가퇴색되게됩니다. 24/31

6 WRAP Utility 를이용한 Package 암호화 6.1 현재 Crypt Package 내용검색 SQL> select * from dba_source where type IN ('PACKAGE', 'PACKAGE BODY') and name in ('CRYPT'); OWNER NAME TYPE LINE TEXT ------------------------------ ------------------------------ ------------ ---------- ---------------------------------------------------------------------- SYS CRYPT PACKAGE 1 PACKAGE Crypt AS SYS CRYPT PACKAGE 2 FUNCTION encrypt( Str VARCHAR2 ) RETURN VARCHAR2; SYS CRYPT PACKAGE 3 FUNCTION decrypt( xcrypt VARCHAR2 ) RETURN VARCHAR2; SYS CRYPT PACKAGE 4 END Crypt2; Package 의내용을간단한검색으로확인할수있으며가장중요한키값이노출됩니다. WRAP Utility 를이용하여 Package 를암호화하겠습니다. 6.2 Package Wrap SQL> CREATE OR REPLACE PACKAGE cryptit AS 2 FUNCTION ngy_e_hwan(v_string in varchar2) return varchar2; 3 FUNCTION ngy_d_hwan(v_str in varchar2) return varchar2; 4 END CryptIT; 5 / 25/31

Package created. SQL> CREATE OR REPLACE PACKAGE BODY CryptIT AS 2 crypted_string VARCHAR2(2000); 3 4 function ngy_d_hwan(v_str in varchar2) return varchar2 is 5 decrypted_raw raw(2000); 6 encryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_DES + 7 SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5; 8 v_key raw(128) := utl_i18n.string_to_raw( 'unione', 'AL16UTF16' ); 9 begin 10 decrypted_raw := DBMS_CRYPTO.Decrypt 11 ( 12 src => HEXTORAW(v_str), 13 typ => encryption_type, 14 key => v_key 15 ); 16 return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL16UTF16'); 17 end ngy_d_hwan; 18 19 function ngy_e_hwan(v_string in varchar2) return varchar2 is 20 encrypted_raw RAW (2000); 26/31

21 encryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_DES + 22 SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5; 23 v_key raw(128) := utl_i18n.string_to_raw('unione', 'AL16UTF16' ); 24 begin 25 encrypted_raw := DBMS_CRYPTO.ENCRYPT 26 ( 27 src => UTL_I18N.STRING_TO_RAW (v_string,'al16utf16'), 28 typ => encryption_type, 29 key => v_key 30 ); 31 return RAWTOHEX(encrypted_raw); 32 end ngy_e_hwan; 33 end CryptIT; 34 / Package body created. 6.3 Wrap 을이용한 Package 암호화 /oracle $wrap iname=pcrypt.sql oname=pcrypt2.sql PL/SQL Wrapper: Release 10.2.0.5.0-64bit Production on Wed Jan 29 01:03:17 2014 27/31

Copyright (c) 1993, 2004, Oracle. All rights reserved. Processing pcrypt.sql to pcrypt2.sql /oracle $wrap iname=pcrypt_body.sql oname=pcrypt_body2.sql PL/SQL Wrapper: Release 10.2.0.5.0-64bit Production on Wed Jan 29 01:03:42 2014 Copyright (c) 1993, 2004, Oracle. All rights reserved. Processing pcrypt_body.sql to pcrypt_body2.sql /oracle $sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 29 01:12:10 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @pcrypt.sql 28/31

Package created. SQL> @pcrypt_body.sql Package body created. 6.4 Wrap 되어진 Package 내용확인 SQL> select * from dba_source where type IN ('PACKAGE', 'PACKAGE BODY') and name in ('CRYPT'); OWNER NAME TYPE LINE TEXT ---------- ---------- ------------ ---------- ---------------------------------------------------------------------- SYS CRYPT PACKAGE 1 a000000 34e 29/31

9 93 9e CuqDIxANarzBcTj+/odQqrmX7Q8wgwLZf56pfHSmZxcP98zqsdyoPN/nLRrMUvwfonHk7Yx7 vhxx5qt/ma6gg67f3xeoa8gifmo41bzblgot8t8bvd3hmcxpd+jegd8ehbm6+opzqocjylxx Lb78OQhoFnE= DBMS_CRYPTO Package 도같은방법으로 wrap 가능합니다. Package 를조회하면 Package 명은출력이되지만 Package Body 내용은출력이되지않습니다. 때문에 Package 명과 Body(Function) 명을같이알고있어야암 / 복호화가가능합니다. 7 암호화컬럼의 Index 활용 인덱스사용가능 (o) Select * From crypto_test Where 주민등록번호 = encrypt('111111-1111111'); 인덱스사용불가능 (x) Select * From crypto_test Where decrypt( 주민등록번호 )='111111-1111111'; 30/31

일반적으로컬럼사용시암호화하면인덱스사용불가합니다. 컬럼의값을확인하기위해컬럼에복호화를해야하기때문입니다. 제한되지만위의표와같은방법으로인덱스를사용할수있습니다. 31/31