• TDE is an automatic mechanism encryption
of sensitive information. There is no need to change application
logic. It encrypts data and index values on the disk.
• It uses an opened Oracle Wallet to generate a
master key for the entire database.
• Column length changes on disk.
• Actual lengths not reported by DUMP or VSIZE.
Setting up TDE
1. Create the Wallet file:
add the following to the sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
(SOURCE=(METHOD=file)
(METHOD_DATA=(DIRECTORY=C:\oracle\OraDb10g\admin\ora10g\wall
et)))
Note: Make sure the indicated folder exists.
Note: Alternatively, you can use Oracle
Wallet Manager.
Caution: Wallet file must be included in your
backup.
2. Set the master key
This is done only once:
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY
<password>;
3. Create tables that contain encrypted columns
CREATE TABLE emp (
first_name VARCHAR2(128),
...
empID NUMBER ENCRYPT NO SALT,
salary NUMBER(6) ENCRYPT USING '3DES168',
comm NUMBER(6) ENCRYPT);
ALTER TABLE EMP MODIFY ( SAL ENCRYPT NO SALT )
Note: possible algorithms are AES128,
(AES192), AES256, or 3DES168
Note: the salt increases the
protection but prevents indexing on the column.
Existing Tables and TDE
Add encrypted columns:
ALTER TABLE emp ADD (ssn VARCHAR2(11) ENCRYPT);
Encrypt unencrypted columns:
ALTER TABLE emp
MODIFY (first_name ENCRYPT);
Disable column encryption:
ALTER TABLE emp
MODIFY (first_name DECRYPT);
Add or remove salt:
ALTER TABLE emp
MODIFY (first_name ENCRYPT [NO]
SALT);
Change keys and the encryption algorithm:
ALTER TABLE emp
REKEY USING '3DES168';
To Test TDE
SELECT
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO
(ROWID,USER,'EMP'),
DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) FROM EMP;
File Nu Block Number
------- ------------
4 63
ALTER SESSION SET EVENTS '10389 trace name
context forever, level 1';
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 63;
Opening and Closing the Wallet
The Wallet must be opened after instance restart.
ALTER SYSTEM SET
ENCRYPTION WALLET OPEN
IDENTIFIED BY password>
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
TDE and Data Pump Export and Import
Use your own provided column key during export and
import:
expdp hr/hrpswd
directory=dir_dp tables=emp
ENCRYPTION_PASSWORD=testme
impdp hr/hrpswd directory=dir_dp
ENCRYPTION_PASSWORD=testme
table_exists_action=replace tables=emp
RMAN Encrypted Backups
Three possible encryption modes for your backups:
• Transparent mode: It requires Oracle Wallet. It is
best suited for day-to-day backup and restore operations at the
same location. It is the default encryption mode.
CONFIGURE ENCRYPTION FOR DATABASE ON
• Password mode: It requires you to provide a
password. It is best suited for backups restored at remote
locations.
SET ENCRYPTION ON IDENTIFIED BY password ONLY
• Dual mode: It can use either Oracle Wallets or
passwords.
After making sure the wallet is open, SET ENCRYPTION
ON IDENTIFIED BY password If there is no wallet or the wallet is
closed:
SET DECRYPTION IDENTIFIED BY password1 {,
password2,…, passwordn}
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
