Breadcrumbs

To verify the Master Encryption Key is Encrypting the PDB

  1. Create a SCIENTISTS table in the PDB.

›_ sqlplus console 

SQL> create table SCIENTISTS (SCID NUMBER(5), Name VARCHAR(42), SALARY NUMBER(10));

  1. Enter some values in the SCIENTISTS table.

›_ sqlplus console 

SQL> insert into SCIENTISTS values (001, 'George Bailey', 10000);  SQL> insert into SCIENTISTS values (002, 'Denial Vettory', 20000); SQL> commit;

  1. Encrypt the Salary column of the SCIENTISTS table.

›_ sqlplus console 

SQL> alter table SCIENTISTS modify (Salary Encrypt);

  1. List the values in the encrypted column. Transparent Data Encryption decrypts them automatically and the values are returned in clear text.

›_ sqlplus console 

SQL> select salary from SCIENTISTS;

  1. List encrypted columns in your databases.

›_ sqlplus console 

SQL> select * from DBA_ENCRYPTED_COLUMNS;

  1. Create an encrypted tablespace.

›_ sqlplus console 

SQL> create tablespace SECURETS DATAFILE '/u01/app/oracle/oradata/utimacodb/SECURETS01.DBF' SIZE 10M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

  1. Create EMP table inside the NONSECURE_TS tablespace.

›_ sqlplus console 

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

  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;