使用Oracle的TDE特性加密

　2010-01-27 16:10:55　来源：WEB开发网　　　

核心提示：列表3删除剩余敏感数据的未加密虚副本需要一系列步骤才能解决很多相关性，列表 3 中的最后一步针对特定的操作系统，使用Oracle的TDE特性加密(5)，在本例中，我演示了 Linux/Unix shred 实用程序的用法，但由于该索引值存储在索引中的方式，加密值的随机性质使得加密索引的范围扫描成本过高，其他您可能想探究

列表3　删除剩余敏感数据的未加密虚副本需要一系列步骤才能解决很多相关性。　

列表 3 中的最后一步针对特定的操作系统。在本例中，我演示了 Linux/Unix shred 实用程序的用法。其他您可能想探究的实用程序是 Linux/Unix wipe、scrub 和 srm 程序。

准备可能的重新生成密钥操作

我的客户还想做些准备以应对将来可能需要重新生成密钥的情况。为现有数据重新生成密钥意味着您要使用新密钥对现有数据重新加密。当您怀疑某人已经获得对现有表密钥的访问权限并且可能会在您的控制之外破解敏感数据时，可能需要执行该操作。

完成重新生成密钥操作的步骤与最初加密现有数据的步骤类似：删除目标列上的索引，重新生成该列数据的密钥，然后重建删除的索引。或者，如果您担心与使用以前的密钥加密的数据相对应的虚副本，可以重复将段移动到新表空间、删除旧表空间、然后清除旧表空间的数据文件的过程。

注：以我的经验，PCI 审计员会对重新生成万能加密密钥非常满意，因为该过程不需要访问任何数据，并且 PCI 标准不包含对 2 层密钥体系结构（如Oracle的体系结构）的建议。从符合 PCI 的角度看，重新生成万能加密密钥应该足够了，而且 PCI 审计员无法强制具有几十亿行的公司将其业务关闭几天，仅仅只是为了重新生成密钥。

确定可能的查询计划更改

Oracle 文档、若干文章以及我阅读过的一些论坛帖子几乎都提到了有关加密列的数据后现有查询执行计划可能发生更改的概要信息以及某些具体信息。一般来说，相对于没有索引的列，在加密具有索引的列时，您必须注意执行 SQL 语句所发生的情况。当Oracle加密具有索引的列时，Oracle 还将加密相应的索引值。如果您花点时间考虑这个问题，就会清楚地发现以具有索引的数据为目标的相等谓词应该继续利用索引，但由于该索引值存储在索引中的方式，加密值的随机性质使得加密索引的范围扫描成本过高。列表 4 演示了这些详细描述的基本情况。

SQL> CONNECT app_001 Enter password: Connected. SQL> -- Create a plan table SQL> @?/rdbms/admin/utlxplan.sql; Table created. SQL> -- Disable encryption of the credit card column SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card DECRYPT); Table altered. SQL> -- Ensure schema stats are current SQL> EXEC dbms_stats.gather_schema_stats('app_001'); PL/SQL procedure successfully completed. SQL> -- Display some representative data SQL> COL credit_card FOR 9999999999999999; SQL> SELECT * FROM app_001.transactions 2 WHERE rownum < 5; TRANS_ID CREDIT_CARD ---------- ----------------- 389 3469681098409570 390 3441050723354352 391 3485598407754404 392 3485458104610650 SQL> -- Enable tracing and explain plan output SQL> SET AUTOTRACE ON EXPLAIN; SQL> -- Demonstrate an equality predicate targeting the SQL> -- encrypted column SQL> SELECT * FROM app_001.transactions 2 WHERE credit_card = 3485458104610650; TRANS_ID CREDIT_CARD ---------- ----------------- 392 3485458104610650 Execution Plan ---------------------------------------------------------- Plan hash value: 32329967 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 1 | 14 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TRANSACTIONS_NDX1 | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CREDIT_CARD"=3485458104610650) SQL> -- Demonstrate a range predicate targeting the SQL> -- encrypted column SQL> SELECT * FROM app_001.transactions 2 WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999; TRANS_ID CREDIT_CARD ---------- ----------------- 4629 3499990987277941 18597 3499993250694089 13364 3499996558049599 79326 3499996616476145 60420 3499997873591732 24392 3499998608513414 97433 3499999831086288 72183 3499999977925392 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 32329967 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 42 | 6 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 3 | 42 | 6 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TRANSACTIONS_NDX1 | 3 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CREDIT_CARD">=3499990000000000 AND "CREDIT_CARD"<=3499999999999999) SQL> -- Disable tracing and explain plan output SQL> SET AUTOTRACE OFF; SQL> -- Encrypt the column (and indexes) SQL> ALTER TABLE app_001.transactions 2 MODIFY (credit_card ENCRYPT NO SALT); Table altered. SQL> -- Ensure schema stats are current SQL> EXEC dbms_stats.gather_schema_stats('app_001'); PL/SQL procedure successfully completed. SQL> -- Enable tracing and explain plan output SQL> SET AUTOTRACE ON EXPLAIN; SQL> -- Rerun previous queries, compare execution plans SQL> SELECT * FROM app_001.transactions 2 WHERE credit_card = 3485458104610650; TRANS_ID CREDIT_CARD ---------- ----------------- 392 3485458104610650 Execution Plan ---------------------------------------------------------- Plan hash value: 32329967 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 1 | 14 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TRANSACTIONS_NDX1 | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CREDIT_CARD"=3485458104610650) SQL> SELECT * FROM app_001.transactions 2 WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999; TRANS_ID CREDIT_CARD ---------- ----------------- 60420 3499997873591732 4629 3499990987277941 18597 3499993250694089 13364 3499996558049599 24392 3499998608513414 79326 3499996616476145 72183 3499999977925392 97433 3499999831086288 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1321366336 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1250 | 17500 | 914 (2)| 00:00:11 | |* 1 | TABLE ACCESS FULL| TRANSACTIONS | 1250 | 17500 | 914 (2)| 00:00:11 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(INTERNAL_FUNCTION("CREDIT_CARD")>=3499990000000000 AND INTERNAL_FUNCTION("CREDIT_CARD")<=3499999999999999) SQL> -- Disable tracing and explain plan output SQL> SET AUTOTRACE OFF;