Enable Transparent Data Encryption (TDE) for Oracle E-Business Suite
Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database level. But not in Operating System Level (Data Files).
Oracle Database provides
Transparent Data Encryption (TDE), TDE encrypts sensitive data stored in
data files. To prevent unauthorized decryption, TDE stores the encryption keys
in a security module external to the database, called a keystore.
In order to use this
feature, Oracle E-Business Suite database requires downtime, as the tablespace
to be encrypted needs to be temporarily offline.
TDE is part of the Oracle
Advanced Security, which also includes Data Redaction. It is available as an
additional licensed option for the Oracle Database Enterprise Edition
Benefits are as follows:
1) Using TDE helps you address security-related regulatory
compliance issues
2) you can be sure that sensitive data is
encrypted and therefore safe in the event that the storage media or data
file is stolen.
3) You do
not need to modify your applications to handle the encrypted data. The database
manages the data encryption and decryption.
4) TDE is fully integrated with Oracle database.
Encrypted data remains encrypted in the database, whether it is in tablespace
storage files, temporary tablespaces, undo tablespaces, or other files that
Oracle Database relies on such as redo logs. Also, TDE can encrypt entire
database backups (RMAN) and Data Pump exports.
Prerequisites
1)
This procedure can be used with Oracle E-Business
Suite Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 ("12.1.0.2.171017
(Oct 2017) and later)
2)
Ensure the COMPATIBLE database parameter is set
to the appropriate database version, 11.2.0.4 or 12.1.0.2.
3)
Take a full backup of your database before
starting the procedure.
TDE Offline Datafile Encryption Restrictions
It can only be performed for application tablespace datafiles. SYSTEM, SYSAUX, UNDO and TEMP tablespaces cannot be encrypted.
External Large Objects (BFILEs) cannot be encrypted using TDE tablespace encryption. because these files reside outside the database
Procedure to Perform TDE Tablespace Offline Encyption
Create ACFS Directory on Oracle RAC to hold wallet and keys
Create wallet for TDE
1) Identify all the temporary and undo tablespaces in the database
a) SQL>select tablespace_name from dba_tablespaces where contents='TEMPORARY' and STATUS='ONLINE';
b) SQL>select tablespace_name from dba_tablespaces where contents='UNDO' and STATUS='ONLINE';
2 ) Create a script called tbsp_offline.sql script to bring tablespaces other than system, sysaux, temp and undo offline:
$ sqlplus / as sysdba; SQL>set heading off SQL>spool tbsp_offline.sql SQL>select 'alter tablespace '||tablespace_name|| ' offline;' from dba_tablespaces where tablespace_name not in ('<Give returned tablesapce name from above sql statement >');
3) Create a script called datafiles_encrypt.sql containing the commands to encrypt your datafiles, except system, sysaux, temp and undo. Include all TEMP and UNDO tablespace names from the database in your EBS instance, in the exclusion list.
$ sqlplus / as sysdba; SQL>set heading off SQL>spool datafiles_encrypt.sql SQL> select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in ('<Give returned tablesapce name from above sql statements >');
4) Now edit the tbsp_offline.sql script to remove all lines other than alter tablespace commands. run bellow script to offline tablesapces
$ sqlplus / as sysdba SQL>@tbsp_offline.sql -- it will make all the tablespace offline
5) Now edit the datafiles_encrypt.sql script to remove all lines other than alter database commands. to encrypt tablesapces (If you have a large number of datafiles, you can parallelize their encryption by creating sub-scripts and running the sub-scripts from parallel SQL*Plus sessions.)
$ sqlplus / as sysdba SQL>@datafiles_encrypt.sql - it will encrypt all the tablesapce
6) Take the offline tablespaces online (Some tablespaces may take time to show as online. These are probably tablespaces that are encrypted.)
a. Copy the file tbsp_offline.sql and call the copy tbsp_online.sql. b. Edit the tbsp_online.sql file, change the word offline to online, then save the file. c. Connect to SQL*Plus / as sysdba and run the tbsp_online.sql file to bring the tablespaces online:
Procedure for Decryption
Offline Tablespace Encryption is a reversible procedure. This section lists the steps you can use to decrypt an encrypted database.Source the Oracle E-Business Suite Database Oracle Home.
Make the encrypted tablespaces offline by running the tbsp_offline.sql script created in Step above from "sqlplus / as sysdba".
Copy the datafiles_encrypt.sql from Step above to datafiles_decrypt.sql, edit the new file, replace the word encrypt with decrypt for each of the datafiles, then save the file.
Decrypt the datafiles by running datafiles_decrypt.sql above from "sqlplus / as sysdba"
Make the offline tablespaces online by running tbsp_online.sql from Step 12 above.
Check the status of encryption using the query in Step 12d above. It should show the tablespaces as NO for ENCRYPTED COLUMN.
Close the wallet
Shut down the database from SQL*Plus:
$ sqlplus / as sysdba
SQL>shutdown
Start up the database from SQL*Plus:
$ sqlplus / as sysdba
SQL>startup
Comments
Post a Comment