由于最近刚好有客户需要做安全加固工作，所以有机会学习下TDE(Transparent Data Encryption)。Transparent Data Encryption透明机密技术是Oracle在10g版本之后推出的数据层加密技术。
核心概念是借助外置于数据库的加密文件，对数据表列乃至表空间(11G开始支持)等多层次进行加密。这种技术对存储在数据库中的敏感信息，提供了简单可靠的安全防护解决方案。
上图为Transparent Data Encryption的工作原理图。
参考资料来自于：
Database Advanced Security Administrator's Guide 10g Release 2 (10.2)
简单记录下测试过程：
启用TDE的先决条件：
Before transparent data encryption can be enabled, the compatibility level for the database must be 10.2. To start using transparent data encryption, the security administrator must create a wallet and set a master key.
The wallet can be the default database wallet shared with other Oracle Database components or a separate wallet specifically used by transparent data encryption. Oracle recommends that, for greater security, a separate wallet be used to store transparent data encryption master keys.
1、指定Wallet目录
使用TDE的一个重要过程就是对于主加密key的创建管理，
数据库文件外的key是存放在称作wallet的目录中。
2、创建master key文件，指定wallet密码
3、启动关闭Wallet
4、加密数据列
5、关闭wallet后查看
后续继续测试其他情况
核心概念是借助外置于数据库的加密文件，对数据表列乃至表空间(11G开始支持)等多层次进行加密。这种技术对存储在数据库中的敏感信息，提供了简单可靠的安全防护解决方案。
上图为Transparent Data Encryption的工作原理图。
参考资料来自于：
Database Advanced Security Administrator's Guide 10g Release 2 (10.2)
简单记录下测试过程：
启用TDE的先决条件：
Before transparent data encryption can be enabled, the compatibility level for the database must be 10.2. To start using transparent data encryption, the security administrator must create a wallet and set a master key.
The wallet can be the default database wallet shared with other Oracle Database components or a separate wallet specifically used by transparent data encryption. Oracle recommends that, for greater security, a separate wallet be used to store transparent data encryption master keys.
[oracle@orl5 ~]$
[oracle@orl5 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jul 28 02:16:02 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL>复制
1、指定Wallet目录
使用TDE的一个重要过程就是对于主加密key的创建管理，
数据库文件外的key是存放在称作wallet的目录中。
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/u01/app/admin/HCN/wallet)))复制
2、创建master key文件，指定wallet密码
SQL>
SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "hcn123456";
System altered.
SQL>复制
3、启动关闭Wallet
SQL>
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
System altered.
SQL>
SQL>
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "hcn123456";
System altered.
SQL>
SQL>
[oracle@orl5 wallet]$
[oracle@orl5 wallet]$ pwd
/u01/app/admin/HCN/wallet
[oracle@orl5 wallet]$
[oracle@orl5 wallet]$
[oracle@orl5 wallet]$ ls -lrt
total 8
-rw------- 1 oracle oinstall 1309 Jul 28 02:23 ewallet.p12
[oracle@orl5 wallet]$
[oracle@orl5 wallet]$
---生成了ewallet.p12（Personal Information Exchange类型的文件）复制
4、加密数据列
---创建测试用户
SQL>
SQL> create user hcn identified by oracle default tablespace users;
User created.
SQL>
SQL>
SQL>
SQL> grant resource ,connect to hcn;
Grant succeeded.
SQL>
SQL>
SQL> grant dba to hcn;
Grant succeeded.
SQL>
---创建测试表
SQL>
SQL> create table tde1 as select * from dba_objects;
Table created.
SQL>
SQL> create table tde2 as select * from dba_objects;
Table created.
SQL>
SQL>
SQL> select dbms_metadata.get_ddl('TABLE','TDE2') from dual;
DBMS_METADATA.GET_DDL('TABLE','TDE2')
--------------------------------------------------------------------------------
CREATE TABLE "HCN"."TDE2"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128) ENCRYPT USING 'AES192' 'SHA-1',
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> desc tde2
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter table tde2 modify (OBJECT_NAME encrypt);
Table altered.
SQL>
SQL>
SQL>
SQL> desc tde2
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128) ENCRYPT
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL> CREATE TABLE TDE3
2 (OWNER VARCHAR2(30),
3 OBJECT_NAME VARCHAR2(128) ENCRYPT,
4 SUBOBJECT_NAME VARCHAR2(30),
5 OBJECT_ID NUMBER,
6 DATA_OBJECT_ID NUMBER,
7 OBJECT_TYPE VARCHAR2(19),
8 CREATED DATE,
9 LAST_DDL_TIME DATE,
10 TIMESTAMP VARCHAR2(19),
11 STATUS VARCHAR2(7),
12 TEMPORARY VARCHAR2(1),
13 GENERATED VARCHAR2(1),
14 SECONDARY VARCHAR2(1)
15 );
Table created.
SQL>
SQL> insert into tde3 select * from dba_objects;
50604 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL>
SQL> desc tde3
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128) ENCRYPT
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL>
SQL> set lines 200
SQL> col segment_name for a40
SQL>
SQL> select segment_name,bytes from user_segments;
SEGMENT_NAME BYTES
---------------------------------------- ----------
TDE3 8388608
TDE2 8388608
TDE1 6291456
SQL>
SQL>
SQL> select (8388608 - 6291456)/6291456 from dual;
(8388608-6291456)/6291456
-------------------------
.333333333
SQL>
---增加了33%的空间,不论是创建时指定加密列还是创建后指定加密列，大小都是一样的
SQL>
SQL>
SQL> desc tde1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
SQL>
SQL>
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HCN','TDE1');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HCN','TDE2');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HCN','TDE3');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> select table_name,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN from user_tables;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ -----------
TDE1 50602 715 0 93
TDE2 50603 1020 0 93
TDE3 50604 1000 0 93
SQL>
SQL>
SQL>复制
5、关闭wallet后查看
SQL>
SQL> set lines 200
SQL> set pages 200
SQL>
SQL>
SQL> select owner,object_name from tde1 where rownum<10;
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
9 rows selected.
SQL>
SQL> select owner,object_name from tde2 where rownum<10;
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
9 rows selected.
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
System altered.
SQL>
SQL> select owner,object_name from tde1 where rownum<10;
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
9 rows selected.
SQL>
SQL> select owner,object_name from tde2 where rownum<10;
select owner,object_name from tde2 where rownum<10
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL>
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "hcn123456";
System altered.
SQL>
SQL>
SQL> select owner,object_name from tde2 where rownum<10;
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
9 rows selected.
SQL>复制
后续继续测试其他情况
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【MySQL 30周年庆】MySQL 8.0 OCP考试限时免费！教你免费领考券
墨天轮小教习
2704次阅读
2025-04-25 18:53:11
2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
2462次阅读
2025-04-09 15:33:27
2025年4月国产数据库大事记：4个千万级中标项目诞生！2024年达梦净利3.6亿、金仓净利8006.6万……
墨天轮编辑部
1487次阅读
2025-04-30 17:39:54
【DBA坦白局】第一期：在小城市和一线城市做DBA，是“躺”还是“卷”？
墨天轮编辑部
1479次阅读
2025-04-10 14:17:22
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
1350次阅读
2025-04-21 16:58:09
数据库国产化替代深化：DBA的机遇与挑战
代晓磊
1139次阅读
2025-04-27 16:53:22
Oracle Concepts（Oracle 19c）：07 SQL
Ryan Bai
1048次阅读
2025-04-09 10:57:11
2025 DBA 薪资观察：做 DBA 还香吗？
墨天轮编辑部
967次阅读
2025-04-24 15:53:21
2025年3月国产数据库中标情况一览：TDSQL大单622万、GaussDB大单581万……
通讯员
820次阅读
2025-04-10 15:35:48
MySQL 30 周年庆！MySQL 8.4 认证免费考！这次是认真的。。。
严少安
804次阅读
2025-04-25 15:30:58
