TDE enables you to encrypt individual table columns or an entire tablespace. When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. When users select the column, the data is automatically decrypted. After the selection, the data is re-encrypted.
Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen, because it stores the encryption keys in a security module (that is, a wallet) external to the database. Protecting data from this type of theft is required for most compliance regulations. The benefit to using transparent data encryption is that it requires little coding and is quick and easy to implement.
New features of TDE (11GR2)
1. One wallet can be used for tablespace and column encryption
2. Regenerate MASTER KEY using alter system set
3. Store master key in HCM or Wallet
Before trying to encrypt tablespace or column, a wallet must be created if tablespace or column needs to be encrypted.
- For oracle to identify wallet, we need to set ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
- The default location for the wallet is $ORACLE_BASE/admin/$ORACLE_SID/wallet.
Note: As a best practice it is always good to use a separate wallet for TDE
In our example we store wallet master key in /etc/ORACLE/WALLETS/SABA (ORACLE_SID)
Since /etc is owned by "root", these directories are created by "root" when done, change ownership to "oracle:oinstall" and set the permissions to "oracle" only:
# cd /etc# mkdir –p ORACLE/WALLETS/SABA
# chown –R oracle:oinstall ORACLE
# chmod –R 700 ORACLE
Set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora to the newly created directory:
(SOURCE = (METHOD = FILE)
METHOD_DATA = (DIRECTORY = /etc/ORACLE/WALLETS/SABA)))
Set the master encryption key in the wallet.
alter system set encryption key identified by “ankrose”;
This creates a wallet at the location defined in the sqlnet.ora
Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data.
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "ankrose";
ALTER SYSTEM SET WALLET CLOSE;
After successful creation of the wallet and master key, reduce permissions on the wallet file from the initial value, determined by "umask" for the "oracle" user :
Now you could see ewallet.p12 under,
$ cd /etc/ORACLE/WALLETS/SABA
$ chmod 600 ewallet.p12
It is highly recommended to always backup the wallet at the same time when backing up your database, but do not include the wallet on the same media as the database backup. Also, backup the wallet before any manipulation of its content, whether performing a master key re-key operation, or changing the wallet password.
The encrypted wallet ("ewallet.p12") offers strong protection of the master key, by encrypting the wallet with the wallet password. Opening the wallet is a manual operation and must be performed to make the master encryption key available to the database. Optionally, the master key can be copied into an "auto-open" wallet. This can be done either using Oracle Enterprise Manager, Oracle Wallet Manager or the "orapki" utility:
$ orapki wallet create –wallet
This command creates an auto-open wallet ("cwallet.sso"). In order to significantly strengthen your security when using an auto-open wallet, a local auto-open wallet can be created, starting with Oracle Database 220.127.116.11; it does not open on any machine other than the one it was created on:
$ orapki wallet create –wallet
It is not possible use a local auto-open wallet in Oracle RAC when the wallet is to be stored centrally in ACFS.
NOTE - Do not delete the original encryption wallet. Re-keying the master key requires the original encryption wallet to be present. When the master key is re-keyed, the corresponding (local) auto-open wallet is updated automatically.
Changing the Wallet Password
Starting with Oracle Database release 18.104.22.168, the "orapki" utility has been enhanced to enable wallet password changes from the command line:
$ orapki wallet change_pwd -wallet
How do I create wallet if I have multiple database on the server
If there are multiple Oracle Databases installed on the same server, they must access their own individual TDE wallet. Sharing the same wallet between independent instances is not supported and can potentially lead to a loss of the encrypted data.
If the databases share the same ORACLE_HOME, they also share the same sqlnet.ora file in $TNS_ADMIN. In order to access their individual wallet, the DIRECTORY entry for the ENCRYPTION_WALLET_LOCATION needs to point each database to its own wallet location:
DIRECTORY = /etc/ORACLE/WALLETS/$ORACLE_SID
The names of the subdirectories under /etc/ORACLE/WALLETS/ reflect the ORACLE_SID names of the individual databases.
If the databases do not share the same ORACLE_HOME, they will also have their individual sqlnet.ora files that have to point to the individual subdirectories.
Tablespace encryption (TDE)
CREATE TABLESPACE user_tdeDATAFILE '/u01/app/oracle/oradata/SABA/user_tde01.dbf'
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING '3DES168'
DEFAULT STORAGE (ENCRYPT);
You can use the following algorithms while creating an encrypted tablespace.AES128
If you don't specify any algorithm with the encryption clause it will use AES128 as default.
SELECT tablespace_name , encryptedFROM dba_tablespaces
TABLESPACE_NAME ENC------------------------------ ------
SELECT t.name , e.encryptionalg , e.encryptedts
FROM v$tablespace t , v$encrypted_tablespaces e
WHERE t.ts# = e.ts#;
NAME ENCRYPT ENC
--------------- --------------- ----------
USER_TDE 3DES168 YES
Table column encryption
CREATE TABLE test (id NUMBER(10),card VARCHAR2(50) ENCRYPT)TABLESPACE users;
insert into test (id, card) VALUES (1, 'encrypted');
Note : There is a performance overhead associated with the encryption/decryption process
DBA_ENCRYPTED_COLUMNS views are used to display information about encrypted columns.
SET LINESIZE 100COLUMN owner FORMAT A15
COLUMN tble_name FORMAT A15
COLUMN column_name FORMAT A15
SELECT * FROM dba_encrypted_columns;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
------------ --------------------- ------------------------ --------------------------- --------
ANTONY TEST CARD AES 192 bits key YES
1 rows selected.
Hope this document help !!!