Transparent Data Encryption (TDE)

Preamble

Transparent Data Encryption (TDE) belongs to the Advanced Security Option that is available as an Option for the Oracle Database Enterprise Edition only.

In Oracle 11gR2 it is made of column encryption, available since Oracle 10gR2, with however some limitation (index range scan and foreign keys mainly) and tablespace encryption that is aiming to remove all those limitations.

Transparent Data Encryption setup

Create the wallet directory first:

[orarac@server1 ~]$ cd $ORACLE_HOME
[orarac@server1 software]$ mkdir wallet

And add in your sqlnet.ora file located at $TNS_ADMIN:

ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=$ORACLE_HOME/wallet)))

Then create the wallet:

SQL> ALTER SYSTEM SET encryption KEY IDENTIFIED BY yourpassword;
 
SYSTEM altered.
 
SQL> !ls -l
total 4
-rw-r--r-- 1 orarac dba 1573 Feb 22 17:05 ewallet.p12

When you then startup the database you need to open the wallet and specify the creation password:

SQL> ALTER SYSTEM SET encryption wallet OPEN IDENTIFIED BY yourpassword;
 
SYSTEM altered.
SQL> col WRL_PARAMETER FOR a30
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            OPEN

Remark:
On a RAC installation you need to transfer your sqlnet.ora file and your wallet to the second node.

Column encryption

For the purpose of the test create a test1 table like this:

SQL> CREATE TABLE test1 AS SELECT OBJECT_ID AS id, OBJECT_NAME AS name FROM dba_objects;
 
TABLE created.
 
SQL> DESC test1
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(128)
 
SQL> CREATE INDEX test1_idx_id ON test1(id);
 
INDEX created.
 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
> method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> ALTER TABLE test1 MODIFY (name VARCHAR2(30));
 
TABLE altered.

Encrypt a column with:

SQL> ALTER TABLE test1 MODIFY (NAME encrypt USING 'AES256' IDENTIFIED BY 'yourpassword');
 
TABLE altered.
 
SQL> DESC test1;
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(128) ENCRYPT

SQL> col WRL_PARAMETER FOR a30
SQL> SET lines 130
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            OPEN
 
SQL> ALTER SYSTEM SET encryption wallet CLOSE IDENTIFIED BY yourpassword;
 
SYSTEM altered.

Then if someone select onto the table in another session the wallet is re-opened without any command (!!):

SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            OPEN

SQL> ALTER TABLE test1 MODIFY (id encrypt);
ALTER TABLE test1 MODIFY (id encrypt)
                          *
ERROR AT line 1:
ORA-28338: COLUMN(s) cannot be both indexed AND encrypted WITH salt

Salt option:
When you encrypt the same original value the encrypted value will look the same so it could be easy for a hacker to guess many values by knowing one of them. To prevent same encrypted occurrence Oracle TDE is applying a “salt” to the encrypted data. Means in clear that encrypted value of same column value of two rows will be different. Salt option is the default and you must de-activate it for indexed columns.

SQL> ALTER TABLE test1 MODIFY (id encrypt no salt);
 
TABLE altered.
SQL> DESC test1
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER ENCRYPT
 NAME                                                                             VARCHAR2(128) ENCRYPT
 
SQL> SELECT * FROM USER_ENCRYPTED_COLUMNS;
 
TABLE_NAME                     COLUMN_NAME                    ENCRYPTION_ALG                SAL INTEGRITY_AL
------------------------------ ------------------------------ ----------------------------- --- ------------
TEST1                          ID                             AES 256 bits KEY              NO  SHA-1
TEST1                          NAME                           AES 256 bits KEY              YES SHA-1

When the wallet is not in its location accessing encrypted figures is not possible:

SQL> DESC test1
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER ENCRYPT
 NAME                                                                             VARCHAR2(30) ENCRYPT
 
SQL> ALTER TABLE test1 MODIFY (id decrypt);
 
TABLE altered.
 
SQL> DESC test1
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(30) ENCRYPT
 
SQL> ALTER SYSTEM SET encryption wallet CLOSE IDENTIFIED BY yourpassword;
 
SYSTEM altered.
 
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            CLOSED
 
SQL> !
[orarac@server1 wallet]$ ll
total 4
-rw-r--r-- 1 orarac dba 1837 Feb 22 18:22 ewallet.p12
[orarac@server1 wallet]$ mv ewallet.p12 /tmp
[orarac@server1 wallet]$ EXIT
EXIT
 
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            CLOSED
 
