Tablespace Encryption - Oracle 11g

In Oracle Database 10g Release 2, Oracle introduced the new transparent data encryption (TDE) feature, which lets you easily encrypt a column’s data in a table.The encryption is “transparent” because the Oracle database takes care of all the encryption and decryption details, with no need for you to manage any tables or triggers to decrypt data.Now, in Oracle Database 11g, you can encrypt an entire tablespace by simply using a pair of special clauses during table space creation.

Oracle has been gradually improving its encryption capabilities over the years.In Oracle 8i,Oracle introduced the DBMS_ OBFUSCATION_ TOOLKITpackage and in the Oracle 10.1 release introduced the DBMS_CRYPTOpackage to facilitate encryption.Both the toolkit and the DBMS_CRYPTOpackage required the appli cation to manage the encryption keys and call the API to perform the necessary encryption/decryption operations.

Prior to Oracle Database 11g,encryption was limited to a column or multiple columns of a table. When entering data in an encrypted column,Oracle trans parent data encryption automatically encrypts the data.When the data is selected out of the table, it is automatically decrypted.Oracle Database 11g adds the functionality to encrypt a complete table space.This breaks the boundary of encryption at the column.Tablespace encr yption also relies on encryption keys in a wallet outside the database.

When you encrypt a column,you have to be concerned about performance implications.The higher the level of encryption, the more CPU intensive the encryption and decryption function.Oracle supports up to AES 256-bitencryption.

Tablespace encryption eliminates the need for granular analysis of applications to determine which columns are candidates for encryption. You can use tablespace encryption to encrypt entire tables and associated indexes by moving the objects into an encrypted tablespace.The data from the encrypted tablespace is transparently decrypted at runtime.

Tablespace encryption will also encrypt objects that are stored as LOBsincluding BLOBs and CLOBs.However, if you create a table with a BFILE,it will encrypt all other columns except for the BFILE since BFILEs are stored outside the database.Similar to BFILEs,external tablesare not encrypted.

Certain situations in column-level encryption prohibit its use.For example,you cannot encrypt a column that participates in an index scan.When you encrypt a column(s) for a table,there are limitations on certain queries. For example,in Oracle Database10g,you cannot encrypt a column if it is part of a foreign key or used in another database.Encrypting the entire tablespace re moves some of these restrictions.The following are additional considerations to usingtablespace-level encryption:

  • Function-based indexes
  • Index range scans
  • Datatype restrictions
  • Partitioned/subpartitioned tables

Creating the Oracle Wallet

An Oracle Wallet is a container to store authentication and signing creden tials.The tablespace encryption feature relies on the Oracle Wallet to store and protect the master key used in the encryption.There are two kinds of Oracle Wallets—encryption wallets and auto-open wallets.You must manually open an encryption wallet after database start-up, whereas the auto-open wallet automatically opens upon database start-up.The encryption wallet is commonly recommended for tablespace encryption. However, if you are dealing with unattended Data Guard environments, the automatic opening of the wallet comes in handy.

The Oracle Wallet, named ewallet.p12 under both Windows and Unix/Linux-based systems,is a file in your directory system.The location where Oracle stores this file is operating system specific.However, you can specify a nondefault location by using the parameter encryption_wallet_location in the sqlnet.ora file,as shown here:

(DIRECTORY=/apps/oracle/general/wallet) ) )

To use TDE,you must have the ALTER SYSTEM privilege as well as a password for an Oracle Wallet. If you do not have an Oracle Wallet,you must create one and then add a master key to it.You can create the Oracle Wallet in several ways:

  • By invoking the Oracle Wallet Manager through a GUI
  • By invoking the Oracle Wallet Manager by issuing the command owm at the command line
  • By using the mkstore command from the operating system command line

Creating the Oracle Wallet

Wallet Requirements

TDE requires an Oracle Wallet encryption key be established and opened. Because the wallet is the essence of TDE, we will take you on a little tour to create a key and open it for use for the database.The easiest way to create a wallet is by using the ALTER SYSTEM command, which is the method we use here:

SQL>alter system set encryption key identified by "clidba123"; System altered.

The alter system statement shown here both creates the wallet if it does not already exist and adds a master key to it.You must replace the password with your own password for the wallet.The ALTER SYSTEM statement you issued in the previous example works in the following way:

  • If you already have an Oracle Wallet,it opens that wallet and creates (or re-creates) the master encryption key.
  • If you do not have an Oracle Wallet already,it creates a new wallet,opens the wallet,and creates a new master encryption key.

Since we created an encryption wallet,we must re-open the wallet after database start-up.To open the wallet (ewallet.p12) after database start-up, you can issue the following command:

alter system set wallet open identified by "clidba123";

