首页 » ORACLE, ORACLE 12C » Oracle 12cR2新特性:Online Tablespace encryption (Transparent Data Encryption)

Oracle 12cR2新特性:Online Tablespace encryption (Transparent Data Encryption)

默认数据库中的数据文件都是未加密的,或者说是可以直接使用如OS strings命令读取数据文件,明文显示的部分内容,如有些敏感信息都是明文存储,这样就可以直接从存储中读取此类数据如手机号、身份证、信用卡、或密码。Tablespace encryption 使用的是TDE技术, Transparent Data Encryption (以下简称TDE) 是Oracle Advanced Security Option中引入从存储层对数据文件加密的技术 . TDE把存储密钥存储在数据库外部,加密表空间不会给数据存储带来额外的空间。

TDE是从Oracle 10g R2版本时引入,Transparent Tablespace Encryption对表空间的加密是在ORACLE 11G R1版本引入,但是只支持对新建表空间时指定加密选项,虽然可以先创建加密表空间逐个移入表对象对其加密,但是那样对于Huge数据库就意味着申请很多的时间维护窗口,从12C R2版本引入了online tablespace encryption的新特性,同时还引入了新的管理密钥的方法,下面是我对12.2 TDE新特性的一些总结。

以下Demo环境中是12.2 的多租户环境,TDE对是否为多租户都是支持的, ORACLE表示非多租户架构已经deprecated, 当然目前还不是Desupported,但是多租户已是趋势,对于不存在多PDB需求的用户,可以使用1CDB和1PDB的组合,同样不需要购买Multitenant license。

12.2 TDE 特性

12.2 TDE引入了新的密钥管理方法,之前的版本中一直叫Wallet(钱包),在12C版本中叫Keystore(密钥库)。Keystore可以分软件或硬件,关于硬件的不在这篇的描述范围可以查看这里。 过去版本中的PKI已经deprecated,引入 ADMINISTER KEY MANAGEMENT 新的命令替换过去的 ALTER SYSTEM SET ENCRYPTION WALLET 和 ALTER SYSTEM SET ENCRYPTION KEY命令;在多租户环境中ROOT container (CDB$ROOT)必须有1个打开的Keystore (Wallet)和1个可用的Master Encryption Key;同时12.2 中online tablespace encryption功能就意味着可以在不停业务的情况下,对已存在的表空间进行加密,原理应该和ONLINE tablespace Move一样,转换中DBWR写两份,完成后remove原文件,切到新文件;另外在12.2中可以加密整个数据库所有表空间,如SYSTEM、SYSAUX 、TEMP 、 UNDO tablespace同样支持,但是加密这些系统表空间不能指定 encryption key,但是如果system等系统表空间已加密,那KEYSOTRY就不能再关闭了,否则会报ORA-28439. 但是注意ORACLE是不建议对这些系统表空间加密的,除非有业务数据在这些表空间或需要有些PL/SQL对象中的信息. 注意于Temp 表空间的加密,也只能使用增加新的删除旧的方式。

TDE在公有云不是一个OPTION,另外有个UNDOCUMENT参数encrypt_new_tablespace在DBaaS Cloud环境中默认会加密码新建的表空间,参数描述如下:

The ENCRYPT_NEW_TABLESPACES initialization parameter controls default encryption of new tablespaces. In Database as a Service databases, this parameter is set to CLOUD_ONLY.
Any tablespace created will be transparently encrypted with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause.

Step 1: Setup a Keystore(密钥库) Location:

密钥库存储是的TDE的密钥信息,路径可以在sqlnet.ora中ENCRYPTION_WALLET_LOCATION参数指定,查询Keystore的方法顺序是:

if  ENCRYPTION_WALLET_LOCATION in sqlnet.ora
else WALLET_LOCATION in sqlnet.ora
else $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet

Oracle 建议将 Oracle Keysotre(Wallet) 放置在 $ORACLE_BASE 目录树之外,以避免意外将钱包与加密数据一起存储在备份磁带上, 如果是RAC 建议放在ASM OR  ACFS 共享存储上。Demo使用的本地文件系统。

