Enabling Transparent Data Encryption

D Enabling Transparent Data Encryption

Oracle Identity Manager supports Oracle Transparent Data Encryption (TDE). You can configure TDE before or after installing Oracle Identity Manager.

This appendix describes how to configure Oracle Transparent Data Encryption (TDE) for Oracle Identity Manager. It contains the following topics:

D.1 Types of Data Encryption

Oracle Database supports TDE tablespace encryption and TDE column encryption.

Oracle Database supports the following types of data encryption:

TDE tablespace encryption: Encrypts all content stored in that tablespace. It is useful in situations where the sensitive data are stored in multiple columns.
TDE column encryption: Protects data stored in a table column. It encrypts and decrypts data transparently when data passes through the SQL layer.

Note:

For detailed information about TDE, see Oracle Database Advanced Security Guide.

Oracle Identity Manager supports and works with TDE tablespace encryption.

D.2 Configuring TDE for New Installation of Oracle Identity Governance

Configuring TDE requires downtime for the data movement from un-encrypted tablespaces to encrypted tablespaces. Therefore, you configure TDE for Oracle Identity Manager deployment immediately after installing the database schemas using Repository Creation Utility (RCU) and before installing Oracle Identity Manager application.

To configure TDE for a new installation of Oracle Identity Manager:

Install Oracle Database. For details, refer to Oracle Database documentation.
Create Oracle Identity Manager schema and the dependent schemas by running RCU, as described in Creating Database Schemas Using the Oracle Fusion Middleware Repository Creation Utility (RCU) in Installation Guide for Oracle Identity and Access Management.
Shut down Oracle Identity Manager, if applicable.

If you are configuring TDE after installing Oracle Identity Manager, then you must shut down Oracle Identity Manager because TDE implementation does data movement and Oracle Identity Manager application will not be available for the time period when data movement occurs from normal tablespace to TDE-enabled tablespace.
Create a backup of Oracle Identity Manager database schema by using the Data Pump utility.
Using RDBMS data migration utilities, such as Data Pump, create a backup of Oracle Identity Manager database schema and the dependent schemas. This backup might be required to be restored post TDE enablement on tablespace level.The following is a sample command to create the backup:
```
expdp system/PASSWORD@TNS_ALIAS schemas=OIM_SCHEMA_NAME directory=DATA_PUMP_DIR dumpfile=DUMP_FILE_NAME logfile=LOG_FILE_NAME
```
Note:

Before exporting the Oracle Identity Manager schema, capture and retain the system and object grants on it by using the following SQL commands (to be run as SYS user):
- ```
SELECT DBMS_METADATA.GET_GRANTED_DDL ('SYSTEM_GRANT','OIM_SCHEMA_NAME') FROM DUAL;
```
- ```
SELECT DBMS_METADATA.GET_GRANTED_DDL ('OBJECT_GRANT', 'OIM_SCHEMA_NAME') FROM DUAL;
```
Copy the output of the SQL commands and edit it for appending semicolon (;) after each statement. The retained grants are required to be run post Step 10.
Specify the wallet location.
When you first enable TDE, you must create the wallet where the master key will be stored. By default, the wallet is created in the $ORACLE_BASE/admin/$ORACLE_SID/wallet/ directory.

You can select a different directory path by specifying it in the sqlnet.ora file located in $ORACLE_HOME/network/admin/ directory. For instance, if you want the wallet to be in the orawallet/ directory, then include the following lines in the sqlnet.ora file:
```
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=ORACLE_HOME\orawallet)))
```
For Oracle RAC clusters with local file system for binaries, change the SQLNET.ora of all the nodes.

Note:

A backup of the wallet location must be maintained along with the regular backups.

To use the same Oracle database wallet share by different Oracle components, set wallet parameter as follows:
```
WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=D:\oracle\product\11.2.0\dbhome_1\orawallet)))
```
Create the wallet.
For Oracle Database 11g:

To use TDE, you must have the ALTER SYSTEM privilege and a valid password to the Oracle wallet. If an Oracle wallet does not exist, then a new one is created by using the password specified in the SQL command.

To create a new master key and use TDE, run the following SQL command:
```
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "PASSWORD";
```
This command performs the following:
- Creates the wallet in the location specified in step 4.
- Sets the password of the wallet as the one you provided. The password is case-sensitive and must be enclosed in double quotes.
- Opens the wallet for TDE to store and retrieve the master key.
The SQL command generates the database server master encryption key, which the server uses to encrypt the column encryption key for each table. No table column in the database can be encrypted until the master key of the server has been set.

For Oracle Database 12c (12.1.0.2.0) Non-CDB and CDB:

To use TDE, you must have the ALTER SYSTEM, ADMINISTER KEY MANAGEMENT or SYSKM privilege and a valid password to the Oracle wallet. If an Oracle wallet does not exist, then a new one is created by using the password specified in the SQL command.