SQL> DESC test1
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(30) ENCRYPT
 
SQL> SELECT * FROM test1 WHERE rownum<=10;
SELECT * FROM test1 WHERE rownum<=10
              *
ERROR AT line 1:
ORA-28365: wallet IS NOT OPEN
 
 
SQL> SELECT id  FROM test1 WHERE rownum<=10;
 
        ID
----------
        20
        28
        29
         3
        41
        54
        26
        13
         9
        51
 
10 ROWS selected.
 
SQL> SELECT name FROM test1 WHERE rownum<=10;
SELECT name FROM test1 WHERE rownum<=10
                 *
ERROR AT line 1:
ORA-28365: wallet IS NOT OPEN

SQL> desc test1 Name Null? Type ----------------------------------------------------------------------- -------- ------------------------------------------------ ID NUMBER ENCRYPT NAME VARCHAR2(30) ENCRYPTSQL> alter table test1 modify (id decrypt);Table altered.SQL> desc test1 Name Null? Type ----------------------------------------------------------------------- -------- ------------------------------------------------ ID NUMBER NAME VARCHAR2(30) ENCRYPTSQL> ALTER SYSTEM SET encryption wallet CLOSE IDENTIFIED BY yourpassword;System altered.SQL> SELECT * FROM V$ENCRYPTION_WALLET;WRL_TYPE WRL_PARAMETER STATUS -------------------- ------------------------------ ------------------ file $ORACLE_HOME/wallet CLOSEDSQL> ! [orarac@server1 wallet]$ ll total 4 -rw-r--r-- 1 orarac dba 1837 Feb 22 18:22 ewallet.p12 [orarac@server1 wallet]$ mv ewallet.p12 /tmp [orarac@server1 wallet]$ exit exitSQL> SELECT * FROM V$ENCRYPTION_WALLET;WRL_TYPE WRL_PARAMETER STATUS -------------------- ------------------------------ ------------------ file $ORACLE_HOME/wallet CLOSEDSQL> desc test1 Name Null? Type ----------------------------------------------------------------------- -------- ------------------------------------------------ ID NUMBER NAME VARCHAR2(30) ENCRYPTSQL> select * from test1 where rownum<=10; select * from test1 where rownum<=10 * ERROR at line 1: ORA-28365: wallet is not openSQL> select id from test1 where rownum<=10;ID ---------- 20 28 29 3 41 54 26 13 9 5110 rows selected.SQL> select name from test1 where rownum<=10; select name from test1 where rownum<=10 * ERROR at line 1: ORA-28365: wallet is not open

Trying to recreate it, even with the same password, is not possible, would be really too easy:

SQL> ALTER SYSTEM SET encryption KEY IDENTIFIED BY yourpassword;
ALTER SYSTEM SET encryption KEY IDENTIFIED BY yourpassword
*
ERROR AT line 1:
ORA-28396: rekey OF enc$ dictionary TABLE failed
 
 
SQL> !ls
ewallet.p12
 
SQL> SELECT * FROM test1 WHERE rownum<=10;
SELECT * FROM test1 WHERE rownum<=10
              *
ERROR AT line 1:
ORA-28362: master KEY NOT found
 
 
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            OPEN
 
SQL> ALTER SYSTEM SET encryption wallet CLOSE IDENTIFIED BY yourpassword;
 
SYSTEM altered.
 
SQL> !
[orarac@server1 wallet]$ ll
total 4
-rw-r--r-- 1 orarac dba 1309 Feb 23 11:34 ewallet.p12
[orarac@server1 wallet]$ mv /tmp/ewallet.p12 .
[orarac@server1 wallet]$ EXIT
EXIT
 
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
 
WRL_TYPE             WRL_PARAMETER                  STATUS
-------------------- ------------------------------ ------------------
FILE                 $ORACLE_HOME/wallet            CLOSED
 
SQL> SELECT * FROM test1 WHERE rownum<=10;
 
        ID NAME
---------- ------------------------------
        20 ICOL$
        28 CON$
        29 C_COBJ#
         3 I_OBJ#
        41 I_IND1
        54 I_CDEF2
        26 I_PROXY_ROLE_DATA$_1
        13 UET$
         9 I_FILE#_BLOCK#
        51 I_CON1
 
