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

14S_테이블스페이스 암호화 구현




테이블스페이스 암호화 구현

  • 11gR1
  • 통계정보 제약, 인덱스 제약, 참조 키에 관한 컬럼 암호화 제약은 암호화된 테이블스페이스에 적용되지 않는다.
  • 제약조건 X
    
    CREATE TABLESPACE TABLESPACE_NAME
    datafile ...
    encryption
    Using 'algorithm' ( optional )
    defult storage ( ENCRYPT );
    
    
  • 알고리즘은 사용할 암호화 형태을 지정하는 것으로 3DES168, AES128, AES129, AES256 중 하나 선택( AES128이 현재 디폴트 )

테이블스페이스 암호화가 적용된 데이터 저장

디스크에 저장

  • 컬럼 레벨 암호화와 달리, 테이블스페이스 압축에 대한 스토리지 부하는 없다.
  • 이미 모든 테이터베이스 블록이 16바이트의 배수로 되어 있기 때문에 16바이트의 배수로 추가 변환할 필요가 없다.
  • 데이터베이스 블록은 고정 길이 데이터 요소고, 암호화가 고정 길이의 결과을 발생시킨다는 살시은 관련이 없다 ( ? )
  • 각 데이터베이스 블록은 유일한 실체며, 이미 데이터가 유일하기 때문에 SALT 데이터가 존재할 필요가 없다.

SGA에 저장 ( 그림 16-2 page 945 )

  • 컬럼 레벨 암호화와 달리, 암호화 테이블스페이스의 데이터는 암호화되지 않는 상태로 SGA에 저장된다.
  • 데이터베이스 블록이 디스크에서 버퍼 캐시로 읽혀질 때 데이터베이스 블록은 복호화되어 버퍼 캐시에 저장된다.
  • SGA에서 추출되어 디스크에 저장되기 전에 DBWR이 블록을 암호화해서 디스크에 저장한다.

