经验之谈：使用Oracle的TDE特性加密

核心提示： SQL> CONNECT app_001Enter password:Connected.SQL> -- Create a plan tableSQL> @?/rdbms/admin/utlxplan.sql;Table created.SQL> -- Disabl

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;