10 ROWS selected.

Column encryption limitations

Index range scan

There are quite a lot of posts on this index range scan limitation and in real life it is quite difficult to see it. For this create first an index on NAME column:

SQL> CREATE INDEX test1_idx_name ON test1(name);
 
INDEX created.
 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
> method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);
 
PL/SQL PROCEDURE successfully completed.

With ID column:

SQL> DESC test1
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER
 NAME                                                                             VARCHAR2(30)
 
SQL> SET autotrace traceonly EXPLAIN STATISTICS
SQL> SELECT COUNT(*) FROM test1 WHERE id=254;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1440861145
 
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST1_IDX_ID |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=254)
 
 
STATISTICS
----------------------------------------------------------
         36  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo SIZE
        422  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> ALTER TABLE test1 MODIFY(id encrypt no salt);
 
TABLE altered.
 
SQL> SELECT COUNT(*) FROM test1 WHERE id=254;
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1440861145
 
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST1_IDX_ID |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=254)
 
 
STATISTICS
----------------------------------------------------------
        210  recursive calls
          0  db block gets
         40  consistent gets
          0  physical reads
        224  redo SIZE
        422  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          6  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

SQL> desc test1 Name Null? Type ----------------------------------------------------------------------- -------- ------------------------------------------------ ID NUMBER NAME VARCHAR2(30)SQL> set autotrace traceonly EXPLAIN STATISTICS SQL> select count(*) from test1 where id=254;Execution Plan ---------------------------------------------------------- Plan hash value: 1440861145---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| TEST1_IDX_ID | 1 | 5 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("ID"=254)Statistics ---------------------------------------------------------- 36 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> alter table test1 modify(id encrypt no salt);Table altered.SQL> select count(*) from test1 where id=254;Execution Plan ---------------------------------------------------------- Plan hash value: 1440861145---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| TEST1_IDX_ID | 1 | 5 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("ID"=254)Statistics ---------------------------------------------------------- 210 recursive calls 0 db block gets 40 consistent gets 0 physical reads 224 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed

With a simple equal the index is used even when encrypted.

With NAME column:

SQL> DESC test1;
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER ENCRYPT
 NAME                                                                             VARCHAR2(30)
 
SQL> SELECT COUNT(*) FROM test1 WHERE name LIKE 'A%';
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1651843466
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | ROWS  | Bytes | COST (%CPU)| TIME     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    25 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |    25 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST1_IDX_NAME |  1529 | 38225 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("NAME" LIKE 'A%')
       filter("NAME" LIKE 'A%')
 
 
STATISTICS
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo SIZE
        423  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> ALTER TABLE test1 MODIFY(name encrypt no salt);
 
TABLE altered.
 
SQL> SELECT COUNT(*) FROM test1 WHERE name LIKE 'A%';
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 3896847026
 
----------------------------------------------------------------------------
| Id  | Operation          | Name  | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    25 |   379   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |       |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST1 |  3627 | 90675 |   379   (1)| 00:00:05 |
----------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - filter(INTERNAL_FUNCTION("NAME") LIKE 'A%')
 
 
STATISTICS
----------------------------------------------------------
        210  recursive calls
          0  db block gets
       2276  consistent gets
         92  physical reads
      65204  redo SIZE
        423  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          6  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

SQL> desc test1; Name Null? Type ----------------------------------------------------------------------- -------- ------------------------------------------------ ID NUMBER ENCRYPT NAME VARCHAR2(30)SQL> select count(*) from test1 where name like 'A%';Execution Plan ---------------------------------------------------------- Plan hash value: 1651843466------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 25 | 10 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 25 | | | |* 2 | INDEX RANGE SCAN| TEST1_IDX_NAME | 1529 | 38225 | 10 (0)| 00:00:01 | ------------------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - access("NAME" LIKE 'A%') filter("NAME" LIKE 'A%')Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 423 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> alter table test1 modify(name encrypt no salt);Table altered.SQL> select count(*) from test1 where name like 'A%';Execution Plan ---------------------------------------------------------- Plan hash value: 3896847026---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 379 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | 25 | | | |* 2 | TABLE ACCESS FULL| TEST1 | 3627 | 90675 | 379 (1)| 00:00:05 | ----------------------------------------------------------------------------Predicate Information (identified by operation id): ---------------------------------------------------2 - filter(INTERNAL_FUNCTION("NAME") LIKE 'A%')Statistics ---------------------------------------------------------- 210 recursive calls 0 db block gets 2276 consistent gets 92 physical reads 65204 redo size 423 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed

