Oracle Advanced database security Complies with privacy and regulatory mandates that require companies to encrypt and redact application data such as credit cards, social security numbers, or personally identifiable information (PII). By encrypting data at rest and masking data whenever it leaves the database, Oracle Advanced Security provides solution for comprehensive data protection.
Encrypting Tablespaces, tables and columns are few of the advanced security options to secure data in the database.
Encrypting Tablespaces
In Oracle 8i, Oracle introduced the
DBMS_OBFUSCATION_TOOLKIT, and the Oracle 10.1 release introduced the
DBMS_CRYPTO package to facilitate encryption. Both the toolkit and the DBMS_CRYPTO package required that the application manage the encryption keys and call the APIs to perform necessary encryption/decryption operations.
In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE) feature, which let you easily encrypt a column’s data in a table. The encryption is called 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 tablespace creation.
The Tablespace creation statement for an encrypted tablespace has the following syntax:
create tablespace <tbsp_name>
encryption / encryption using <encryption_algorithm>
default storage(encrypt);
The encryption clause in line 2 doesn’t actually encrypt the tablespace. You provide the encryption properties by setting values for the keyword encryption. You may additionally specify the using clause along with the encryption clause (
encryption using . . .) to specify the name of the encryption algorithm you want to use, such as
3DES168, AES128, AES192, and AES256. If you want to use the
default algorithm of AES128, you can omit the using clause altogether. It is the encrypt keyword passed to the storage clause in line 3 that encrypts the tablespace.
But before I actually encrypt a tablespace, let me show you how to create an Oracle wallet, because you’ll need the wallet when you encrypt a tablespace.
Creating the Oracle Wallet as pre-requisite to encrypting Tablespace:
An Oracle Wallet is a container to store authentication and signing credentials. The tablespace encryption feature uses the wallet to 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 startup, whereas the auto-open wallet automatically opens upon database startup. The encryption wallet is commonly recommended for tablespace encryption, unless you’re dealing with unattended Data Guard environments, in which case the automatic opening of the wallet comes in handy.
In order to use Oracle Wallet, you must create the wallet itself and then add a master key to it. You can create a wallet in a couple of ways. You can create the Oracle Wallet by:
■ Using the mkstore command from the operating system command line
■ Invoking the Oracle Wallet Manager either through a GUI interface or by issuing the command
owm at the command line
■ Executing the alter system statement from SQL*Plus
Here is the syntax to create a wallet from the OS:
$ mkstore -wrl $ORACLE_BASE/admin/$ORACLE_SID/wallet -create
Enter password:*******
Enter password again: *******
However, the simplest way to create the wallet is to simply use the following command in SQL*Plus:
SQL> alter system set encryption key identified by "password";
This command both creates the wallet if it doesn’t already exist and adds a master key to it. 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 system–specific. However, you can specify a different location by using the
parameter encryption_wallet_location in the sqlnet.ora file.
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/apps/oracle/general/wallet) ) )
Before you create the Oracle Wallet, you must first create a directory named wallet under the directory
$ORACLE_BASE/admin/$ORACLE_SID. If you don’t do this, you’ll get the error ORA-28368: cannot auto-create wallet. After you create the directory named wallet, issue the following statement from SQL*Plus:
SQL> alter system set encryption key identified by "samimalik";
The alter system statement I 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 don’t have an Oracle Wallet already, it creates a new wallet, opens the wallet, and creates a new master encryption key.
Now that you’ve successfully created the Oracle Wallet and ensured it is open, you’re ready to encrypt tablespaces using the new tablespace encryption feature.
Creating an Encrypted Tablespace
Once you create the Oracle Wallet, creating an encrypted tablespace is a breeze. The following is an example showing how to create a simple encrypted tablespace that uses the default DES128 encryption. Because you don’t have to specify the default encryption level, you don’t specify the using clause for the encryption clause in line 3.
SQL> create tablespace encrypt1
datafile 'd:\app\oracle\oradata\seveneleven\encrypt_01.dbf' size 100m
encryption
default storage (encrypt);
The storage parameter encrypt ensures that the tablespace is encrypted. The encryption clause determines the tablespace encryption properties. In this example, I use the encryption clause by itself, without specifying a particular encryption algorithm for the tablespace. The database will use the default AES128 encryption algorithm to encrypt the tablespace. You can also specify the optional using <algorithm> clause along with the encryption clause, as shown in the following example, to specify the exact encryption algorithm you want.
SQL> create tablespace encrypt1
datafile 'd:\app\oracle\oradata\seveneleven\encrypt_01.dbf' size 100m
encryption using '3des168'
default storage (encrypt);
The
new column ENCRYPTED in the DBA_TABLESPACES view lets you check the encryption status of a tablespace,
SQL> select tablespace_name, encrypted from dba_tablespaces;
TABLESPACE_NAME ENCRYPTED
----------------------------------------------------
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
ENCRYPT1 YES
SQL>create tablespace DATA datafile
'…/data01.dbf' size 4000M reuse autoextend off
logging online permanent
blocksize 16K
extent management local autoallocate
segment space management auto
default compress for all operations storage (encrypt)
encryption using 'AES256'
/
Oracle encrypts the data in the tablespace upon writing it and decrypts it upon reading the data. There is no additional memory requirement because the tablespace encryption and decryption aren’t performed in memory, but there is an encryption overhead on I/O. The encrypted data will remain encrypted in both the undo segments as well as the redo logs, in addition to being encrypted in temporary tablespaces during typical operations such as sort and join operations that make use of a temporary tablespace.
Below two pics depict the "Loading of Data Buffers" and "Flushing of buffers from Cache to Disk" respectively.
Note: If you want to change the key for an encrypted tablespace, the only method in the present release is to create a new tablespace and move all the objects in the encrypted tablespace to the new tablespace. You can then encrypt the new tablespace.
Restrictions on Tablespace Encryption
When you encrypt a column(s) for a table, there are limitations on certain queries. By encrypting the entire tablespace, some of these restrictions are removed. For example, in Oracle Database 10g, if the column is part of a foreign key or used in another Database Constraint, it cannot be encrypted. By encrypting the entire tablespace instead of just a table or tables, this restriction is lifted. Note the following restrictions on tablespace encryption. You
■ Can transport an encrypted tablespace only if the two operating system platforms have the same endianness and the same wallet.
■ Can’t change the key for an encrypted tablespace.
■ Can’t encrypt temporary and undo tablespaces.
■ Can’t encrypt bfiles and external tables.
Encryption vs Performance
In general, encryption solves a security issue while introducing a
new one: degraded performance. The latter is not acceptable in many
real-world situations, so many organizations have little choice but to
sacrifice encryption in favor of performance. With transparent
tablespace encryption, however, degraded performance is no longer an
issue—the performance in an encrypted tablespace is on a par with
cleartext performance in unencrypted tablespaces while the data is
protected at the storage level via encryption. And best of all,
encryption is done transparently without you having to write a single
line of code.
New Feature in Oracle Database 12c Release 2
With Oracle Database 12c Release 2, encryption of a tablespace can be deployed with zero downtime. The encryption process can be executed in the background, so that the tablespace can be available for DML access. Basically, you can encrypt, decrypt, and rekey a tablespace using Transparent Data Encryption (TDE) in live conversion. However, you cannot encrypt, decrypt or rekey a temporary tablespace online. This process will help with rotation of data encryption keys, with TDE in the background.
Online Encrypt Tablespace
To encrypt an existing tablespace online, you must login to the database with the
SYSKM role (SYSKM is responsible for TDE and Data Vault related administrative operations). To encrypt the SYSTEM or SYSAUX tablespace, you must login with the SYSDBA role. Issue the ALTER TABLESPACE command with the ENCRYTION and ENCRYPT clause, to encrypt a tablespace online:
SQL> ALTER TABLESPACE sysaux ENCRYPTION ONLINE USING ‘AES256’ ENCRYPT;
Tablespace altered.
If you do not specify the USING clause, the default encryption will be set to AES128 bit encryption. For non-OMF files, we must also specify the FILE_NAME_CONVERT clause and list out the filenames with the source filename, to target the encrypted filename. Here is an example for the FILE_NAME_CONVERT clause: FILE_NAME_CONVERT = (‘sysaux01.dbf’ ‘sysaux01_enc.dbf’).
The second filename, for the FILE_NAME_CONVERT clause, will become the encrypted version of the file; once the ALTER TABLESPACE command completes execution.
Online Decrypt Tablespace
With Oracle Database 12c Release 2, we can decrypt a tablespace online without any downtime. To decrypt a tablespace, execute the ALTER TABLESPACE with the DECRYPT option:
SQL> ALTER TABLESPACE sysaux ENCRYPTION ONLINE DECRYPT;
Tablespace altered.
Online Rekey Tablespace
We can also rekey a tablespace encryption. In the example below, we are rekeying a tablespace from AES128 to AES192 bit encryption for a non-OMF managed file:
SQL> ALTER TABLESPACE vna_index ENCRYPTION ONLINE USING ‘AES192’ REKEY FILE_NAME_CONVERT = (‘vna_index01_enc.dbf’,’vna_index01_enc192.dbf’);
Tablespace altered.
In the example above, we will replace the vna_index01_enc.dbf file with vna_index01_enc192.dbf, in the same directory where the vna_index01.dbf file was located, after the encryption rekey process completes.
Complete Database Encryption
The capability to enable TDE encryption for all tablespaces; including SYSTEM, SYSAUX, and UNDO is now available. This provides complete encryption of the databases for regulatory compliance. Oracle extends their encryption and hashing algorithms to include ARIA, GOST, and SEED Encryption Algorithms for International Standards
TDE Tablespace Offline Conversion
Oracle Database 12c Release 2 provides the ability to perform offline conversion of a tablespace, without additional storage overhead. We can leverage compute across RAC, and many instances, with parallel processing on many CPU cores. For Data Guard configuration, encryption can be done on physical standby first and then switchover, while the primary database is being encrypted.
Separation of Duty for Administering RAC Clusters
12.2 RAC introduces an administrative privilege called SYSRAC. The intent of the SYSRAC privileges, is to provide only the minimal set of privileges for the RAC administrator to perform their day to day tasks. This privilege is used by the clusterware agent and removes the need to use SYSDBA privilege for RAC.
