Oracle Encryption - Encrypt data on storage with wallet

 --Specify location for wallet root

ALTER SYSTEM SET WALLET_ROOT='$ORACLE_BASE/admin/igt/wallet' SCOPE=SPFILE;

Restart Oracle is required here

SHUTDOWN IMMEDIATE;

STARTUP;

SHOW PARAMETER WALLET_ROOT

--Specify keystore type

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH;

SHOW PARAMETER TDE_CONFIGURATION

SELECT con_id, keystore_mode FROM V$ENCTYPTION_WALLET;

Auto-Login Keystore         - can be opened from a remote servers

Local Auto-Login Keystore   - can be opened from a local server

Password-Protected Keystore - can be opened only after providing a password

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY Abcd@2026;

This will create an ewallet.p12 file under $ORACLE_BASE/admin/igt/wallet/TDE

SELECT wrl_type, wrl_parameter, status, con_id
FROM V$ENCTYPTION_WALLET;

At this point, password walled is closed

Need manually to open password protected keystore.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Abcd@2026;

SELECT con_id, keystore_mode, status FROM V$ENCTYPTION_WALLET;

Status should be OPEN_NO_MASTER_KEY

Now, create Master Key

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Abcd@2026 WITH BACKUP;

SELECT masterkey_activated FROM V$DATABASE_KEY_INFO

Should be YES

Now, Alter key to be auatologin

ADMINISTER KEY MANAGEMENT CREATE auto_login keystore FROM KEYSTORE '$ORACLE_BASE/admin/igt/wallet/tde' IDENTIFIED BY Abcd@2026;

Configuration completed!

====================

How to use:

====================

====================

Encrypt Tablespace

====================

CREATE TABLESPACE IGT_ENC_TBS DATAFILE '/some/patn/to/datafile/encypted_tbs.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 10000M;

ALTER TABLESPACE IGT_ENC_TBS ENCRYPTIONONLINE ENCRYPT;

SELECT tablespace_name, status, encrypted FROM DBA_TABLESPACES;

Deafult Encryption is AES192. Salt and MAC are added by default

Salt - random string added before encryption. More strength to encryption. required additional 16 bytes for each encrypted data value. Cannot be used on indexed columns.

MAC  - Message Authentication Code. Used for Data Integrity checking. Adds additional 20 bytes for each encrypted data value.

Encrypt a column

CREATE TABLE TOKEN_USAGE 

(id          NUMBER,

 usage_name  VARCHAR2(100),

 usage_value NUMBER ENCRYPT;

);

ALTER TABLE TOKEN_USAGE ADD (CONSTRAINT TOKEN_USAGE_PK PRIMARY KEY  (id) USING INDEX TABLESPACE IGT_INDEX;

INSERT INTO TOKEN_USAGE (id, usage_name, usage_value) VALUES (1,'Usage Name',20);