在12.2之前，如果对表空间进行透明数据加密，这是需要停机时间的，可参考 Oracle Advanced Security 透明数据加密最佳实践，但是在12.2中，我们可以不用停机的进行TDE加密了。
是的，no downtime。
我们先来创建一个表空间，创建一个表，如信用卡信息表（credit_card表），里面放的是信用卡用户名和信用卡号。通过strings数据文件，其实我们是可以看到存储的数据的。也就是说，如果有人得到了这个数据文件，是可以窥探到其中的信息的。包括像信用卡用户和卡号这样的敏感信息。
1.先创建一个测试用户test。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[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。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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该数据文件，可以看到对应的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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目录：
|
1 2 3 4 5 6 7 8 9 10 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[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操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[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.进行在线透明数据加（注意表空间的数据文件名字会被修改成别的）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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即可：
|
1 2 |
SQL> alter tablespace tbs_test encryption online using 'AES192' 2 encrypt file_name_convert('tbs_test','tbs_test_enc'); |
6.现在，我们再次strings数据文件，可以看到已经被加密了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
[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)