Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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.

Related articles.

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:

  1. If present, the location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
  2. If present, the location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
  3. The default location for the wallet. If the $ORACLE_BASE is set, this is "$ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet", otherwise it is "$ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet", where DB_UNIQUE_NAME comes from the initialization parameter file.

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 IDENTIFIED BY "myPassword";

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

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

ALTER SYSTEM SET ENCRYPTION 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;

Database Startup

The following text shows the impact of stopping and starting the database on an encrypted tablespace. Notice how the wallet must be opened before the data is accessible.

SQL>CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             234882088 bytes
Database Buffers          381681664 bytes
Redo Buffers                7507968 bytes
Database mounted.
Database opened.
SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

TABLESPACE_NAME                ENC STATUS
------------------------------ --- ---------
SYSTEM                         NO  ONLINE
SYSAUX                         NO  ONLINE
UNDOTBS1                       NO  ONLINE
TEMP                           NO  ONLINE
USERS                          NO  ONLINE
EXAMPLE                        NO  ONLINE
SOE                            NO  ONLINE
ENCRYPTED_TS                   YES ONLINE

8 rows selected.

SQL> SELECT * FROM test.ets_test;
select * from test.ets_test
                   *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";

System altered.

sys@db11g> select * from test.ets_test;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

1 row selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.