1. HOME
  2. [종료]구루비 DB 스터디
  3. 2014년 하반기 - 오라클 데이터베이스 스터디
  4. 14S_테이블스페이스 암호화 구현

14S_테이블스페이스 암호화 구현 (by ljh1148) [2015.02.06]

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

  • 11gR1
  • 통계정보 제약, 인덱스 제약, 참조 키에 관한 컬럼 암호화 제약은 암호화된 테이블스페이스에 적용되지 않는다.
  • 제약조건 X
    {CODE:SQL}

CREATE TABLESPACE TABLESPACE_NAME
datafile ...
encryption
Using 'algorithm' ( optional )
defult storage ( ENCRYPT );

{CODE}

  • 알고리즘은 사용할 암호화 형태을 지정하는 것으로 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
    {CODE: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정도 _
{CODE}

  • 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 유발
    {CODE: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.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

{CODE}

  • 플랜 영향도
    {CODE:SQL}
    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


--

IdOperationNameRowsBytesCost (%CPU)Time


--

0SELECT STATEMENT2196342 (1)00:00:05
  • 1
TABLE ACCESS FULLNONENCRYPTED2196342 (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

---

IdOperationNameRowsBytesCost (%CPU)Time

---

0SELECT STATEMENT2196342 (1)00:00:05
  • 1
TABLE ACCESS FULLENCRYPTED2196342 (1)00:00:05

---

Predicate Information (identified by operation id):

---

1 - filter("OBJECT_NAME"='ALL_OBJECT')

SQL>

{CODE}

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2014년 하반기 - 오라클 데이터베이스 스터디
  4. 14S_테이블스페이스 암호화 구현