# mkdir -pv /etc/ORACLE/anbob/encryption_keystore
# cd /etc
# chown -R oracle:oinstall ORACLE
# chmod -R 700 ORACLE
编辑"$ORACLE_HOME/network/admin/sqlnet.ora"文件, 增加下面的记录:

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)
(METHOD_DATA =(DIRECTORY = /etc/ORACLE/anbob/encryption_keystore/))
)

如果使用ASM 路径如(DIRECTORY=+DATA/PRODCDB/WALLET)

Step 2: Create a Keystore(密钥库):

Keystore在12C的多租户架构下必须创建在root container, 创建可以使用sysdba或syskm . software keystore因为是file 类型,一旦创建就会在localtion对应的目录中创建一个ewallet.p12的文件,Keystore的状态可 查询v$encryption_wallet

oracle@anbob ~]$ sqlplus / as  syskm
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 15:56:51 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/anbob/encryption_keystore/' IDENTIFIED BY "Anbob.com";
keystore altered.

---- 
---- keystore_location : Keystore Location mentioned in SQLNET.ORA file
---- keystore_password : Password for opening the Keystore

SQL> host ls -l /etc/ORACLE/anbob/encryption_keystore
total 4
-rw------- 1 oracle oinstall 2408 Jul 27 15:57 ewallet.p12

SQL> col WRL_PARAMETER for a45
SQL> select * from v$encryption_wallet;

WRL_TYPE    WRL_PARAMETER                                 STATUS     WALLET_TYPE   WALLET_OR FULLY_BAC     CON_ID
----------- --------------------------------------------- ---------- ------------- --------- --------- ----------
FILE        /etc/ORACLE/anbob/encryption_keystore/        CLOSED     UNKNOWN       SINGLE    UNDEFINED          1

Step 3: Open the Keystore(密钥库):

需要在root container打开密钥库,如果没有使用CONTAINER=ALL 只影响当前的container.状态发生改变

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Anbob.com" CONTAINER=ALL;
keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                 STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- --------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /etc/ORACLE/anbob/encryption_keystore/        OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNDEFINED          1

如果CLOSE 使用
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "Anbob.com" CONTAINER=ALL;

Step 4: Create TDE Master Encryption Key:

打开密钥库后还必须要在root container和每个PDB创建一个主密钥,可以使用CONTAINER=ALL一条命令创建,如果没带还需要在每个PDB中创建,创建后可以在 V$ENCRYPTION_KEYS view查询,同时密钥库状态改变,密钥一定要保管好,每次修改记的备份和异地保存

语法:
ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = ALL | CURRENT];
--- 
--- tag : It is the associated attributes and infomration that we define.
--- password : It is the mandatory Keystore password defined while creating the Keystore.
--- WITH BACKUP : This optional parameter creates a backup of the Keystore. We must use this option for a password based Keystore. Optionally,we can use the 'USING' clause to add a brief description about the backup.
--- CONTAINER: This parameter is for use in a multi-tenant environment (CDB). We can use ALL option for creating Master Encryption Key in all associated PDBs and CURRENT for the current PDB or ROOT (CDB$ROOT) container. If omitted the default is the CURRENT container.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "Anbob.com" WITH BACKUP CONTAINER=ALL;
keystore altered.

SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE             WRL_PARAMETER                                 STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- --------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /etc/ORACLE/anbob/encryption_keystore/        OPEN                           PASSWORD             SINGLE    NO                 1

SQL> SELECT con_id, key_id FROM v$encryption_keys;
    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
         3 AftVI2YmxE/Uv9MrhgEDqBMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         1 Aco82tv4Rk9rv4/L7JSWusMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
如果不创建密钥,加密时会提示
SQL> create tablespace user_enc datafile '/u02/app/oracle/oradata/anbob/pdbanbob/user_enc.dbf' size 10m encryption using 'AES128' encrypt;
create tablespace user_enc datafile '/u02/app/oracle/oradata/anbob/pdbanbob/user_enc.dbf' size 10m encryption using 'AES128' encrypt
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet

