在Oracle 11g 数据库上设置透明数据加密（TDE）
本文回答2个问题：
- 11g下简明的TDE设置过程
- 由于11g不支持在线TDE，介绍2中11g下的加密表空间的迁移方法
设置表空间TDE之前
表空间没有加密时，很容易探测到明文数据：
create tablespace unsectbs datafile 'unsectbs.dbf' size 10m autoextend on next 10m maxsize unlimited;
create table unsectbl tablespace unsectbs as select * from dba_users;
create unique index idx1 on unsectbl(user_id);
SQL> select TABLESPACE_NAME from user_tables where table_name = 'UNSECTBL';
TABLESPACE_NAME
------------------------------
UNSECTBS
SQL> !strings /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/unsectbs.dbf
}|{z
h7/dORCL
UNSECTBS
SPATIAL_WFS_ADMIN_USR
EXPIRED & LOCKED
USERS
TEMP
DEFAULT
DEFAULT_CONSUMER_GROUP
10G 11G
PASSWORD,
SPATIAL_CSW_ADMIN_USR
EXPIRED & LOCKED
USERS
TEMP
...
设置表空间TDE
compatibility 需设为11.2或以上：
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
通过tnsping，可以得到sqlnet.ora的路径：
$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-NOV-2023 12:39:17
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = instance-20231116-1239-db11g)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
$ vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
在sqlnet.ora中追加以下语句，指定wallet的位置。
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/app/oracle/wallet)))
执行以下命令，创建加密的wallet，以及master key：
-- 目录必须存在，否则报错ORA-28368
SQL> !mkdir /home/oracle/app/oracle/wallet
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Easy2rem";
System altered.
SQL> !ls /home/oracle/app/oracle/wallet
ewallet.p12
创建加密表空间：
alter system set db_create_file_dest='/home/oracle/app/oracle/oradata/orcl';
CREATE TABLESPACE sectbs
DATAFILE 'secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);
确认已加密：
SQL> select TABLESPACE_NAME, ENCRYPTED from user_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
SECTBS YES
6 rows selected.
SQL> create table sectbl tablespace sectbs as select * from dba_users;
Table created.
设置表空间TDE之后
SQL> !strings /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/secure01.dbf
}|{z
h7/dORCL
SECTBS
Zdp!
2VN?
9&*.
2vq
[f9k
z=G=
23WV-
@Y6w
/2.-
m:Wp.
Z-]
D''8
$gU%
? 5T
...
加密已有的表空间
11g不支持加密已有的表空间，只支持新建。
但我们可以将未加密表空间中的表迁移到加密的表空间中：
SQL> alter table unsectbl move tablespace sectbs;
Table altered.
SQL> select TABLESPACE_NAME from user_tables where table_name = 'UNSECTBL';
TABLESPACE_NAME
------------------------------
SECTBS
注意，表的索引会变为无效，因此需要rebuild。详见这里。
SQL> select status from user_indexes where index_name = 'IDX1';
STATUS
--------
UNUSABLE
SQL> alter index idx1 rebuild;
Index altered.
SQL> select status from user_indexes where index_name = 'IDX1';
STATUS
--------
VALID
另一种迁移方式是通过数据泵导出再导入。
表的导出：
$ expdp test/Welcome1@orcl tables=unsectbl
Export: Release 11.2.0.4.0 - Production on Mon Nov 20 05:30:50 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/********@orcl tables=unsectbl
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST"."UNSECTBL" 14.32 KB 31 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/oracle/admin/orcl/dpdump/expdat.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 20 05:30:54 2023 elapsed 0 00:00:04
表的导入（原表已先行删除）：
$ impdp test/Welcome1@orcl remap_table=unsectbl:sectbl remap_tablespace=unsectbs:sectbs dumpfile=expdat.dmp
Import: Release 11.2.0.4.0 - Production on Mon Nov 20 05:54:24 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/********@orcl remap_table=unsectbl:sectbl remap_tablespace=unsectbs:sectbs dumpfile=expdat.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."SECTBL" 14.32 KB 31 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 20 05:54:24 2023 elapsed 0 00:00:00
这个实验说明了2点问题：
- expdp 表的导出也会导出索引
- 索引的名字
参考
- Oracle 11G Tutorial | Transparent Data Encryption | InfiniteSkills Training
- Back to basics with Transparent Data Encryption (TDE)
- Objective: Create an encrypted Oracle 11g tablespace with Transparent Data Encryption (TDE)
- Tablespace Encryption in Oracle 11g Database Release 1
- Transparent Data Encryption in Oracle 11g
- 8.2.5 Encrypting Entire Tablespaces
- 8.4 Example: Getting Started with TDE Column Encryption and TDE Tablespace Encryption
- TDE Tablespace Encryption
- 8.3.6 Using Transparent Data Encryption in a Multi-Database Environment