전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
컬럼 레벨 암호화 구현 0 0 48,518

by 구루비스터디 데이터 암호화 ENCRYPT [2018.09.27]


컬럼 레벨 암호화 구현

  • 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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입