Breadcrumbs

Create Software Keystore

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

  1. Create a wallet directory located in the $ORACLE_BASE/admin/db_unique_name directory e.g., wallet.

  2. Log in to the database instance as a user who has been granted the SYSDBA administrative privilege.

›_ sqlplus console 

SQL> connect / as sysdba

  1. Set WALLET_ROOT parameter.

›_ sqlplus console 

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

  1. Shut down and start up database.

›_ sqlplus console 

SQL> shutdown immediate; 

SQL> startup;

  1. Set TDE_CONFIGURATION parameter.

›_ sqlplus console 

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 that you want to use.

›_ sqlplus console 

SQL> grant ADMINISTER KEY MANAGEMENT to system; 

SQL> commit;

  1. Connect to the database as system user.

›_ sqlplus console 

SQL> connect system/<password>

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

›_ sqlplus console

 
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.

›_ sqlplus console

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

  1. Set the master encryption key in the software keystore.

›_ sqlplus console 

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

  1. Create a SCIENTISTS table into the DB.

›_ sqlplus console 

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

  1. Enter data into the SCIENTISTS table.

›_ sqlplus console 

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 inserted data into SCIENTISTS table.

›_ sqlplus console 

SQL> select * from SCIENTISTS;

  1. Encrypt the 'Salary' column from SCIENTISTS.

›_ sqlplus console 

SQL> alter table SCIENTISTS modify (Salary ENCRYPT);

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

›_ sqlplus console 

SQL> select salary from SCIENTISTS;

  1. Verify the column is encrypted in your DB.

›_ sqlplus console 

SQL> select * from DBA_ENCRYPTED_COLUMNS;

  1. View the information of software keystore.

›_ sqlplus console 

SQL> select * from V$ENCRYPTION_WALLET;

  1. Create an encrypted tablespace.

›_ sqlplus console 

SQL> create tablespace SECURETS DATAFILE '/u01/app/oracle/oradata/utimacodb/SECURETDB_01.DBF' SIZE 10M ENCRYPTION USING 'AES256' ENCRYPT;

  1. Create EMP table inside the SECURETS tablespace.

›_ sqlplus console 

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

  1. Insert data into the 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 software keystore.

›_ sqlplus console 

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