Step 5: Encrypt the Tablespace

在创建了密钥后并打开了密钥库后,下面可以测试加密表空间了,在12.2中可以在创建表空间时加密,同时也可以对已存在的表空间在线加密解密,首先创建个不加密表空间,我们尝试使用strings从数据库外读取数据,再加密后尝试。

SQL> alter session set container=pdbanbob;
Session altered.

SQL> create tablespace user_enc datafile '/u02/app/oracle/oradata/anbob/pdbanbob/user_enc.dbf' size 10m ;
Tablespace created.

SQL> select tablespace_name,status,contents,encrypted from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS              ENC
------------------------------ --------- --------------------- ---
SYSTEM                         ONLINE    PERMANENT             NO
SYSAUX                         ONLINE    PERMANENT             NO
UNDOTBS1                       ONLINE    UNDO                  NO
TEMP                           ONLINE    TEMPORARY             NO
USERS                          ONLINE    PERMANENT             NO
LOWER                          ONLINE    PERMANENT             NO
USER_ENC                       ONLINE    PERMANENT             NO

SQL> @ls USER_ENC

TABLESPACE_NAME                   FILE_ID FILE_NAME                                           EXT         MB      MAXSZ
------------------------------ ---------- --------------------------------------------------- --- ---------- ----------
USER_ENC                               55 /u02/app/oracle/oradata/anbob/pdbanbob/user.dbf     NO          10

SQL> create table t_user (id int, name varchar2(10), phone varchar2(100)) tablespace USER_ENC;
Table created.

begin
 for i in 1..100 loop
 insert into t_user values (i,'anbob'||i,'138'||lpad(i,8,'0'));
 dbms_lock.sleep(5);
 end loop;
 commit;
end;
/

