Tablespace Encryption in Oracle 11g Database Release 1

The Transparent Data Encryption (TDE) feature was introduced in Oracle 10g Database Release 2 to simplify the encryption of data within datafiles, preventing access to it from the operating system. Tablespace encryption extends this technology, allowing encryption of the entire contents of a tablespace, rather than having to configure encryption on a column-by-column basis.

Wallet Creation
Tablespace Creation
Test Encryption

Wallet Creation

The environment setup for tablespace encryption is the same as that for transparent data encryption. Before attempting to create an encrypted tablespace, a wallet must be created to hold the encryption key. The search order for finding the wallet is as follows:

If present, the location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
If present, the location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
The default location for the wallet ($ORACLE_BASE/admin/$ORACLE_SID/wallet).

Although encrypted tablespaces can share the default database wallet, Oracle recommend you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file. To accomplish this we add the following entry into the sqlnet.ora file on the server and make sure the specified directory has been created.

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/u01/app/oracle/admin/DB11G/encryption_wallet/)))

This parameter can also be used to identify a Hardware Security Model (HSM) as the location for the wallet.

The following command creates and opens the wallet.

CONN sys/password@db11g AS SYSDBA
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "myPassword";

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data.

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "myPassword";

ALTER SYSTEM SET WALLET CLOSE;

Tablespace Creation

Encrypted tablespaces are created by specifying the ENCRYPTION clause with an optional USING clause to specify the encryption algorithm. If the USING clause is omitted, the encryption algorithm defaults to 'AES128'. In addition, the default storage clause of ENCRYPT must be specified. Tablespace encryption does not allow the NO SALT option that is available in TDE. The following statement creates an encrypted tablespace by explicitly naming the 'AES256' encryption algorithm in the USING clause.

CREATE TABLESPACE encrypted_ts
DATAFILE '/u01/app/oracle/oradata/DB11G/encrypted_ts01.dbf' SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

The ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
ENCRYPTED_TS                   YES

6 rows selected.

SQL>

Regular tablespaces cannot be converted to encrypted tablespaces. Instead, data must be transferred manually using export/import, "ALTER TABLE ... MOVE ..." or "CREATE TABLE ... AS SELECT * FROM ...".

Test Encryption

With the tablespace in place, we can create some objects to test the encryption. The following code creates a table and index in the encrypted tablespace and inserts a single row into the table.

CONN test/test@db11g

CREATE TABLE ets_test (
  id    NUMBER(10),
  data  VARCHAR2(50)
)
TABLESPACE encrypted_ts;

CREATE INDEX ets_test_idx ON ets_test(data) TABLESPACE encrypted_ts;

INSERT INTO ets_test (id, data) VALUES (1, 'This is a secret!');
COMMIT;

Flush the buffer cache to make sure the data is written to the datafile.

CONN sys/password@db11g AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;

When the file is opened using a HEX editor (like UltraEdit) only non-printable characters are present. The 'This is a secret!' string is not visible in the table or index data within the encrypted tablespace.

When you are finished testing the encrypted tablespace, be sure to clean up the tablespace and associated datafile.

DROP TABLESPACE encrypted_ts INCLUDING CONTENTS AND DATAFILES;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.