Please remember that since the wallet was created with the double quotes around it, it needs to be opened with double quotes. Otherwise, you will receive the ORA- 28353 (“failed to open wallet”) error.To verify that a wallet is open, you can query the V$ENCRYPTION_WALLET view:

SQL>select wrl_parameter, status from v$encryption_wallet
----------------------------------- -----------
/apps/oracle/general/wallet OPENWallet Requirements

Encrypted Tablespace Caveats

There are several caveats to encrypted tablespaces.You cannot encrypt an existing tablespace. You can encrypt a new tablespace only at creation time.If you want to encrypt an existing table space, you must move the objects into a newly created encrypted tablespace using the various options such as CTAS, ALTER TABLE MOVE,exp/imp,online data redefinition, or expdp/impdp.Table space-level encryption makes it transparent to even developers.Deve lopers do not have to be concerned about how to encrypt a column or what is required to maintain a high-performance data encryption environment.

If you are upgrading from Oracle Database 10g Release 2 to Oracle Database 11g and have set the compatibility mode to 11.1.0, you need to reissue the ALTER SYSTEM SET ENCRYPTION KEY command to create a master encryption key for tablespace encryption.This command will re-create the standard TDE master key and overwrite the existing one if one already exists.It also creates a new tablespace master encryption key.If the tablespace key already exists in the wallet folder,the ALTER SYSTEM SET ENCRYPTION KEY command will not rekey it.

Encrypted Tablespace Caveats

Additional restrictions apply to encrypted tablespaces:

  • The NO SALT option cannot be used (for clarification on SALT, please refer to the beginning of this chapter).
  • BFILES and external tables are not encrypted.
  • Temporary and undo tablespaces cannot be encrypted.
  • Transportable tablespaces must be in the same endian format.

Encrypting a Tablespace

Once you create the Oracle Wallet,creating an encrypted tablespace is a breeze.The tablespace creation statement for an encrypted tablespace has the following syntax:

create tablespace <tbsp_name> encryption default storage(encrypt)

The encryption clause in the second line does not actually encrypt the tablespace.You merely provide the encryption properties by setting values for the keyword encryption.You may use the using clause to specify the name of the encryption algorithm you want to use.The supported encryption algorithms are as follows:

  • AES192:Advanced Encryption Standard 192-bit encryption
  • 3DES168:Triple Data Encryption Standard 168-bit encryption
  • AES128:Advanced Encryption Standard 128-bit encryption
  • AES256:Advanced Encryption Standard 256-bit encryption

You can use the default algorithm of AES128,in which case you can omit the using clause altogether.The encrypt keyword is passed to the storage clause in the third line that performs the actual encryption of the tablespace.In the following sections,we review how to encrypt a tablespace.

The following is an example showing how to create a simple encrypted table space that uses the default AES 128-bit encryption. Since you do not have to specify the default encryption level, you do not specify the using clause for the encryption clause in the third line.

SQL> create tablespace docs_encrypted_default
2 datafile '+DATA'
3 encryption
4 default storage (encrypt);
Tablespace created.

Next,we will create an encrypted tablespace with the AES 192-bit encryption .Notice the keywords encryption using 'AES192' default storage (encrypt) in the following example:

SQL> create tablespace docs_d_e
2 datafile '+FRA' size 10m
3 extent management local uniform size 128k
4 segment space management auto
5 encryption using 'AES192'
6* default storage (encrypt)
Tablespace created.

Now all objects created in the docs_d_e tablespace will be encrypted.The beauty behind encrypted tablespaces is that the encryption consideration just becomes an object placement consideration.By designing an encrypted table space,the DBAs can place objects into the encrypted tablespace and be confi dent that the data is encrypted.Furthermore, both DBAs and data architects can collaborate and place objects into the encrypted tablespaces during the design phase.

Upon creating the encrypted tablespace,you can query the DBA_TABLESPACES view to review the encryption state. Oracle provides a new column called ENCRYPTED in the DBA_TABLESPACES view to reveal whether a tablespace is enc rypted.In the following query, we are identifying the encryption attributes for tablespaces that start with the name DOC:

1 select tablespace_name,encrypted
2 from dba_tablespaces
3* where tablespace_name like 'DOC%'
SQL> /
------------------------------- -----

Another provided view called V$ENCRYPTED_TABLESPACES reveals the encryption attributes of encrypted tablespaces:

Name Null? Type
-------------------------------- -------- -----------------------

You can join this view with the V$TABLESPACE view based on the ts# column to determine which tablespace is encrypted and which encryption algorithm is being used.The following query produces output of all encrypted tablespaces including the encryption algorithm information:

SQL> select,vet.encryptionalg, vet.encryptedts
2 from v$encrypted_tablespaces vet,
3 v$tablespace vt
4* where vet.ts#=vt.ts#
SQL> /
------------------------------------- ----------- -------

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics