11G OCM Create and Manage encrypted tablespaces

References

Procedure

Create System Default Location

The information in the online help for owm is WRONG : the default wallet location for the oracle user is NOT $ORACLE_HOME/admin/$ORACLE_SID , but /etc/ORACLE/WALLETS/oracle . (Notice the capitalisation! — ignore the /etc/oracle directory.)

The proper reference is 9.4.11 Saving in System Default .

Use the following commands to create the system default wallet location (if you have sudoers set up for the oracle user).

sudo mkdir -p /etc/ORACLE/WALLETS/oracle
sudo chown -R oracle:oinstall /etc/ORACLE

Create the Wallet

I used the owm GUI to create the wallet and set the auto-login attribute. If the system default location is set up correctly, you should see the following dialogue:

Make the Wallet Auto-Login

With the wallet open in owm , click the auto login entry in the Wallet menu as shown below:

Save the Wallet in the Correct Location

The correct location for the wallet is not the system default, but the following location for the example SID:

/u01/app/oracle/admin/example/wallet

This is the value displayed in the Transparent Date Encryption page of OEM.

Open the Wallet in the Database Instance

Use the following command to open the wallet by the database instance by using the wallet password:

SQL> alter system set encryption wallet open identified by "********";

System altered.

First Attempt to Encrypt Columns

I tried to encrypt the LOSAL and HISAL columns in the SCOTT.SALGRADE table:

SQL> ALTER TABLE "SCOTT"."SALGRADE" MODIFY ( "LOSAL" ENCRYPT USING 'AES192',
 "HISAL" ENCRYPT );
ALTER TABLE "SCOTT"."SALGRADE" MODIFY ( "LOSAL" ENCRYPT USING 'AES192', "HISAL" ENCRYPT )
*
ERROR at line 1:
ORA-28361: master key not yet set

An explanation of the error message is, via oerr ora 28361 ,:

28361, 0000, "master key not yet set"
// *Cause:  The master key for the instance was not set.
// *Action: Execute the ALTER SYSTEM SET KEY command to set a master key
//          for the database instance.

Set the Database Master Key

With the wallet opened, I set the master key for the database as follows:

SQL> alter system set encryption key identified by "**********";
alter system set encryption key identified by "**********"
*
ERROR at line 1:
ORA-28378: Wallet not open after setting the Master Key

An explanation of the message is, via oerr ora 28378 ,:

28378, 0000, "Wallet not open after setting the Master Key"
// *Cause:   The Master Key has been set or reset. However, wallet could not be
//           reopened successfully.
// *Action:  Reopen the wallet.

Open the wallet yet again using the wallet password:

SQL> alter system set encryption wallet open identified by "**********";

System altered.

Second Attempt to Encrypt Columns

Let's try again to encrypt the LOSAL and HISAL columns in the SCOTT.SALGRADE table:

SQL> ALTER TABLE "SCOTT"."SALGRADE" MODIFY ( "LOSAL" ENCRYPT USING 'AES192',
 "HISAL" ENCRYPT );

Table altered.

Now it works!

Check that Data is Accessible

Ran the following test to see that the data is still visible:

SQL> select * from scott.salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

Test the Auto Login Feature of the Wallet

To test whether to wallet will be automatically opened when the database instance restarts, I did the following test:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  322240512 bytes
Fixed Size                  1299652 bytes
Variable Size             155192124 bytes
Database Buffers          159383552 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL>  select * from scott.salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

Create an Encrypted Tablespace

I used the following command to create an encrypted tablespace called ENCRYPTED_TS :

SQL> CREATE SMALLFILE TABLESPACE "ENCRYPTED_TS"
  DATAFILE '+DATA' SIZE 100M LOGGING
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO DEFAULT
  STORAGE(ENCRYPT) ENCRYPTION USING 'AES192';

Tablespace created.

And I set up a test table in this tablespace and added some data to it:

SQL> CREATE TABLE "SCOTT"."TEST_ENCRYPT1" ( "COL1" NUMBER, "COL2" VARCHAR2(20))
 TABLESPACE "ENCRYPTED_TS";

Table created.

SQL> insert into scott.test_encrypt1 values (1, 'ONE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test_encrypt1;

      COL1 COL2
---------- --------------------
         1 ONE

Key Points to Remember

Correct Wallet Location

The correct wallet location is $ORACLE_BASE/admin/$ORACLE_SID/wallet which is NOT the system default.

Creation of Wallet

Use owm to create the wallet.

Set Auto Login

Use owm on an opened wallet to set the auto login feature.

Set Master Key

To set the master key:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY wallet_pw;
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY master_key;
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY wallet_pw;