To do so:
1. To create a keystore and use TDE, Run the following SQL command from SYS user:
```
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '<keystore_location>' IDENTIFIED BY <keystore_password>;
```
  Here, <keystore_location> is the value provided in DIRECTORY path in the SQLNET.ORA file, as shown in step 5. <keystore_password> is the keystore password.
  
  This command performs the following:
  - Creates the wallet in the location specified in step 4.
  - Sets the password of the keystore as the one you provided. The password is case-sensitive.
  The SQL command generates the database server (keystore) master key, which the server uses to encrypt the column encryption key for each table. No table column in the database can be encrypted until the keystore key of the server has been set.
2. Verify that keystore has been created. Run the below SQL command from PDB (for CDB) and SYS (for Non-CDB) user.
```
SQL> SELECT wrl_parameter,status FROM v$encryption_wallet;
```
  The expected result of running this SQL command:
  - Status: Closed
  - wrl_parameter: <keystore_location> mentioned in step 5
3. Shut down the database, as shown:
```
SQL> shutdown immediate;
```
4. Restart the database by running the following command. For CDB, make sure to start the respective pluggable database(s) also.
```
SQL> startup;
```
Open the wallet.
For Oracle Database 11g:

As the wallet is created only once, you must specify the wallet location and create the wallet only once. The wallet must be opened explicitly with the master key whenever the database instance starts.

To load the master key after the database is restarted, run the following SQL command:
```
ALTER system SET encryption wallet OPEN authenticated BY "PASSWORD";
```
OR:
```
ALTER system SET wallet OPEN IDENTIFIED BY "PASSWORD";
```
The wallet must be open for TDE to work. If the wallet is closed, then you can access all non-encrypted columns, but not the encrypted columns.
Note:

You can close the wallet by running the following command:
```
ALTER system SET encryption wallet CLOSE IDENTIFIED BY "PASSWORD";
```
For Oracle Database 12c (12.1.0.2.0) Non-CDB

As the keystore is created only once, you must specify the keystore location and create the keystore only once. The keystore must be opened explicitly with the (keystore) master key whenever the database instance starts.

To do so:
1. Load the (keystore) master key after the database is restarted. Run the following SQL command as the SYS user:
```
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <keystore_password>;
```
  Here, <keystore_password> is the same password used in step 5 to create the wallet.
  
  The keystore must be open for TDE to work. If the keystore is closed, then you can access all non-encrypted columns, but not the encrypted columns.
2. Verify the status of the keystore. To do so, run the following SQL command as the SYS user:
```
SQL> SELECT status FROM v$encryption_wallet;
```
  Running this command sets the status to OPEN_NO_MASTER_KEY.
For Oracle Database 12c (12.1.0.2.0) CDB

As the keystore is created only once, you must specify the keystore location and create the keystore only once. The keystore must be opened explicitly with the (keystore) master key whenever the database instance starts.

To load the (keystore) master key and verify the status of the keystore:
1. To load the (keystore) master key after the database is restarted, run the following SQL command as the CDB SYS user:
```
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <keystore_password>;
```
  Here, <keystore_password> is the same password used in step 5 to create the wallet.
  
  The keystore must be open for TDE to work. If the keystore is closed, then you can access all non-encrypted columns, but not the encrypted columns.
2. Verify the status of the keystore. To do so, run the following SQL command as the CDB SYS user:
```
SQL> SELECT status FROM v$encryption_wallet;
```
  Running this command sets the status to OPEN_NO_MASTER_KEY.
3. To load the (keystore) master key after the database is restarted, run the following SQL command as the PDB SYS user:
```
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <keystore_password>;
```
  Here, <keystore_password> is the same password used in step 5 to create the wallet.
  
  The keystore must be open for TDE to work. If the keystore is closed, then you can access all non-encrypted columns, but not the encrypted columns.
4. Verify the status of the keystore. To do so, run the following SQL command as the PDB SYS user.
```
SQL> SELECT status FROM v$encryption_wallet;
```
  Running this command sets the status to OPEN_NO_MASTER_KEY.
Set the master encryption key (applicable to Oracle Database 12c only).
For Oracle Database 12c (12.1.0.2.0) Non-CDB

After the keystore is open, set the TDE master encryption key for the same. To do so:
1. To set the TDE master encryption key in a keystore, use the ADMINISTER KEY MANAGEMENT statement with the SET KEY clause. Run the following SQL command as the SYS user:
```
SQL> ADMINISTER KEY MANAGEMENT SET KEY [USING TAG '<tag>'] IDENTIFIED BY <password> [WITH BACKUP [USING 'backup_identifier']];
```
  Here:
  - <password> is the same password used in step 5 to create the wallet.
  - <tag> is the associated attributes and information that you define. Enclose this setting in single quotation marks (' '), for example 'oim12ccdb'.
2. Verify the status of the keystore. Run the below SQL command from SYS and PDB SYS user.
```
SQL> SELECT status FROM v$encryption_wallet;
```
  Running this command sets the status to OPEN.

