Configuring the Oracle Server Database Encryption with TDE Key

This section explains how to configure the Oracle Server database encryption using the TDE key.

Column Encryption

Create a 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 encrypted columns.
Copy
```
SELECT * FROM DBA_ENCRYPTED_COLUMNS;
```

Verification

To verify the column 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/orcl.conf" CONTAINER = ALL;

After closing the wallet, data retrieval should fail.

Copy

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

Data retrieval works again after opening the key store.

Copy

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

Tablespace Encryption

Create encrypted tablespace.

Copy

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

Create a table in encrypted tablespace.

Copy

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

Insert data.
Copy
```
SELECT * FROM EMPLOYEE;
```

Verification: Verifying tablespace encryption is the same as for Column Encryption. See Verification.