꿈꾸는 개발자, DBA 커뮤니티 구루비

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

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>