Oracle Transparent Data Encryption

Oracle Transparent Data Encryption Installation

Oracle TDE is available by default in Oracle RDBMS Enteprise Edition. To be able to use this it is necessary to purchase an Oracle Advanced Security license.
Oracle Transparent Data Encryption Configuration and Administration

To start using TDE the following operations have to be performed:

1) Make sure that the wallet location exists. If a non default wallet location must be used then specify it in the sqlnet.ora file :

ENCRYPTION_WALLET_LOCATION =
   (SOURCE = (METHOD = FILE)
     (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

Note: The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If you want to let Oracle manage a wallet in the default location then there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.

2) Generate a master key:
alter system set encryption key identified by "wallet_password";

This command will do the following:

A) If there is no wallet currently in the wallet location then a new wallet with the password "wallet_password" will be generated. The password is enclosed in double quotes to preserve the case of the characters. If the double quotes are not used then the characters of the password will be all in upper case. This command will also cause the new wallet to be opened and ready for use.

Comment: This is the only valid way of creating a new wallet for TDE, you may not use oracle wallet manager or command line tool orapki to create the TDE wallet for the first time!

B) A new master key will be generated and will be written to the wallet. This newly generated master key will become the active master key. The old master keys (if there were any) will still be kept in the wallet but they will not be active. They are kept there to be used when decrypting data that was previously encrypted using them.


To see the status of an wallet run the following query:
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/ORCL/wallet
OPEN


3) Enable encryption for a column or for an entire tablespace:

3.1) Create a table by specifying the encrypt option:

create table test(col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT);

SET LINESIZE 100
COLUMN owner FORMAT A15
COLUMN tble_name FORMAT A15
COLUMN column_name FORMAT A15

SELECT * FROM dba_encrypted_columns;


3.2) Encrypt the column(s) of an existing table:

alter table test modify( col2 encrypt SALT);

3.3) Create an encrypted tablespace :

CREATE TABLESPACE encryptedtbs
DATAFILE '/u01/app/oracle/oradata/ORCL/encryptedtbs01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

SELECT tablespace_name, encrypted FROM dba_tablespaces;

Note: An existing  non encrypted tablespace cannot be encrypted. If you must encrypt the data from an entire tablespace then create a new encrypted tablespace and then move the data from the old tablespace to the new one

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data.

    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";
    ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
   
 Database Startup

The following text shows the impact of stopping and starting the database on an encrypted tablespace. Notice how the wallet must be opened before the data is accessible.

    SQL>CONN / AS SYSDBA
    Connected.
    SQL> SHUTDOWN IMMEDIATE;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP
    ORACLE instance started.

    Total System Global Area  626327552 bytes
    Fixed Size                  2255832 bytes
    Variable Size             234882088 bytes
    Database Buffers          381681664 bytes
    Redo Buffers                7507968 bytes
    Database mounted.
    Database opened.
    SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

    TABLESPACE_NAME                ENC STATUS
    ------------------------------ --- ---------
    SYSTEM                         NO  ONLINE
    SYSAUX                         NO  ONLINE
    UNDOTBS1                       NO  ONLINE
    TEMP                           NO  ONLINE
    USERS                          NO  ONLINE
    EXAMPLE                        NO  ONLINE
    SOE                            NO  ONLINE
    ENCRYPTED_TS                   YES ONLINE

    8 rows selected.

    SQL> SELECT * FROM test.ets_test;
    select * from test.ets_test
                       *
    ERROR at line 1:
    ORA-28365: wallet is not open

    SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

    System altered.

    sys@db11g> select * from test.ets_test;

            ID DATA
    ---------- --------------------------------------------------
             1 This is a secret!

    1 row selected.

    SQL>