8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2
- Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)
- Data Encryption - DBMS_OBFUSCATION_TOOLKIT
- Database Security Enhancements in Oracle Database 10g - DBMS_CRYPTO
- SecureFiles in Oracle 11g Database Release 1 - LOB 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. 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:
- About Tablespace Encryption
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2
- Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)
- Data Encryption - DBMS_OBFUSCATION_TOOLKIT
- Database Security Enhancements in Oracle Database 10g - DBMS_CRYPTO
- SecureFiles in Oracle 11g Database Release 1 - LOB Encryption
Hope this helps. Regards Tim...