In this chapter, we will cover the following tasks:
Configuring a keystore location in sqlnet.ora
Creating and opening the keystore
Setting a master encryption key in a software keystore
Column encryption - adding a new encrypted column to a table
Column encryption - creating a new table that has encrypted column(s)
Using salt and MAC
Column encryption - encrypting the existing column
Autologin keystore
Encrypting tablespace
Rekeying
Backup and recovery
Encryption is a very important security mechanism used to enforce confidentiality of data. There are two types of encryption that can be used in the Oracle Database. The first type is application-based encryption, which is implemented using the DBMS_CRYPTO PL/SQL package (this type is not covered in this book), and the second type is Transparent Data Encryption (TDE). TDE is a part of Advanced Security option of Oracle Database Enterprise Edition. It can be used to encrypt data in rest (table columns and tablespaces inside the database) and in transit (network, Recovery Manager (RMAN) backups, and Data Pump Exports).
The word transparent in Transparent Data Encryption means that application is not aware that data is encrypted in any way. In other words, application will never see the encrypted data-if user is not authorized to see the data, error (for example, insufficient privileges, table, or view does not exist) will be shown. The only way that a user will see encrypted data...
In this recipe, you're going to configure the location of a software keystore in a regular file system. If you want to use Hardware Security Module (HSM), see the official Oracle documentation (Chapter 3 in Oracle Advanced Security Guide, part named Configuring Hardware Keystore).
Create a directory, to hold a keystore, that is accessible to the owner of Oracle software (for example, $ORACLE_BASE/admin/ora12cR1/wallet). See Figure 1:
Figure 1 - Create a directory and edit sqlnet.ora
Edit sqlnet.ora and add entry to specify the location of the keystore (see Figure 1 and 2). This step is optional if you are using default location for the wallet, which is $ORACLE_HOME/admin/<db_name>/wallet.
Figure 2 - Define ENCRYPTION_WALLET_LOCATION parameter
In this recipe, you're going to create a password-based keystore. Open it and learn to check its status.
It is assumed that the keystore location is already configured (instructions are given in the recipe Configuring keystore location in sqlnet.ora). In this recipe, you'll grant, as the SYS user, administer key management privilege, or SYSKM administrative privilege to an existing user (for example, maja).
Connect to the database as a user who can grant administer key management privilege (for example, SYS) and grant the privilege to an existing user (for example, maja).
To create a password-based software keystore, connect to the database as the user in the previous step (for example, maja) and execute the following statement (after you change parameters so that they are appropriate for your environment) (an example is shown in Figure 3):
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location'
IDENTIFIED...In this recipe, you're going to create the first master key for the password-based software keystore you created and opened in the previous recipe.
It is assumed that software keystore is already opened. To complete this recipe, you'll need an existing user who has the SYSKM administrative or administer key management privilege (for example, maja).
Connect to the database as a user who has the SYSKM administrative or administer key management privilege (for example, maja):
$ sqlplus maja
Create a master key for the password-based keystore (Figure 7 shows the creation of master key for the keystore you created in the recipe Creating and opening the keystore):
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY
keystore_password
WITH BACKUP
USING 'desc_purpose';
Figure 7
In this recipe, you'll add a new column, which will be encrypted using a nondefault encryption algorithm, to an existing table.
Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja) and verify that the keystore is in the OPEN status. You should get the result similar to the one depicted in Figure 9:
$ sqlplus maja
Figure 9
Add a column (for example, bonus) to a table (for example, hr.employees), encrypted using the AES 256 algorithm.
Figure 10 - Adding the new encrypted column to the table
In this recipe, you're going to learn to use TDE column encryption to encrypt columns in a newly created table.
Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja):
$ sqlplus maja
Create a new table (for example, table enc_cols in schema hr) that has, for example, the following structure:
|
Column name |
Column type |
Encrypted |
|
|
|
No |
|
|
|
Yes, AES192 |
|
|
|
Yes, AES192 |
Figure 11 - A syntax to create the table hr.enc_cols
Connect to the database as a user who can insert and view data in the table (for example, hr user):
SQL> connect hr
Insert several arbitrary values into the table HR.ENC_COLS.
Figure 12 - Test values
Verify that the user can view unencrypted values in all columns.
Figure 13- Encryption is transparent
Connect...
In this recipe, you'll understand when you should use salt and MAC.
Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja):
$ connect maja
Encrypt two columns in an existing table (for example, sh.customers)
Figure 15 - Using salt and MAC
In step 2:
You encrypted the last_name column using the AES256 algorithm with salt and used MAC
You encrypted the cust_street_address column using the AES256 algorithm with no salt and used MAC
In general, you have to use same encryption algorithm for all encrypted columns at the same time. You can choose a SALT option on the encrypted column level in a table, but you have to choose either the MAC or NOMAC option on a table level (meaning that all encryption columns in a table must use the same option).
It is common case that organizations first create database and later decide that they want to implement encryption. In this recipe, you're going to encrypt an existing column using TDE column encryption.
Connect to the database as a user who can read data from the OE.CUSTOMERS table (for example, the oe user):
$ sqlplus oe
Select data from column you want to encrypt (for example, cust_email), just to verify that the user can view it.
Figure 18 - A test query
Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja):
SQL> connect maja
Encrypt the cust_email column in the oe.customers table using the default encryption algorithm (AES192) and no salt.
Figure 19 - Encrypting an existing column, which has an index
Execute steps 1 and 2 again to verify that there is no change in the way user/application views...
Autologin keystore is a type of keystore that doesn't need to be manually opened. The local autologin keystore can be opened only from computer where it has been created. Autologin keystores have system-generated passwords. They are less secure than password-based keystores. They are created from password-based software keystores.
Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja):
$ sqlplus maja
Create (local) an autologin keystore. In our case, keystore_location is /u01/app/oracle/admin/ora12cR1/wallet and keystore_password is welcome1:
SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM
KEYSTORE 'keystore_location' IDENTIFIED BY keystore_password;
OR
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM
KEYSTORE 'keystore_location' IDENTIFIED BY keystore_password;
It is not possible to encrypt an existing tablespace using TDE tablespace encryption. In this recipe, you'll create a new encrypted tablespace.
Connect to the database as a user who has a create tablespace privilege (for example, zoran):
$ sqlplus zoran
Create encrypted tablespace (for example, TEST_ENC) using AES192 encryption algorithm:
SQL> CREATE TABLESPACE TEST_ENC
DATAFILE '/u01/app/oracle/oradata/ORA12CR1/datafile/testenc01.dbf'
SIZE 20M
ENCRYPTION USING 'AES192'
DEFAULT STORAGE (ENCRYPT);
Figure 22 - Encrypting tablespace
In step 2, you create an encrypted tablespace TEST_ENC. To find information about encrypted tablespaces, you can query the V$ENCRYPTED_TABLESPACES view.
Figure 23 - Finding information about encrypted tablespace
You can change (rekey) a master key and table keys. You cannot rekey tablespace keys.
Connect to the database as a user who has administer key privilege or SYSKM privilege (for example, maja):
$ sqlplus maja
To rekey a table (for example, the oe.customer) using a different encryption algorithm (for example, AES128), execute the following statement:
Figure 24 - Rekeying a table key
Change a master key by executing the following statement (in our example, keystore_password is welcome1):
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY
keystore_password
WITH BACKUP;
Figure 25 - Rekeying a master key
RMAN supports three encryption modes:
Transparent mode
Password mode
Dual mode
In this recipe, you're going to learn to create encrypted backups using RMAN.
Connect to the RMAN as user who has the sysbackup privilege:
$ rman target '"zoran@orcl as sysbackup"'
Configure encryption on a database level:
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;
Backup a tablespace example in transparent mode:
RMAN> BACKUP TABLESPACE EXAMPLE tag 'tran_mode';
Enable dual mode encryption and backup tablespace example in dual mode:
RMAN> SET ENCRYPTION ON IDENTIFIED BY "password_1";RMAN> BACKUP TABLESPACE EXAMPLE tag 'dual_mode';
Enable password mode and backup tablespace example in password mode:
RMAN> SET ENCRYPTION ON IDENTIFIED BY "password_2" ONLY;
RMAN> BACKUP TABLESPACE EXAMPLE tag 'pass_mode';
© 2016 Packt Publishing Limited All Rights Reserved
