Database activity examples

Here we define a set of basic commands to test your newly encrypted database. The commands below will create a new dummy table and insert some data in the rows. Then we try to view the content, which is prevented until the keystore is opened via the HSM.

Only after that can the database be viewed.

CREATE TABLE FOO (ID NUMBER(5), BAR NUMBER(10));

Insert rows:

INSERT INTO FOO VALUES (001 , 10000);
INSERT INTO FOO VALUES (002 , 20000);
INSERT INTO FOO VALUES (003 , 30000);

Cipher the BAR column in the FOO table

ALTER TABLE FOO MODIFY (BAR ENCRYPT);

Select the rows in the table. TDE returns clear text

SELECT BAR FROM FOO;

List the ciphered columns in the DB instance

SELECT * FROM DBA_ENCRYPTED_COLUMNS;

Create a ciphered table space

CREATE TABLESPACE PRIMUS DATAFILE '/u01/app/oracle/oradata/orcl/PRIMUS.DBF' SIZE 150M ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Create a table in the table space

CREATE TABLE CUSTOMERS (ID NUMBER(5),NAME VARCHAR(40), BALANCE NUMBER(10)) TABLESPACE PRIMUS;

Insert values

INSERT INTO CUSTOMERS VALUES (001,'Oracle',10000);
INSERT INTO CUSTOMERS VALUES (002,'Microsoft',15000);
INSERT INTO CUSTOMERS VALUES (003,'IBM',20000);

Select table content

SELECT * FROM CUSTOMERS;

Close the key store

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY password;

Try to run a select, you will get an error

SELECT * FROM CUSTOMERS;