In Oracle Database 11g, we can encrypt an entire tablespace by simply using a pair of special clauses 'encryption' during tablespace creation.Once the tablespace is encrypted, the entire tables and associated indexes in the tablespace are encrypted along with the data stored in the redo logs.
But before doing tablespace encryption, we'll need to create an Oracle wallet
Creating the Oracle Wallet
An Oracle Wallet is a container to store authentication and signing credentials.
Ways to create Oracle Wallet:
Wallet can be created from OS, SQLPLUS or GUI interface. However, before creating the Wallet, we must first create a directory named wallet under $ORACLE_BASE/admin/$ORACLE_SID to avoid ORA-28368: cannot auto-create wallet.
$ mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -create
Enter password again:
SQL> alter system set encryption key identified by "password";
The alter system statement works in the following way:
- If Oracle Wallet exists, it opens that wallet and creates (or re-creates) the master encryption key
- If Oracle Wallet does not exist, it creates a new wallet, opens the wallet, and creates a new master encryption key.
Not covered in this article.
The tablespace encryption feature uses the wallet to protect the master key used in the encryption.Oracle stores the encryption keys outside the database, in a file called an Oracle Wallet. By default, this file is named ewallet.p12 under both Windows and UNIX/Linux-based systems. The location where Oracle stores this file is operating ystem–specific. However, you can specify a different location by using the parameter encryption_wallet_location in the sqlnet.ora file as below
(DIRECTORY=/apps/oracle/general/wallet) ) )
Creating an Encrypted Tablespace:
Once Oracle Wallet is created, encrypted tablespace is failrly simple
datafile 'u01\app\oracle\oradata\encrp_ts\ encrypt_01.dbf' size 100m
default storage (encrypt);
The storage parameter encrypt ensures that the tablespace is encrypted.
The new column ENCRYPTED in the DBA_TABLESPACES displays encryption status of a tablespace:
SQL> select tablespace_name, encrypted
2 from dba_tablespaces;
6 rows selected.
Oracle encrypts the data in the tablespace upon writing it and decrypts it upon reading the data.There is no additional memory requirement however, there is an encryption overhead on I/O.
Restrictions on Tablespace Encryption
• You cannot encrypt an existing tablespace.
• exp and imp utilities are not supported with objects in the encrypted tablespaces.
• You cannot re-create the tablespace encryption key.
• The NO SALT option is not supported.
• Temporary and undo tablespaces cannot be encrypted.
• COMPATIBLE parameter must be set to 11.1 or higher.
• BFILES and external tables are not encrypted.
• Logically, encrypted tablespace is less efficient than normal un-encrypted tablespace