[oracle@anbob admin]$  strings /u02/app/oracle/oradata/anbob/pdbanbob/user.dbf  
}|{z
Yt"NANBOB
USER_ENC
AAAAAAAA
anbob100
13800000100,
anbob99
13800000099,
anbob98
13800000098,
anbob97
13800000097,
anbob96
13800000096,
anbob95
13800000095,
anbob94
13800000094,
...

Note:
在加密前可以直接从操作系统读取到数据库内的敏感信息。下面在新开一个会话,在不停的insert数据,模仿在线应用,另一个会话直接对存在的表空间加密。

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Anbob.com" CONTAINER=ALL;
keystore altered.

begin
 for i in 101..150 loop
 insert into t_user values (i,'anbob'||i,'138'||lpad(i,8,'0'));
 dbms_lock.sleep(5);
 end loop;
 commit;
end;
/
ONLINE  ENCRYPT TABLESPACE
SQL> ALTER TABLESPACE USER_ENC ENCRYPTION ONLINE using 'aes192'  encrypt file_name_convert = ('user.dbf','user_enc.dbf');
Tablespace altered.

加密算法可以使用下面的:
3DES168
AES128
AES192 (default)
AES256

SQL>  select tablespace_name,status,contents,encrypted from dba_tablespaces;
TABLESPACE_NAME                STATUS    CONTENTS              ENC
------------------------------ --------- --------------------- ---
SYSTEM                         ONLINE    PERMANENT             NO
SYSAUX                         ONLINE    PERMANENT             NO
UNDOTBS1                       ONLINE    UNDO                  NO
TEMP                           ONLINE    TEMPORARY             NO
USERS                          ONLINE    PERMANENT             NO
LOWER                          ONLINE    PERMANENT             NO
USER_ENC                       ONLINE    PERMANENT             YES

SQL> select * from t_user;

        ID NAME       PHONE
---------- ---------- ---------------------------------------------------
         1 anbob1     13800000001
         2 anbob2     13800000002
         3 anbob3     13800000003
...
       142 anbob142   13800000142
       143 anbob143   13800000143
       144 anbob144   13800000144
       145 anbob145   13800000145
       146 anbob146   13800000146
       147 anbob147   13800000147
       148 anbob148   13800000148
       149 anbob149   13800000149
       150 anbob150   13800000150

[oracle@anbob ~]$ strings /u02/app/oracle/oradata/anbob/pdbanbob/user_enc.dbf|head
}|{z
Yt"NANBOB
USER_ENC
k+4d
U#f&
R\kBy
'7&A
qleT
I(U"
NaHS

ONLINE DECRYPT TABLESPACE
SQL>  ALTER TABLESPACE USER_ENC ENCRYPTION ONLINE DECRYPT file_name_convert = ('user_enc.dbf', 'user.dbf');
Tablespace altered.

Note:

加密后的表空间,数据文件已经在存储层加密,无法再使用strings读取数据,但在密钥库打开的情况下,应用可以透明的读取,同时是在线加密,对当前业务不需要中断, 因当前的表空间较小所以时间很快,如果几百G的空间可以需要几小时,所以不要在业务忙和对该表空间写操作较多时在在线加密的操作。 如果不是OMF模式管理的数据文件需要使用file_name_convert转换前后文件名。据其它用户测试在线加密表空间中原SQL最终用户响应时间的性能影响为 4% 至 8%,CPU 利用率提高了 1% 至 5%。 对整个实例的性能影响可能在50%左右,相关的等待事件主要是I/O类,时间mode中可以看到Tablespace encryption elapsed time较高。

 

Setup Auto logon KEYSTORE

我们可以创建自动登录或本地登录密钥库; 以避免每次手动打开Keystore。一旦创建了自动登录的密钥会在密钥库路径下创建’cwallet.sso’文件。要使密钥库自动打开,请使用以下命令:

ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY keystore_password;

---  
--- LOCAL: enables us to create a local auto-login keystore. Otherwise, skip this clause if you want the keystore to be accessible by other computers.
--- keystore_location: Location of the Keystore 
--- keystore_password: password of the password-based keystore for which we want to create the auto-login keystore

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/anbob/encryption_keystore/' IDENTIFIED BY "Anbob.com";
keystore altered.

SQL> ho ls -lrt /etc/ORACLE/anbob/encryption_keystore/
total 20
-rw------- 1 oracle oinstall 2408 Jul 27 16:10 ewallet_2017072708103106.p12
-rw------- 1 oracle oinstall 5328 Jul 27 16:10 ewallet.p12
-rw------- 1 oracle oinstall 5379 Aug  1 15:21 cwallet.sso

SQL> SELECT * FROM v$encryption_wallet

WRL_TYPE             WRL_PARAMETER                  STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
FILE                                                OPEN                           LOCAL_AUTOLOGIN      SINGLE    NO                 3

Export\Import encryption keys

迁移PDB包含加密表空间时需要导出密钥,同时使用一个密码再次加密密钥文件,这里的密码是mySecre,然后在新的CDB像上面的方法创建密钥库,再使用密码导入密钥。

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/tmp/export.p12' IDENTIFIED BY "Anbob.com";

# CREATE KEYSTORE AND ROOT LEVEL
CONN / AS SYSDBA
HOST mkdir -p /u01/app/oracle/admin/cdb2/encryption_keystore/
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb2/encryption_keystore/' IDENTIFIED BY "Anbob.com";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Anbob.com";

# PLUGIN PDB ,IMPORT KEYS
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ""Anbob.com"";
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "mySecret" FROM '/tmp/export.p12' IDENTIFIED BY ""Anbob.com"" WITH BACKUP;

EXPDP Table in encrypted Tablespace

导出或导入加密的表数据时,需要使用数据泵,增加ENCRYPTION parameter 和 ENCRYPTION_PWD_PROMPT parameter ,如ENCRYPTION_PWD_PROMPT=YES 这样在导出里就要人为交互输入密钥。导入时一样,同时要先打开密钥库。

另外TDE 有一些限制条件,如加密列类型和加密列索引和外键时需要注意,更多请查询官方文档。

— over —

打赏

, ,

对不起,这篇文章暂时关闭评论。