Drop Oracle Identity Manager and its tablespaces.

Drop OIM user before dropping the tablespaces. The following are some sample commands to do so:

DROP USER OIM_SCHEMA_NAME CASCADE;
DROP TABLESPACE SCHEMA_NAME INCLUDING contents AND datafiles;
DROP TABLESPACE SCHEMA_NAME_LOB INCLUDING contents AND datafiles;
DROP TABLESPACE SCHEMA_NAME_ARCH_DATA INCLUDING contents AND datafiles;

Create TDE-enabled tablespaces and user for Oracle Identity Manager.

Create tablespaces for Oracle Identity Manager with encryption to enable TDE at tablespace layer. You must create all the three tablespaces that you dropped in step 8. You can use DBMS_METADATA API to get the DDL for tablespace creation. The following are sample commands:

CREATE TABLESPACE SCHEMA_NAME DATAFILE 'FILE_PATH' SIZE 128K AUTOEXTEND ON NEXT 64K EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
 
CREATE TABLESPACE SCHEMA_NAME_LOB DATAFILE 'FILE_PATH' SIZE 128K AUTOEXTEND ON NEXT 64K EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

CREATE TABLESPACE SCHEMA_NAME_ARCH_DATA DATAFILE 'FILE_PATH' SIZE 128K AUTOEXTEND ON NEXT 64K EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

CREATE USER SCHEMA_NAME IDENTIFIED BY PASSWORD DEFAULT TABLESPACE OIM_DATA_TBS TEMPORARY TABLESPACE OIM_TEMP;

Note:

Datafile path can be referred from the following command:

Select name from v$datafile;

To validate the encryption at tablespace level, you run the following query:

SELECT ts.name, es.encryptedts, es.encryptionalg FROM v$tablespace ts INNER JOIN v$encrypted_tablespaces es ON es.ts# = ts.ts#;

Import the data back to Oracle Identity Manager database for Oracle Identity Manager and its dependent schemas.
As TDE enabled-tablespaces are created, you must import/restore the Oracle Identity Manager schema backup. The following is a sample command to import the Oracle Identity Manager schema backup:
```
Impdp system/<PASSWORD>@TNS_ALIAS schemas=OIM_SCHEMA_NAME directory=DATA_PUMP_DIR dumpfile=DUMP_FILE_NAME logfile=LOG_FILE_NAME
```
Note:
- After importing the Oracle Identity Manager schema, execute the preserved grants, as suggested in step 4.
- After importing the Oracle Identity Manager schema, compile all the objects in the schema by using the following command (to be run as SYS user):
```
BEGIN
 UTL_RECOMP.recomp_serial('OIM_SCHEMA_NAME');
END;
```
  Here, replace OIM_SCHEMA_NAME with the Oracle Identity Manager database schema name.
Configure Oracle Identity Manager.

On successful import of the Oracle Identity Manager schema backup, continue with Oracle Identity Manager installation and configuration.

D.3 Configuring TDE for an Existing Installation of Oracle Identity Governance

Postinstallation configuration of TDE requires downtime for the data movement from un-encrypted tablespaces to encrypted tablespaces.

If you are configuring TDE after installing Oracle Identity Manager, then perform the following steps:

Shut down Oracle Identity Manager because TDE implementation performs data movement and Oracle Identity Manager application will not be available for that time period.
Perform steps 3 through 11, as described in Configuring TDE for New Installation of Oracle Identity Governance.
Start Oracle Identity Manager.

D.4 Deconfiguring TDE for Oracle Identity Governance

Deconfiguring TDE involves dropping OIM User and tablespaces after creating backups for the same, closing the encryption wallet, re-creating the tablespaces, and restoring the backups.

To deconfigure TDE for Oracle Identity Manager:

Create a backup of OIM User, tablespaces, and Object Grants by using DBMS_METADATA.GET_DDL() package.
Create a backup of Oracle Identity Manager database schema.
Drop OIM User.
Drop the following Oracle Identity Manager tablespaces:
- DEV_OIM
- DEV_OIM_LOB
- DEV_OIM_ARCH_DATA
Close the encryption wallet by running the following query as SYSDBA user:
```
ALTER system SET encryption wallet CLOSE IDENTIFIED BY "PASSWORD";
```
For Oracle Database 12c CDB and Non-CDB environment, connect to CDB as the SYS user, and run the following SQL command:
```
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <SOFTWARE_KEYSTORE_PASWORD> [CONTAINER = ALL | CURRENT];
```

Run the following Update query followed by Commit from SYSDBA user:

UPDATE TS$ SET flags=flags - 16384 WHERE online$=3 AND bitand(flags,16384) = 16384;COMMIT;

Restart Oracle Identity Manager database.
Re-create OIM user, tablespaces, and Object Level grants.
Restore the Oracle Identity Manager backup.
Remove Encryption entry from the SQLNET.ORA file.
Remove the wallet key/directory, which is ORACLE_HOME/orawallet/.
Start Oracle Identity Manager.