Breadcrumbs

Creating a Software Keystore

This example starts by creating a database protected by a software wallet. If you already have a database protected by a software wallet, you can skip this section.

  1. Create a wallet directory in the C:\oracle\admin\ORCLDB\WALLET directory, e.g., wallet.

  1. Log in to the database instance as a user granted the SYSDBA administrative privilege.

SQL
SQL> connect / as sysdba

  1. Set the WALLET_ROOT parameter.

SQL
SQL> alter system set wallet_root='<path to the oracle wallet directory>' scope=spfile

  1. Shut down and start up the database.

SQL
SQL> shutdown immediate; 
SQL> startup;

  1. Set the TDE_CONFIGURATION parameter.

SQL
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=both ;

  1. Grant the ADMINISTER KEY MANAGEMENT or SYSKM privilege to SYSTEM and any user you want.

SQL
SQL> grant ADMINISTER KEY MANAGEMENT to system; 
SQL> commit;

  1. Connect to the database as a system user.

SQL
SQL> connect system/<password>

  1. Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY <software_keystore_password>;

  1. Run the ADMINISTER KEY MANAGEMENT SQL statement to open the software-based keystore.

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

  1. Set the Master Encryption Key in the software keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <software_keystore_password > WITH BACKUP USING 'backupdb';

  1. Create a SCIENTISTS table in the DB.

SQL
SQL> create table SCIENTISTS (SCID NUMBER(4), FirstName VARCHAR2(128), LastName VARCHAR2(128), Salary NUMBER(6));

  1. Add data to the SCIENTISTS table.

SQL
SQL> insert into SCIENTISTS values (0001, 'Albert', 'Einstein', 850000); 
SQL> insert into SCIENTISTS values (0002, 'Isaac', 'Newton', 750000); 
SQL> insert into SCIENTISTS values (0003, 'Charles', 'Darwin', 650000); 
SQL> insert INTO SCIENTISTS values (0004, 'Curie', 'Einstein', 550000); 
SQL> commit;

  1. Verify the added data in the SCIENTISTS table.

SQL
SQL> select * from SCIENTISTS;

  1. Encrypt the 'Salary' column from SCIENTISTS.

SQL
SQL> alter table SCIENTISTS modify (Salary ENCRYPT);

  1. The Transparent Data Encryption decrypts the encrypted column automatically and returns the data in clear format.

SQL
SQL> select salary from SCIENTISTS;

  1. Verify the column is encrypted in your DB.

SQL
SQL> select * from DBA_ENCRYPTED_COLUMNS;

  1. View the information of the software keystore.

SQL
SQL> select * from V$ENCRYPTION_WALLET;

  1. Create an encrypted tablespace.

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

  1. Create the EMP table inside the SECURETS tablespace.

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

  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 software keystore.

SQL
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <software_keystore_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 < software_keystore_password >;

  1. Now, view the data from the EMP table.

SQL
SQL> select * from EMP;

  1. List all Key IDs.

SQL
SQL> select KEY_ID from V$ENCRYPTION_KEYS;