12.2 online TDE

在12.2之前,如果对表空间进行透明数据加密,这是需要停机时间的,可参考 Oracle Advanced Security 透明数据加密最佳实践,但是在12.2中,我们可以不用停机的进行TDE加密了。

是的,no downtime。

我们先来创建一个表空间,创建一个表,如信用卡信息表(credit_card表),里面放的是信用卡用户名和信用卡号。通过strings数据文件,其实我们是可以看到存储的数据的。也就是说,如果有人得到了这个数据文件,是可以窥探到其中的信息的。包括像信用卡用户和卡号这样的敏感信息。

1.先创建一个测试用户test。

[oracle12c@testdb10 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 12 23:28:07 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create tablespace tbs_test datafile size 100m;

Tablespace created.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource,unlimited tablespace to test;

Grant succeeded.

2.在测试用户下创建信用卡信息表,insert数据。并做个move。

SQL> conn test/test
Connected.
SQL> create table credit_card (name varchar2(20), card_no varchar2(50)) tablespace tbs_test;

Table created.

SQL> insert into credit_card (name,card_no) select 'Jimmy',rownum+1000 from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.


SQL> insert into test.credit_card select * from test.credit_card ;

10000 rows created.

SQL> /

20000 rows created.

SQL> commit;

Commit complete.


SQL> alter table test.credit_card move;

Table altered.

3.找到该表所在的表空间是在哪个数据文件上,我们通过strings该数据文件,可以看到对应的信息:

SQL> select file_name from dba_data_files where tablespace_name='TBS_TEST';

FILE_NAME
--------------------------------------------------------------------------------
/u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_tbs_test_ddbt8d1l_.dbf

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle12c@testdb10 ~]$
[oracle12c@testdb10 ~]$
[oracle12c@testdb10 ~]$
[oracle12c@testdb10 ~]$ strings /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_tbs_test_ddbt8d1l_.dbf |more
}|{z
ORA12C
TBS_TEST
AAAAAAAAAAAAAAAA
, : H V d r
Jimmy
3041,
Jimmy
3042,
Jimmy
3043,
Jimmy
3044,
Jimmy
3045,
Jimmy
3046,
Jimmy
3047,
Jimmy
3048,
Jimmy
3049,
Jimmy
3050,
Jimmy
3051,
Jimmy
3052,
Jimmy
3053,
Jimmy
3054,
Jimmy
3055,
Jimmy
3056,
Jimmy
3057,
Jimmy
3058,
Jimmy
3059,
Jimmy
3060,
Jimmy
3061,
Jimmy
3062,
Jimmy
3063,
Jimmy
3064,
……

[oracle12c@testdb10 ~]$

好了。我们现在利用12.2的online TDE功能进行数据加密。

4. 首先,做一些启用TDE的准备工作,这在12.1的时候也是这么操作的:
 4.1 创建keystore目录:

[oracle12c@testdb10 ~]$ cd $ORACLE_HOME
[oracle12c@testdb10 db_1]$ ls
addnode bin cfgtoollogs css data dc_ocm diagnostics env.ora install javavm jlib log network odbc opmn ord oss perl QOpatch rdbms schagent.conf sqldeveloper sqlplus sysman utl
apex ccr clone ctx dbjava deinstall dmu has instantclient jdbc ldap md nls olap oracore ordim oui plsql R relnotes scheduler sqlj srvm ucp wwg
assistants cdata crs cv dbs demo dv hs inventory jdk lib mgw oc4j OPatch oraInst.loc ords owm precomp racg root.sh slax sqlpatch suptools usm xdk
[oracle12c@testdb10 db_1]$ mkdir keystore
[oracle12c@testdb10 db_1]$ cd keystore
[oracle12c@testdb10 keystore]$ pwd
/u01/ora12c/app/oracle/product/12.2.0.1/db_1/keystore
[oracle12c@testdb10 keystore]$

4.2 修改sqlnet.ora

[oracle12c@testdb10 keystore]$ cd $ORACLE_HOME/network/admin
[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$ ls
samples shrept.lst sqlnet.ora
[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$ ##修改前:
[oracle12c@testdb10 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/ora12c/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$ ##修改后:
[oracle12c@testdb10 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/ora12c/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/ora12c/app/oracle/product/12.2.0.1/db_1/keystore/)
[oracle12c@testdb10 admin]$
[oracle12c@testdb10 admin]$

4.3 创建和打开keystore密码
 注:ADMINISTER KEY MANAGEMENT命令是12.1引入的命令,之前11g和10g是用ALTER SYSTEM SET ENCRYPTION KEY和ALTER SYSTEM SET ENCRYPTION WALLET操作:

[oracle12c@testdb10 admin]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 12 23:53:49 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> administer key management create keystore
2 '/u01/ora12c/app/oracle/product/12.2.0.1/db_1/keystore' identified by oracleblog;

keystore altered.

SQL>
SQL> administer key management set keystore
2 open identified by oracleblog;

keystore altered.

SQL> administer key management set key
2 identified by oracleblog with backup;

keystore altered.

SQL>

5.进行在线透明数据加(注意表空间的数据文件名字会被修改成别的):

SQL> select tablespace_name,file_name from dba_data_files
SQL> /

TABLESPACE_NAME FILE_NAME
---------------------------------------- --------------------------------------------------------------------------------
SYSTEM /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_system_ddbr1kg4_.dbf
SYSAUX /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_sysaux_ddbr1x14_.dbf
UNDOTBS1 /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_undotbs1_ddbr23dc_.dbf
USERS /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_users_ddbr2ktc_.dbf
TBS_TEST /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_tbs_test_ddbt8d1l_.dbf

SQL>
SQL>
SQL>
SQL> alter tablespace tbs_test encryption online using 'AES192' encrypt ;

Tablespace altered.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
---------------------------------------- --------------------------------------------------------------------------------
SYSTEM /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_system_ddbr1kg4_.dbf
SYSAUX /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_sysaux_ddbr1x14_.dbf
UNDOTBS1 /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_undotbs1_ddbr23dc_.dbf
USERS /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_users_ddbr2ktc_.dbf
TBS_TEST /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_tbs_test_ddbw3m85_.dbf

SQL>
SQL>

注意我们这里使用了omf,所以不用指定TDE之后的数据文件名,如果你原来不使用omf,可以手工的指定转换的文件名,只需加上file_name_convert即可:

SQL> alter tablespace tbs_test encryption online using 'AES192'
2 encrypt file_name_convert('tbs_test','tbs_test_enc');

6.现在,我们再次strings数据文件,可以看到已经被加密了:

[oracle12c@testdb10 admin]$ strings /u01/ora12c/app/oracle/oradata/ORA12C/datafile/o1_mf_tbs_test_ddbw3m85_.dbf |more
}|{z
ORA12C
TBS_TEST
r9Y(
%mja
YPQ#
*]dV
/MN5
><=z9
?EM+$qWg
y/I9
USBV%1l
,xoY
Sk,|
hk*s
NB?:
j]rv
}Yf#
rwTY
Ytho
Y0\V
Fd"^
|Gmx\c
p z<
[5a%
qgc
MCxB
Mj1q$a
P_Uq
Xw_&K/t
MF*~
j^ t
[P]Z
XTXz
B@U&
l?T-
wgn5
4b~]M
T(Qa
9m= ((6
U"bk
F~mq
6veW
jNEc
}:{Qh
]-?b
)dEND
_rd~
2"O:
jVW"
FtG;PXl~3T
>=y.
Uc7;
:!a[
:L~\O
fk4[[
ZB3+
3jU\
'm!6
eSm_
F,j(d|
T*O
/BI;
"Xay
reIQ!
EGEn7
[oracle12c@testdb10 admin]$

最后,需要提醒的是,虽然TDE已经可以在线操作,但是在转换的过程中,还是会对性能有一定的影响,根据swingbench的测试,对OLTP系统大约会有50%的性能影响,也就是说,OLTP每秒事务数下降一半。

所以不建议在业务时间段进行TDE的转换操作,而是找非业务峰值的时间进行操作。

参考:
 1. Oracle Advanced Security 透明数据加密最佳实践
 2. Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)
 3. 12c Release 2 — Transparent Data Encryption online !
 4. ORACLE DATABASE 12.2 — NEW FEATURE: ONLINE TRANSPARENT DATA ENCRYPTION (TDE)

Show your support

Clapping shows how much you appreciated 小荷’s story.