Breadcrumbs

Creating an Encrypted Tablespace

  1. To create an encrypted tablespace, you must use the CREATE TABLESPACE statement with the ENCRYPTION USING clause.

SQL
SQL> create tablespace SECURE_TS DATAFILE 'C:/Oraclenew/oradata/ORCLNEW/SECURETS_01.DBF' SIZE 10M ENCRYPTION USING 'AES256' ENCRYPT;

  1. Create an EMP table inside the SECURE_TS tablespace.

SQL
SQL> create table EMP (EMPID NUMBER(4),NAME VARCHAR(100),SALARY NUMBER(6)) tablespace SECURE_TS;

  1. Add data to the EMP table.

SQL
SQL> insert into EMP values (0001, 'Michael Jackson', 999999); 
SQL> insert into EMP values (0002, 'Lady Gaga', 888888); 
SQL> insert into EMP values (0003, 'Freddie Mercury', 777777); 
SQL> insert into EMP values (0004, 'Steven Tyler', 666666); 
SQL> commit;

  1. View the data from the EMP table.

SQL
SQL> select * from EMP;

  1. Close the keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_password>;

  1. Now, try to view the contents of the EMP table.

SQL
SQL> select * from EMP;

As the keystore is closed, you will get an error message "ORA-28365: wallet is not open”, and hence you cannot view the data from the EMP table

  1. Open the Keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_password>;

  1. Now view the data from the EMP table.

SQL
SQL> select * from EMP;

  1. List all the Key IDs.

SQL
SQL> select KEY_ID from V$ENCRYPTION_KEYS;