테이블스페이스 암호화의 성능 영향도 측정

  • conventional path 읽기만 사용한다면 이 기능의 중요한 영향도를 볼수 없을 것이다.
  • 수 기가바이트의 데이터를 direct path 로드한다면 영향도는 클것이다.
    
    SQL> create tablespace new_encrypted
      2  datafile 'D:\app\LG\data_file\new_encrypted.dbf' size 1m
      3  encryption
      4  default storage( encrypt );
    
    테이블스페이스가 생성되었습니다.
    
    SQL> create tablespace new_clear
      2  datafile 'D:\app\LG\data_file\new_clear.dbf' size 1m
      3  autoextend on next 1m;
    
    테이블스페이스가 생성되었습니다.
    
    SQL> create table scott.stage
      2  as
      3  select *
      4   from all_objects;
    
    테이블이 생성되었습니다.
    SQL> create table scott.nonencrypted
      2  tablespace new_clear
      3  as
      4  select *
      5    from scott.stage
      6   where 1=0;
    
    테이블이 생성되었습니다.
    
    SQL> alter table scott.nonencrypted add constraint nonencrypted_pk primary key( object_id )
      2  using index
      3  (create index scott.nonencrypted_pk on scott.nonencrypted( object_id )
      4  tablespace new_clear
      5   );
    
    테이블이 변경되었습니다.
    
    SQL> create table scott.encrypted
      2  tablespace new_encrypted
      3  as
      4  select *
      5    from scott.stage
      6   where 1=0
      7
    SQL> /
    
    테이블이 생성되었습니다.
    
    SQL> alter table scott.encrypted add constraint encrypted_pk primary key( object_id )
      2  using index
      3  (create index scott.encrypted_pk on scott.encrypted( object_id )
      4  tablespace new_encrypted
      5   );
    
    테이블이 변경되었습니다.
    
    SQL>
    
    
  • PL/SQL
    SQL> create or replace procedure do_sql( p_sql in varchar2
      2                                    , p_tname in varchar2
      3                                    , p_truncate in boolean default true )
      4  authid current_user -- SQL 인젝션 공격 보안 문제 회피
      5  as
      6    l_start_cpu number;
      7    l_start_redo number;
      8    l_total_redo number;
      9    function get_stat_val( p_name in varchar2 ) return number
     10    as
     11           l_val number;
     12    begin
     13       select b.value
     14             into l_val
     15         from v$statname a, v$mystat b
     16        where a.statistic# = b.statistic#
     17          and a.name = p_name;
     18
     19           return l_val;
     20   end;
     21  begin
     22    if( p_truncate )
     23    then
     24      execute immediate 'truncate table '||p_tname;
     25    end if;
     26
     27      dbms_output.put_line( replace( p_sql, '#TNAME#', p_tname) );
     28
     29      l_start_cpu := dbms_utility.get_cpu_time;
     30      l_start_redo := get_stat_val( 'redo size' );
     31
     32      execute immediate replace( p_sql, '#TNAME#', p_tname);
     33
     34      commit work write batch wait;
     35
     36      dbms_output.put_line( (dbms_utility.get_cpu_time - l_start_cpu ) || ' cpu hsecs' );
     37
     38      l_total_redo := round( ( get_stat_val('redo size') - l_start_redo ) / 1024 / 1024, 1 );
     39
     40      dbms_output.put_line( to_char( l_total_redo, '999,999,999,9')||' mbytes redo' );
     41
     42  end;
     43  /
    
    프로시저가 생성되었습니다.
    
    SQL> ALTER TABLESPACE  new_encrypted ADD DATAFILE 'D:\app\LG\data_file\new_encrypted01.dbf' SIZE 1M autoextend on next 1m;
    
    테이블스페이스가 변경되었습니다.
    
    SQL>
    SQL> begin
      2     do_sql( 'insert into #TNAME# SELECT * FROM SCOTT.STAGE', 'SCOTT.nonencrypted' );
      3     do_sql( 'insert into #TNAME# SELECT * FROM SCOTT.STAGE', 'SCOTT.encrypted'  );
      4   end;
      5   /
    insert into SCOTT.nonencrypted SELECT * FROM SCOTT.STAGE
    234 cpu hsecs
    3,2 mbytes redo
    insert into SCOTT.encrypted SELECT * FROM SCOTT.STAGE
    396 cpu hsecs
    3,2 mbytes redo
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SQL>
    -- CPU 활용 측면에서 영향도는 거의 없다는데 1.5정도  -_-
    
  • direct patch 로드
    SQL>  begin                                                                                           
      2     do_sql( 'insert /*+ append */ into #TNAME# SELECT * FROM SCOTT.STAGE', 'SCOTT.nonencrypted' );
      3     do_sql( 'insert /*+ append */ into #TNAME# SELECT * FROM SCOTT.STAGE', 'SCOTT.encrypted'  );  
      4   end;                                                                                            
      5   /                                                                                               
    insert /*+ append */ into SCOTT.nonencrypted SELECT * FROM SCOTT.STAGE                                
    207 cpu hsecs                                                                                         
    2,0 mbytes redo                                                                                       
    insert /*+ append */ into SCOTT.encrypted SELECT * FROM SCOTT.STAGE                                   
    326 cpu hsecs                                                                                         
    2,0 mbytes redo                                                                                       
                                                                                                          
    PL/SQL 처리가 정상적으로 완료되었습니다.                                                              
    -- 음..ㅠ
    
  • insert 성능
    SQL> declare
      2    l_sql long := 'begin ' || 'for x in (select * from scott.stage) ' ||
      3                              'loop ' ||
      4                                'insert into #TNAME# values x; ' ||
      5                              'end loop; ' ||
      6                   'end; ';
      7  begin
      8    do_sql( l_sql,  'SCOTT.nonencrypted' );
      9    do_sql( l_sql,  'SCOTT.encrypted'  );
     10  end;
     11  /
    begin for x in (select * from scott.stage) loop insert into SCOTT.nonencrypted
    values x; end loop; end;
    1408 cpu hsecs
    5,2 mbytes redo
    begin for x in (select * from scott.stage) loop insert into SCOTT.encrypted
    values x; end loop; end;
    1745 cpu hsecs
    5,2 mbytes redo
    
  • 조회 성능
    SQL> declare
      2    l_sql long := 'begin ' || 'for x in (select object_id from scott.stage) ' ||
      3                              'loop ' ||
      4                                'for y in (select * from #TNAME# where object_id = x.object_id ) ' ||
      5                                'loop ' ||
      6                                 'null; '  ||
      7                                 'end loop;' ||
      8                              'end loop; ' ||
      9                   'end; ';
     10  begin
     11    do_sql( l_sql,  'SCOTT.nonencrypted', false );
     12    do_sql( l_sql,  'SCOTT.encrypted', false  );
     13  end;
     14  /
    begin for x in (select object_id from scott.stage) loop for y in (select * from
    SCOTT.nonencrypted where object_id = x.object_id ) loop null; end loop;end loop;
    end;
    1120 cpu hsecs
    0 mbytes redo
    begin for x in (select object_id from scott.stage) loop for y in (select * from
    SCOTT.encrypted where object_id = x.object_id ) loop null; end loop;end loop;
    end;
    1140 cpu hsecs
    0 mbytes redo
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    
  • 강제로 물리 IO 유발
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    
    
    SQL> declare
      2    l_sql long := 'begin ' || 'for x in (select object_id from scott.stage) ' ||
      3                              'loop ' ||
      4                                'for y in (select * from #TNAME# where object_id = x.object_id ) ' ||
      5                                'loop ' ||
      6                                 'null; '  ||
      7                                 'end loop;' ||
      8                              'end loop; ' ||
      9                   'end; ';
     10  begin
     11    do_sql( l_sql,  'SCOTT.nonencrypted', false );
     12    -- do_sql( l_sql,  'SCOTT.encrypted', false  );
     13  end;
     14  /
    begin for x in (select object_id from scott.stage) loop for y in (select * from
    SCOTT.nonencrypted where object_id = x.object_id ) loop null; end loop;end loop;
    end;
    1081 cpu hsecs
    0 mbytes redo
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    
    SQL> declare
      2    l_sql long := 'begin ' || 'for x in (select object_id from scott.stage) ' ||
      3                              'loop ' ||
      4                                'for y in (select * from #TNAME# where object_id = x.object_id ) ' ||
      5                                'loop ' ||
      6                                 'null; '  ||
      7                                 'end loop;' ||
      8                              'end loop; ' ||
      9                   'end; ';
     10  begin
     11    -- do_sql( l_sql,  'SCOTT.nonencrypted', false );
     12     do_sql( l_sql,  'SCOTT.encrypted', false  );
     13  end;
     14  /
    begin for x in (select object_id from scott.stage) loop for y in (select * from
    SCOTT.encrypted where object_id = x.object_id ) loop null; end loop;end loop;
    end;
    1095 cpu hsecs
    0 mbytes redo
    
    
  • 플랜 영향도
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( 'SCOTT', 'nonencrypted' , METHOD_OPT => 'for columns object_name size 254' );
                                                                                                                          
    PL/SQL 처리가 정상적으로 완료되었습니다.                                                                              
                                                                                                                          
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( 'SCOTT', 'encrypted' , METHOD_OPT => 'for columns object_name size 254' );   
                                                                                                                          
    PL/SQL 처리가 정상적으로 완료되었습니다.                                                                              
                                                                                                                          
    SQL>                                                                                                                  
    SQL>                                                                                                                  
    SQL> set autotrace traceonly explain                                                                                  
    SQL> select * from SCOTT.nonencrypted where object_name = 'ALL_OBJECT';                                               
                                                                                                                          
    Execution Plan                                                                                                        
    ----------------------------------------------------------                                                            
    Plan hash value: 2348679572                                                                                           
                                                                                                                          
    --------------------------------------------------------------------------------                                      
    --                                                                                                                    
                                                                                                                          
    | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time                                          
     |                                                                                                                    
                                                                                                                          
    --------------------------------------------------------------------------------                                      
    --                                                                                                                    
                                                                                                                          
    |   0 | SELECT STATEMENT  |              |     2 |   196 |   342   (1)| 00:00:05                                      
     |                                                                                                                    
                                                                                                                          
    |*  1 |  TABLE ACCESS FULL| NONENCRYPTED |     2 |   196 |   342   (1)| 00:00:05                                      
     |                                                                                                                    
                                                                                                                          
    --------------------------------------------------------------------------------                                      
    --                                                                                                                    
                                                                                                                          
                                                                                                                          
    Predicate Information (identified by operation id):                                                                   
    ---------------------------------------------------                                                                   
                                                                                                                          
       1 - filter("OBJECT_NAME"='ALL_OBJECT')                                                                             
                                                                                                                          
    SQL> select * from SCOTT.encrypted where object_name = 'ALL_OBJECT';                                                  
                                                                                                                          
    Execution Plan                                                                                                        
    ----------------------------------------------------------                                                            
    Plan hash value: 827516354                                                                                            
                                                                                                                         
    -------------------------------------------------------------------------------                                       
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |                                     
    -------------------------------------------------------------------------------                                     
    |   0 | SELECT STATEMENT  |           |     2 |   196 |   342   (1)| 00:00:05 |                                     
    |*  1 |  TABLE ACCESS FULL| ENCRYPTED |     2 |   196 |   342   (1)| 00:00:05 |                                     
    -------------------------------------------------------------------------------                                     
                                                                                                                        
    Predicate Information (identified by operation id):                                                                 
    ---------------------------------------------------                                                                 
                                                                                                                        
       1 - filter("OBJECT_NAME"='ALL_OBJECT')                                                                           
                                                                                                                        
    SQL> 
    
    

문서정보

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