Breadcrumbs

Create an Encrypted Tablespace

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

›_ sqlplus console

 
SQL> create tablespace SECURE_TS DATAFILE '/u01/app/oracle/admin/utimacodb/SECURETS_01.DBF' SIZE 10M ENCRYPTION USING 'AES256' ENCRYPT;

  1. Create EMP table inside the SECURE_TS tablespace.

›_ sqlplus console

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

  1. Insert data into EMP table.

›_ sqlplus console 

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 EMP table.

›_ sqlplus console 

SQL> select * from EMP;

  1. Close the keystore.

›_ sqlplus console 

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

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

›_ sqlplus console 

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 EMP table.

  1. Open the Keystore.

›_ sqlplus console 

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

  1. Now view the data from EMP table.

›_ sqlplus console 

SQL> select * from EMP;

  1. List all the Key IDs.

›_ sqlplus console 

SQL> select KEY_ID from V$ENCRYPTION_KEYS;