Transparent Data Encryption (TDE)
Oracle Transparent Data Encryption (TDE) enables you to encrypt sensitive application data on storage media, i.e. files at rest, completely transparent to the application. TDE addresses encryption regulations associated with PCI DSS and the like.
While most Oracle components (parameter, control, redo ...) have redundancy, the Oracle Wallet only has one file set per instance. If the Oracle Wallet does not open when the database mounts – the entire database will not open. Furthermore, there are numerous phenomena that can effect Wallet files.
Because of the above, the Oracle Wallet is a single point of failure for a production database. You may wish to consider this before committing to a product that requires it.
TDE column encryption enables encryption of new and existing table columns containing sensitive information. An existing non-encrypted tablespace however cannot be encrypted. If you wish to encrypt the data for an entire tablespace create a new encrypted tablespace and then move the data from the old tablespace to the new one. The advantage in encrypting an entire tablespace is that you do not need to make any changes at the table level, i.e. less administration and more scalable.
The Oracle database instance name in these examples is DB42.
To disable TDE go here.
- Create wallet directory.
Make sure that the wallet location exists and can be read/written by the Oracle processes.
Default location: %ORACLE_BASE%\admin\DB42\wallet
If required you can change it from the default by creating a sqnet.ora entry.
- Create a master key.
SQL> alter system set encryption key identified by "MyWalletPassword";
This will cause the file %ORACLE_BASE%\admin\DB42\wallet\ewallet.p12 to be created and open the new wallet.
- Check Status
You can see the wallet status using:
SQL> SELECT * FROM v$encryption_wallet;
From this point on you open the wallet before using any TDE components after each database bounce!
To open the wallet use:
SQL> alter system set wallet open identified by "<password>";
You can set the wallet to auto-login so you don't need to open the wallet manually. To do this use orapki.
OS> orapki wallet create -wallet %ORACLE_BASE%\admin\DB42\wallet -auto_login
This will create the file ewallet.sso which is used by the wallet to auto-login.
Encrypt New Tablespace
This is done using the below tablespace creation options:
- ENCRYPTION USING '<[AES128] | AES192 | AES256 | 3DES168>'
- DEFAULT STORAGE(ENCRYPT)
CREATE BIGFILE TABLESPACE tde1 DATAFILE 'O:\oradata\dbf\tde1.dbf' SIZE 100m REUSE AUTOEXTEND ON NEXT 25m MAXSIZE 10g EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT) LOGGING;
Display Encryption Status
SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE tablespace_name LIKE '%&MyString%';
Encrypt Table Column
Create a table by specifying the encrypt option.
create table test( col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT );
If the table has many rows then this operation might take some time since all the values
stored in col2 must be replaced by encrypted strings.
alter table test modify(col2 encrypt using 'AES256');
SELECT * FROM dba_encrypted_columns ORDER BY table_name, column_name
- Don't use the Wallet Manager. Query the database using one of these two ways:
- SQL> SELECT * FROM v$encryption_wallet
- OS> orapki wallet display -wallet %ORACLE_BASE%\admin\DB42\wallet
- For disaster recovery (DR) to another location you must copy the wallet files (ewallet.p12 and cwallet.sso)
to the same location as in the primary database before using RMAN.
- To change the wallet password:
- OS> orapki wallet change_pwd -wallet %ORACLE_BASE%\admin\DB42\wallet
- If you are using a wallet with auto login enabled, you must regenerate the auto login wallet after changing the password.
- OS> orapki wallet create -wallet %ORACLE_BASE%\admin\DB42\wallet -auto_login
You cannot change the wallet password in 18.104.22.168 because of a bug (ID 1302664.1). Scheduled to be fixed in 22.214.171.124.