Transparent Data Encryption :-
- TDE is an encryption mechanism present in Oracle database used to encrypt the data stored in a table column or tablespace. It protects the data stored on database files (DBF) by doing an encryption in case the file is stolen or hacked.
- Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.
- When using transparent encryption, the Oracle encryption wallet must be created and the wallet should be opened every time the database starts.
TDE supports two levels of encryption
-
Columns Level Encryption: Encrypt the table column data.
- Tablespace Level Encryption: Encrypt all the data in a tablespace.
Oracle database 12c introduced a new way to manage keystores, encryption keys and secrets using the ADMINISTER KEY MANAGEMENT command. This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET ENCRYPTION WALLET commands for key and wallet administration from previous releases.
Steps to create wallet and enable encryption for table column and tablespace:
- Create a wallet location :
[oracle@orcl:~ ] mkdir -p /home/oracle/wallet
- Update the wallet/keystore location in SQLNET.ORA :
A keystore must be created to hold the encryption key.Add the below entry to SQLNET.ORA,
[oracle@orcl:~ orcldemo] cat $ORACLE_HOME/network/admin/sqlnet.ora # sqlnet.ora Network Configuration File: /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = /home/oracle/wallet)))
- Create a keystore :
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/home/oracle/wallet' IDENTIFIED BY wallet$123; keystore altered. SQL> HOST ls /home/oracle/wallet ewallet.p12
- Open the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123; keystore altered.
- Check the v$encryption_keys view to see the key activated:
SQL> SELECT con_id, key_id FROM v$encryption_keys; no rows selected
- Set the encyrption key :
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY wallet$123 WITH BACKUP;
keystore altered.
WITH BACKUP creates a backup of the software keystore.It also creates a backup of the keystore before creating the new master encryption key.
Now,check the v$encryption_keys view to check the keystore is enabled.
SQL> SELECT con_id, key_id FROM v$encryption_keys; CON_ID KEY_ID ---------- ---------------------------------------------------- 0 AVRRh/0Uok8dvyvCAPMtZhwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Check keystore information from v$encryption_wallet view.
The wallet type is password-based software keystore: As names suggests, this type of keystore is protected by a password, and password is required to open the keystore to retrieve the encryption keys.
TDE Implementation in Oracle 12c database :
Table creation with encrypted wallet :
SQL> CREATE TABLE student (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30) ENCRYPT);
Table created.
SQL> INSERT INTO student values('hari',101,'MCA');
1 row created.
SQL> commit;
Commit complete.
SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='STUDENT';
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG
---------- ---------- -------------------- -----------------
HARI STUDENT DEPT AES 192 bits key
- Restart the database,
SQL> startup force ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 8628936 bytes Variable Size 603981112 bytes Database Buffers 1526726656 bytes Redo Buffers 8146944 bytes In-Memory Area 2147483648 bytes Database mounted. Database opened.
- After restart database,check the encrypted table column data,
SQL> SELECT * FROM hari.student;
SELECT * FROM hari.student;
*
ERROR at line 1:
ORA-28365: wallet is not open
- Reopen the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123; keystore altered.
Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet separately.To overcome this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.
Auto-login software keystore:
This kind of keystores are protected by system-generated password, and does not need to opened explicitly because these keystores open automatically.
- Enable AUTOLOGIN wallet type :
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR FULLY_BAC CON_ID
-------- ------------- ------ ---------- ---------- --------- ------
FILE /home/oracle/wallet/ OPEN PASSWORD SINGLE NO 0
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/home/oracle/wallet' IDENTIFIED BY wallet$123; keystore altered.
SQL> HOST ls /home/oracle/wallet
cwallet.sso ewallet.p12 ewallet_2018111618242920.p12 ewallet_2018111618410185.p12
As soon as we execute above statement, we will see cwallet.sso file gets created under keystore location directory. Once we have AUTOLOGIN keystore, there is no need to open keystore for individual pluggable databases because auto-login keystore would open automatically for all pluggable databases as well.
- Restart the database,
SQL> startup force ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 8628936 bytes Variable Size 603981112 bytes Database Buffers 1526726656 bytes Redo Buffers 8146944 bytes In-Memory Area 2147483648 bytes Database mounted. Database opened.
- After restarting database,check the wallet type:
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR FULLY_BAC CON_ID
-------- ------------- ------ ---------- ---------- --------- ------
FILE /home/oracle/wallet/ OPEN AUTOLOGIN SINGLE NO 0
- Now,the encrypted table data and column name ‘DEPT’,
SQL> SELECT * FROM hari.student;
NAME ROLLNO DEPT
----- ------- -----
hari 101 MCA
- Create Encrypted Tablespace :
SQL> CREATE TABLESPACE encrypt_ts DATAFILE '/oradb/app/oracle/oradata/ORCLDEMO/datafile/encrypt_ts.dbf' SIZE 2G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLE student_demo (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30)) tablespace encrypt_ts ;
Table created.
SQL> INSERT INTO student_demo values('hari',101,'MCA');
1 row created.
SQL> commit;
Commit complete.
SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='ENCRYPT_TS';
TABLESPACE_NAME ENC
--------------- ---
ENCRYPT_TS YES
- Restart the database,
SQL> startup force ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 8628936 bytes Variable Size 603981112 bytes Database Buffers 1526726656 bytes Redo Buffers 8146944 bytes In-Memory Area 2147483648 bytes Database mounted. Database opened.
- Check the table data present in tablespace ‘ENCRYPT_TS’
SQL> SELECT * FROM hari.student_demo; NAME ROLLNO DEPT ----- ------- ---- hari 101 MCA
Local auto-login software keystores:
This type of keystores have auto-login functionality on the computer where these are created and these cannot be opened from any other computer.
- To create a local auto-login keystore, use following syntax
SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE 'wallet location' IDENTIFIED BY password;
But for creation of LOCAL AUTO_LOGIN keystore,the wallet type before has to be PASSWORD ( password-based keystore).
- Close the Password based software keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY wallet$123;
keystore altered.
This above option can be enable when the wallet_type is PASSWORD.
To close a password-based software keystore or a hardware keystore, specify the Keystore password.
- Close the Autologin based software keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE; keystore altered.
To close an auto-login keystore, do not specify Keystore password.Before you close an auto-login keystore, check the WALLET_TYPE column of the v$encryption_wallet view. If it returns AUTOLOGIN, then you can close the keystore. Otherwise, if you attempt to close the keystore, then an error occurs.
Catch Me On:- Hariprasath Rajaram LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page :https://www.facebook.com/dbahariprasath/? Twitter :https://twitter.com/hariprasathdba
