by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_컬럼 레벨 암호화 구현




컬럼 레벨 암호화 구현

  • 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를 사용할 수 없다
  • 함수 기반 인덱스 사용 불가

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.