Verifying the Master Encryption Key is Encrypting the PDB

Create a SCIENTISTS table in the PDB.

SQL

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

Add values to the SCIENTISTS table.

SQL

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

Encrypt the Salary column of the SCIENTISTS table.

SQL

SQL> alter table SCIENTISTS modify (Salary Encrypt);

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

SQL

SQL> select salary from SCIENTISTS;

List encrypted columns in your databases.

SQL

SQL> select * from DBA_ENCRYPTED_COLUMNS;

Create an encrypted tablespace.

SQL

SQL> create tablespace SECURETS DATAFILE 'C:/Oraclenew/oradata/ORCLNEW/SECURETS01.DBF' SIZE 10M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create an EMP table inside the NONSECURE_TS tablespace.

SQL

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

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;

View data from the EMP table.

SQL

SQL> select * from EMP;

Close the keystore.

SQL

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

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.

Open the Keystore.

SQL

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

Now, view the data from the EMP table.

SQL

SQL> select * from EMP;

List all Key IDs.

SQL

SQL> select KEY_ID from V$ENCRYPTION_KEYS;