전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
컬럼 레벨 암호화 구현
- 10gR2
ENCRYPT 3가지 옵션
- USING 'algorithm' : 컬럼을 AES 또는 DES 암호화를 사용할 것인지와 키을 몇 비트로 설정 선택
- IDENTIFIED BY password : 데이터을 암호화할 때 사용할 특정 키를 지정
- SALT
- 데이터베이스는 암호화하기에 앞서 데이터에 데이터 난수 바이트를 추가 - 강력
- 공격자가 특정 값을 찾을 수 있는 프로브 형태의 공격을 방해한다. - 유일한 암호화 값이 되는 것을 보장
- NO SALT : 암호화 된 컬럼에 인덱스을 사요하길 원할 때
ENCRYPT 제약 조건
- FBI X
- 참조 키
컬럼 암호화가 적용된 데이터 저장공간
- 컬럼 레벨 암호화는 물리적 추가 스토리지가 필요함
- 컬럼 레벨 암호화는 데이터를 엑세스 할 때마다 암복화를 수행하는 방식으로 SGA에 데이터을 저장한다.
- 자주 엑세스되는 컬럼은 암복호화로 인해 쿼리 성능에 영향을 미칠 것이다.
- 인덱스는 암호화된 데이터로 생선된다
컬럼 암호화로 증가된 저장공간
- 16진수 : 데이터가 항상 16의 배수로 고정된 길의 필드로 저장되기 때문이다 ( 수작업 애플리케이션 암호화 절 )
- SALT
- 문자열 앞에 10개의 난수 바이트 데이터가 항상 존재, HELLO 문자열을 100만번 인서트 한다면 임의값 100만개 인서트됨 - 유일한 암호화값
- 복호화시 처음 10바이트는 무시해고 반환 ( 데이터 프로브 방지 )
SQL> create table scott.t
2 ( last_name varchar2( 30 )
3 , encrypted_name varchar2( 30 ) encrypt
4 )
5 ;
테이블이 생성되었습니다.
SQL> insert into scott.t( last_name )
2 select object_name from scott.stage;
84190개의 행이 생성되었습니다.
SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL )
7 authid current_user
8 as
9 l_free_blks number;
10
11 l_total_blocks number;
12 l_total_bytes number;
13 l_unused_blocks number;
14 l_unused_bytes number;
15 l_LastUsedExtFileId number;
16 l_LastUsedExtBlockId number;
17 l_LAST_USED_BLOCK number;
18
19 l_UNFORMATTED_BLOCKS number;
20 l_UNFORMATTED_BYTES number;
21
22 l_FS1_BLOCKS number;
23 l_FS1_BYTES number;
24 l_FS2_BLOCKS number;
25 l_FS2_BYTES number;
26 l_FS3_BLOCKS number;
27 l_FS3_BYTES number;
28 l_FS4_BLOCKS number;
29 l_FS4_BYTES number;
30 l_FULL_BLOCKS number;
31 l_FULL_BYTES number;
32
33 procedure p( p_label in varchar2, p_num in number )
34 is
35 begin
36 dbms_output.put_line( rpad(p_label,40,'.') ||
37 p_num );
38 end;
39 begin
40 for x in ( select tablespace_name
41 from dba_tablespaces
42 where tablespace_name = ( select tablespace_name
43 from dba_segments
44 where segment_type = p_type
45 and segment_name = p_segname
46 and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
47 )
48 loop
49 dbms_space.free_blocks
50 ( segment_owner => p_owner,
51 segment_name => p_segname,
52 segment_type => p_type,
53 partition_name => p_partition,
54 freelist_group_id => 0,
55 free_blks => l_free_blks
56 --,
57 --UNFORMATTED_BLOCKS => l_UNFORMATTED_BLOCKS
58 );
59 end loop;
60
61 dbms_space.space_usage
62 (segment_owner => p_owner,
63 segment_name => p_segname,
64 segment_type => p_type,
65 UNFORMATTED_BLOCKS => l_UNFORMATTED_BLOCKS,
66 UNFORMATTED_BYTES => l_UNFORMATTED_BYTES,
67 FS1_BLOCKS => l_FS1_BLOCKS,
68 FS1_BYTES => l_FS1_BYTES,
69 FS2_BLOCKS => l_FS2_BLOCKS,
70 FS2_BYTES => l_FS2_BYTES,
71 FS3_BLOCKS => l_FS3_BLOCKS,
72 FS3_BYTES => l_FS3_BYTES,
73 FS4_BLOCKS => l_FS4_BLOCKS,
74 FS4_BYTES => l_FS4_BYTES,
75 FULL_BLOCKS => l_FULL_BLOCKS,
76 FULL_BYTES => l_FULL_BYTES
77 );
78
79 dbms_space.unused_space
80 ( segment_owner => p_owner,
81 segment_name => p_segname,
82 segment_type => p_type,
83 partition_name => p_partition,
84 total_blocks => l_total_blocks,
85 total_bytes => l_total_bytes,
86 unused_blocks => l_unused_blocks,
87 unused_bytes => l_unused_bytes,
88 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
89 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
90 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
91
92 p( 'UNFORMATTED_BLOCKS', l_UNFORMATTED_BLOCKS );
93 p( 'FS1_BLOCKS', l_FS1_BLOCKS );
94 p( 'FS2_BLOCKS', l_FS2_BLOCKS );
95 p( 'FS3_BLOCKS', l_FS3_BLOCKS );
96 p( 'FS4_BLOCKS', l_FS4_BLOCKS );
97 p( 'FULL_BLOCKS', l_FULL_BLOCKS );
98
99 -- p( 'Free Blocks', l_free_blks );
100 p( 'Total Blocks', l_total_blocks );
101 p( 'Total Bytes', l_total_bytes );
102 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
103 p( 'Unused Blocks', l_unused_blocks );
104 p( 'Unused Bytes', l_unused_bytes );
105 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
106 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
107 p( 'Last Used Block', l_LAST_USED_BLOCK );
108 end;
109 /
프로시저가 생성되었습니다.
SQL>
SQL> exec show_space( 'T','SCOTT', 'TABLE', NULL );
UNFORMATTED_BLOCKS......................0
FS1_BLOCKS..............................1
FS2_BLOCKS..............................0
FS3_BLOCKS..............................0
FS4_BLOCKS..............................24
FULL_BLOCKS.............................345
Total Blocks............................384 -- 384 블럭 사용
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................896
Last Used Block.........................128
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> truncate table SCOTT.t;
테이블이 잘렸습니다.
SQL> insert into scott.t( encrypted_name )
2 select object_name from scott.stage;
84190개의 행이 생성되었습니다.
SQL>
SQL> exec show_space( 'T','SCOTT', 'TABLE', NULL );
UNFORMATTED_BLOCKS......................0
FS1_BLOCKS..............................0
FS2_BLOCKS..............................0
FS3_BLOCKS..............................0
FS4_BLOCKS..............................20
FULL_BLOCKS.............................854
Total Blocks............................896 -- 896 블럭 사용
Total Bytes.............................7340032
Total MBytes............................7
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................1408
Last Used Block.........................128
PL/SQL 처리가 정상적으로 완료되었습니다.
SALT 배제 테스트
SQL> truncate table SCOTT.t
2 ;
테이블이 잘렸습니다.
SQL> ALTER TABLE SCOTT.t MODIFY encrypted_name ENCRYPT NO SALT;
테이블이 변경되었습니다.
SQL>
SQL> insert into scott.t( encrypted_name )
2 select object_name from scott.stage;
84190개의 행이 생성되었습니다.
SQL> exec show_space( 'T','SCOTT', 'TABLE', NULL );
UNFORMATTED_BLOCKS......................62
FS1_BLOCKS..............................0
FS2_BLOCKS..............................0
FS3_BLOCKS..............................1
FS4_BLOCKS..............................20
FULL_BLOCKS.............................665
Total Blocks............................768 -- 16의 배수 고정 길이 필드로 저장
Total Bytes.............................6291456
Total MBytes............................6
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................1280
Last Used Block.........................128
PL/SQL 처리가 정상적으로 완료되었습니다.
SGA에 저장 ( 그림 16-1 page 934 )
- 컬럼 레벨 암호화는 데이터가 암호화된 형태로 블록버퍼 캐시에 저장됨
- 데이터베이스가 디스크에서 데이터를 조회하려고 물리적 IO를 수행 하였을때, 암호화된 컬럼은 SGA에 암호화된 상태로 남는다
- 이 컬럼에 대한 모든 액세스는 데이터 복호화 처리를 수행하고, 수정이 일어나면 후속 암호화 절차가 함께 수반된다는 것이다.
- 암호화 된 컬럼은 INDEX RANGE SCAN을 사용 할 수 없다. ( 암호화된 데이터로 인덱스가 생성됨 )
컬럼 암호화 성능 영향 측정법
- 성능 부하는 얼마나 발생하는가? = 엑세스 비도 와 방법에따라 좌우됨
영향을 미치는 정도
에섹스 패턴 1
create table custormer(
cust_id number primary key,
.. other data ..
credit_card# varchar2( 50 ) encrypt
)
- 고객이 결재할 때 조회한다. CUST_ID의 기본키 인덱스로 단지 신용카드 한 로우만 조회한다.
- 고객이 신용카드 정보를 업데이트 할 때( 매년 한 번 또는 두 번 카드 만료 시점에 ) CREDIT_CARE#을 수정한다. ( 미비 )
- 새로운 고객의 고객 레코드가 INSERT 될 때 수정된다, 다행이 특정 시간에 집중되지 않고, 오랜 시간에 걸쳐 많은 새로운 고객들이 추가된다.
- 고객이 추가된다 할지라도 상대적으로 드문 경우를 말하는 것이다.
패턴 2 - 극단적
create table custormer(
cust_id number primary key,
number_of_page_views number encrypt, -- 매번 사이트의 페이지를 클릭시 업데이트 컬럼
.. other data ..
credit_card# varchar2( 50 ) encrypt
)
- 사용자가 모든 페이지에 읽기/쓰기 방식으로 액세스할 때마다 발생한다 => 높은 암호화 및 복호화 비용 발생
암호화의 영향도
cpu 사용량 및 redo 사이즈 영향도
SQL> create table scott.stage
2 as
3 select object_name
4 from all_objects;
테이블이 생성되었습니다.
SQL> create table scott.t
2 (
3 non_encrypted varchar2( 30 ),
4 encrypted varchar2( 30 ) encrypt
5 )
6 ;
테이블이 생성되었습니다.
SQL> create or replace procedure do_sql( p_sql in varchar2, p_truncate in boolean default true )
2 authid current_user -- SQL 인젝션 공격 보안 문제 회피
3 as
4 l_start_cpu number;
5 l_start_redo number;
6 l_total_redo number;
7 function get_stat_val( p_name in varchar2 ) return number
8 as
9 l_val number;
10 begin
11 select b.value
12 into l_val
13 from v$statname a, v$mystat b
14 where a.statistic# = b.statistic#
15 and a.name = p_name;
16
17 return l_val;
18 end;
19 begin
20 if( p_truncate )
21 then
22 execute immediate 'truncate table scott.t';
23 end if;
24 dbms_output.put_line( p_sql );
25
26 l_start_cpu := dbms_utility.get_cpu_time;
27
28 l_start_redo := get_stat_val( 'redo size' );
29
30 execute immediate p_sql;
31
32 commit work write batch wait;
33
34 dbms_output.put_line( (dbms_utility.get_cpu_time - l_start_cpu ) || ' cpu hsecs' );
35
36 l_total_redo := round( ( get_stat_val('redo size') - l_start_redo ) / 1024 / 1024, 1 );
37
38 dbms_output.put_line( to_char( l_total_redo, '999,999,999,9')||' mbytes redo' );
39
40 end;
41 /
프로시저가 생성되었습니다.
SQL>
SQL> begin
2 do_sql( 'insert into scott.t( non_encrypted ) '||'select object_name from scott.stage' );
3 do_sql( 'insert into scott.t( encrypted ) '||'select object_name from scott.stage' );
4 end;
5 /
insert into scott.t( non_encrypted ) select object_name from scott.stage
21 cpu hsecs
3 mbytes redo
insert into scott.t( encrypted ) select object_name from scott.stage
217 cpu hsecs
7 mbytes redo
PL/SQL 처리가 정상적으로 완료되었습니다.
- cpu : 10배 정도, redo : 2배 이상 ( 수작업 암호화 보다 적정 )
로우 단위 영향도
SQL> declare
2 l_sql long := 'begin ' || 'for x in (select object_name from scott.stage) ' ||
3 'loop ' ||
4 'insert into scott.t("CNAME") ' ||
5 'VALUES ( X.OBJECT_NAME ); ' ||
6 'end loop; ' ||
7 'end; ';
8 begin
9 do_sql( replace( l_sql, '"CNAME"', 'non_encrypted') );
10 do_sql( replace( l_sql, '"CNAME"', 'encrypted' ) );
11 end;
12 /
begin for x in (select object_name from scott.stage) loop insert into
scott.t(non_encrypted) VALUES ( X.OBJECT_NAME ); end loop; end;
752 cpu hsecs
2,1 mbytes redo
begin for x in (select object_name from scott.stage) loop insert into
scott.t(encrypted) VALUES ( X.OBJECT_NAME ); end loop; end;
1658 cpu hsecs
2,5 mbytes redo
PL/SQL 처리가 정상적으로 완료되었습니다.
- CPU : 2.1배, 약간의 redo
데이터 조회 비용
SQL> truncate table scott.t;
테이블이 잘렸습니다.
SQL> insert into scott.t select object_name, object_name from scott.stage;
84196개의 행이 생성되었습니다.
SQL> exec dbms_stats.gAther_table_stats( 'SCOTT', 'T' );
PL/SQL 처리가 정상적으로 완료되었습니다.
3 'loop ' ||
4 'null; ' ||
5 'end loop; ' ||
6 'end; ';
7 begin
8 do_sql( replace( l_sql, '#CNAME#', 'non_encrypted'), false );
9 do_sql( replace( l_sql, '#CNAME#', 'encrypted' ), false );
10 end;
11 /
begin for x in (select non_encrypted from scott.t) loop null; end loop; end;
11 cpu hsecs
0 mbytes redo
begin for x in (select encrypted from scott.t) loop null; end loop; end;
140 cpu hsecs
0 mbytes redo
PL/SQL 처리가 정상적으로 완료되었습니다.
- CPU : 12배 정도
암호화된 컬럼 통계정보
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
SQL>
SQL> select * from scott.t where non_encrypted = 'ALL_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 2 | 184 | 342 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NON_ENCRYPTED"='ALL_OBJECTS')
SQL>
SQL> select * from scott.t where encrypted = 'ALL_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 842 | 77464 | 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 842 | 77464 | 342 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ENCRYPTED")='ALL_OBJECTS')
컬럼 암호화 제약
- 인덱스 사용 능력 감소 : 정렬 X ( >= : X, = O )
- 인덱스 사용시 보호 감소 : 암호화된 컬럼에 인덱스가 있다면, 오라클이 일반적으로 암호화된 값에 추가하는 SALT를 사용할 수 없다
- 함수 기반 인덱스 사용 불가
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4079
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.