You can use column level encryption and tablespace level encryption.

Column Encryption

Option 1: Encrypt plaintext table

Create a plaintext table.

Copy

CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10));

Add data to the table.

Copy

INSERT INTO CUSTOMERS VALUES (001, 'Rakesh Sharma', 10000);
INSERT INTO CUSTOMERS VALUES (002, 'Betty John', 20000);
INSERT INTO CUSTOMERS VALUES (003, 'T Ramchandran', 30000);
INSERT INTO CUSTOMERS VALUES (004, 'Amir Khan', 40000);

Encrypt a column.

Copy

ALTER TABLE CUSTOMERS MODIFY (CREDIT_LIMIT ENCRYPT);

List the encrypted columns.

Copy

SELECT * FROM DBA_ENCRYPTED_COLUMNS;

Option 2: Create a table with encrypted column

Copy

CREATE TABLE CUSTOMERS (ID NUMBER(5), NAME VARCHAR(42), CREDIT_LIMIT NUMBER(10) ENCRYPT );

Tablespace Encryption

Data encryption keys are managed by Oracle. They are created when tablespaces are encrypted. The DEKs are wrapped with the master key and the wrapped keys are stored with the data. Oracle communicates with Cryptographic Security Platform Vault for Databases to wrap and unwrap these data keys.

Option 1: Online Encryption of Tablespace

If the tablespace is already created without enabling encryption, then the encryption can be enabled later. For example: 

Copy

ALTER  TABLESPACE plaintext_tablespace encryption online using 'AES192' encrypt ;

Option 2: Create encrypted tablespace

Create an encrypted tablespace. For example using the AES256 algorithm: 

Copy

CREATE TABLESPACE SECURESPACE
DATAFILE '/u01/app/oracle/oradata/orcl/SECURE01.DBF' SIZE 150M
ENCRYPTION using 'AES256'
DEFAULT STORAGE (ENCRYPT);

Create a table in the encrypted tablespace.

Copy

CREATE TABLE EMPLOYEE (ID NUMBER(5),NAME VARCHAR(42),SALARY NUMBER(10)) TABLESPACE SECURESPACE;

Insert data.

Copy

INSERT INTO EMPLOYEE VALUES (001,'JOHN LENON',100000);
INSERT INTO EMPLOYEE VALUES (002,'MARTINA HINGIS',200000);
INSERT INTO EMPLOYEE VALUES (003,'R ATTENBOROUGH',350000);
COMMIT;

Display the data.

Copy

SELECT * FROM EMPLOYEE;

Display the tablespaces.

Copy

COLUMN tablespace_name heading "Tablespace Name" format a20
COLUMN encryption_algorithm heading "Encryption Algorithm" format a21
SELECT
t.name AS tablespace_name,
e.encryptionalg AS encryption_algorithm,
masterkeyid AS "Master Key ID"
FROM
v$tablespace t
JOIN
v$encrypted_tablespaces e
ON
t.ts# = e.ts#;

Copy

#-------- output is like this ------->>

Tablespace Name      Encryption Algorithm  Master Key ID
-------------------- --------------------- --------------------------------
SECURESPACE          AES256                17639D6AEE554F27BFB9DD2F3738E145
SECURESPACE2         AES128                17639D6AEE554F27BFB9DD2F3738E145

Verification

This verification process works the same for the column and tablespace encryption.

Retrieve the encrypted data.

Copy

SELECT CREDIT_LIMIT FROM CUSTOMERS;

Close the wallet.

Copy

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "file:/opt/oracle/entrust/oracle.conf" CONTAINER = ALL;

Retrieve the data again. It should fail because the wallet is closed.

Copy

SELECT CREDIT_LIMIT FROM CUSTOMERS;
ERROR at line 1:
ORA-28365: wallet is not open

Open the key store and retrieve the data again. It should work properly this time.

Copy

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "file:/opt/oracle/entrust/oracle.conf" CONTAINER = ALL;
SELECT CREDIT_LIMIT FROM CUSTOMERS;