전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
테이블스페이스 암호화 구현 0 0 48,325

by 구루비스터디 데이터 암호화 테이블스페이스 암호화 [2018.09.27]


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

  • 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>


"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4080

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

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

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