Here it is, with a LIKE and a VARCHAR2 column we see the range scan limitation.

Foreign key limitation

Encrypted foreign keys limitation:

SQL> DESC test1;
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER ENCRYPT
 NAME                                               VARCHAR2(30) ENCRYPT
 
SQL> DROP INDEX test1_idx_id;
 
INDEX dropped.
 
SQL> ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id);
 
TABLE altered.
 
SQL> CREATE TABLE test2(id NUMBER,
  2  id_fk NUMBER references test1(id));
id_fk NUMBER references test1(id))
                              *
ERROR AT line 2:
ORA-28335: referenced OR referencing FK CONSTRAINT COLUMN cannot be encrypted

Tablespace encryption

SQL> CREATE TABLESPACE encrypt datafile SIZE 10m
  2  EXTENT MANAGEMENT LOCAL
  3  SEGMENT SPACE MANAGEMENT  AUTO
  4  encryption USING 'AES256'
  5  DEFAULT STORAGE (ENCRYPT);
 
TABLESPACE created.

SQL> CREATE TABLESPACE encrypt
  2  datafile '/ora_oid/data01/oid/encrypt01.dbf' SIZE 10m
  3  EXTENT MANAGEMENT LOCAL
  4  SEGMENT SPACE MANAGEMENT  AUTO
  5  encryption USING 'AES256'
  6  DEFAULT STORAGE (ENCRYPT);
 
TABLESPACE created.
 
SQL> DROP TABLE test1;
 
TABLE dropped.
 
SQL> CREATE TABLE test1 TABLESPACE encrypt AS SELECT OBJECT_ID AS id, OBJECT_NAME AS name FROM dba_objects;
 
TABLE created.
 
SQL> ALTER TABLE test1 MODIFY (name VARCHAR2(30));
 
TABLE altered.
 
SQL> CREATE INDEX test1_idx_id ON test1(id);
 
INDEX created.
 
SQL> CREATE INDEX test1_idx_name ON test1(name);
 
INDEX created.
 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
> method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> CREATE TABLE test2 TABLESPACE users AS SELECT OBJECT_ID AS id, OBJECT_NAME AS name FROM dba_objects;
 
TABLE created.
 
SQL> ALTER TABLE test2 MODIFY (name VARCHAR2(30));
 
TABLE altered.
 
SQL> SELECT * FROM test1 WHERE rownum<=10;
 
        ID NAME
---------- ------------------------------
        20 ICOL$
        46 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        41 I_IND1
        54 I_CDEF2
        40 I_OBJ5
 
10 ROWS selected.
 
SQL> INSERT INTO test1 VALUES(9999,'Yannick');
 
1 ROW created.
 
SQL> INSERT INTO test2 VALUES(9999,'Yannick');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> ALTER SYSTEM checkpoint;
 
SYSTEM altered.

[root@server1 oid]# strings users01.dbf | grep "Yannick"
Yannick
Yannick
[root@server1 oid]# strings encrypt01.dbf | grep "Yannick"
[root@server1 oid]#

SQL> SET autotrace traceonly EXPLAIN STATISTICS
SQL> SELECT COUNT(*) FROM test1 WHERE name LIKE 'A%';
 
 
Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1651843466
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | ROWS  | Bytes | COST (%CPU)| TIME     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |    19 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST1_IDX_NAME |   328 |  6232 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("NAME" LIKE 'A%')
       filter("NAME" LIKE 'A%')
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo SIZE
        423  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

References

How I resolved ORA-28362: master key not found Error in Setup 2Node RAC for TDE [ID 432869.1]
Fails To Open / Create The Wallet: ORA-28353 [ID 395252.1]
10g R2 New Feature TDE : Transparent Data Encryption [ID 317311.1]
Best Practices for having indexes on encrypted columns using TDE in 10gR2 [ID 454980.1]
Using Transparent Database Encryption in Oracle Database 11g
Transparent Data Encryption
Encrypting Tablespaces
Transparent Data Encryption (TDE) Frequently Asked Questions

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

IT World