Due to data dependencies, scrambling E-Business Suite data is very time consuming and difficult task, because application functionality should work after Data Mask .
Oracle Provides Pre defined Data Masking Pack (EBSMaskingPack 2.0.7) it is Certified with EBS R12.2 , We can easily do Data Masking on Test environment using OEM 13 C with Pre defined Data Masking Pack.
Data Masking Template
certified with EBS R12.2 does the following.
De-identifies the data:
Scrambles identifiers of individuals, also known as
personally identifiable information (PII). Examples include information
such as name, account, and address.
Masks sensitive data:
Masks data that, if associated with PII, would cause privacy
concerns. Examples include compensation, health and employment
information.
Maintains data validity:
Provides a functional application.
Below Steps Demonstrate How to Mask -Oracle E-Business Suite Release 12.2 Data Masking Template with Database 12.1.0.2 with Oracle Enterprise Manager Cloud Control 13c
1. Prepare Oracle Enterprise Manager 13c
a) Ensure you have OEM 13 Cb) Ensure OEM 13 C agent is installed on Oracle Database 12.2 Database Server you plan to Mask
c) Ensure the target database to be masked is discoverable from the Oracle Enterprise Manager 13 console2. Prepare the Database
There are currently no additional database patching requirements for using the data masking templates with Oracle Database 11g Release 2 (11.2.0.4) or 12.1.
Switch the database over to using local users. Since the masking process changes the user names, you should not associate the database with LDAP, Oracle Single Sign-On (SSO), or Oracle Access Manager (OAM) during or after a mask.
3. Download Patch 30966900
The Oracle EBS template for the Data Masking and Subsetting Pack is delivered as an XML template and some PL/SQL initialization scripts. These files are delivered through a zip file in Patch 30966900.
4. Create Masking User
From this point on, you should perform the following tasks only on a cloned environment
Run Script @ebs_masking_user.sql provided by Patch 30966900 against your EBS R12 Target Database
SQL> @ebs_masking_user.sql
Enter value for password: XXXX
old 1: create user ebs_mask identified by &password
new 1: create user ebs_mask identified by XXXX
5. Pre-Genrate Step
a) Compile Invalid Database Objects against your EBS R12 Target Database
select count(*) "nInvalidBefore", to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "TimeStamp"
from DBA_OBJECTS where STATUS = 'INVALID'
/
exec sys.utl_recomp.recomp_parallel;
select count(*) "nInvalidAfter", to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "TimeStamp"
from DBA_OBJECTS where STATUS = 'INVALID'
/
b) As ebs_mask user run script fndusmaexcr.sql script provided by patch 30966900 against your EBS R12 Target Database, to create table FND_USER_MASKING_EXEMPTIONS table so that the generation step that follows can successfully validate the mask
c) As ebs_mask user run script ebs_pre_generate.sql script provided by patch 30966900 against your EBS R12 Target Database, temporarily create tables required so that the generation step that follows can successfully validate the mask.
6. Generate the Mask
Task 1: Importing the ADM Template and Verifying it
In Oracle Enterprise Manager, navigate to the Enterprise menu > Quality Management > Application Data Modeling.
Import the XML Application Data Model (ADM) template provided by patch 30966900
PS : There are Two type of ADM template provided by patch 30966900, you need to check your EBS Database witch template you need to use
select OWNER from all_tables where TABLE_NAME = 'JE_ES_MODELO_190_ALL';
if output is JE then you need to use standard type of ADM Template (ADM_EBS12.2_V2.0.X_EM_13c_Template.xml)
if output is JG then you need to use standard type of ADM Template (ADM_EBS12.2_JG_V2.0.X_EM_13c_Template.xml)
a) Open OEM Console Enterprise menu > Quality Management > Application Data Modeling.
b) Import ADM Template
c) Verify Template as EBS_MASK user
Task 2: Importing the Data
Masking template
Import Data Masking Template ,
There are Two type of Data masking template provided by patch 30966900, you need to check your EBS Database witch template you need to use
select OWNER from all_tables where TABLE_NAME = 'JE_ES_MODELO_190_ALL';
if output is JE then you need to use standard type of ADM Template (Mask_EBS12.2_V2.0.X_EM_13c_Template.xml)
if output is JG then you need to use standard type of ADM Template (Mask_EBS12.2_JG_V2.0.X_EM_13c_Template.xml)
- In Oracle Enterprise Manager, navigate to the Data Masking Definitions page. Import the XML Masking Template, selecting the Application Data Model (ADM) from the list of available ADMs created earlier. You may optionally enter a new name for the mask.
2. As the EBS_MASK user, edit
the masking definition (it may take a few minutes to open). The UI reports any
columns that do not exist in the associated database. Note these columns and
remove them from the masking definition
Task 3: Generating the Masking Script
Generate the script as the EBS_MASK user, choosing Mask In-Database for the Script Generation Options.
7) Execute the Mask
1) Ensure there is enough free space in the TEMP and SYSTEM tablespaces to accommodate 1-2 times the largest table being masked.
2) Install the predefined masking formats as the EBS_MASK user
You need to Copy below packages from OEM Application Server to Target EBS R12 Database Server .
Location of the package @OEM Application Server
Below SQL run against OEM Database to identify location of the Packages
SQL> select PLUGIN_HOME from gc_current_deployed_plugin where plugin_id='oracle. sysman.db' and destination_type='OMS';
PLUGIN_HOME
--------------------------------------------------------------------------------
/U01/app/oracle/middleware/plugins/oracle.sysman.db.oms.plugin_13.2.1.0.0
Run below Package on EBS R12 Database as EBS_MASK user - dm_fmtlib_pkgdef.sql
- dm_fmtlib_pkgbody.sql
3) Run the fndusmaexcr.sql script as the EBS_MASK user to create the FND_USER_MASKING_EXEMPTIONS table
It is necessary to exempt some users from data masking because if all user names are masked randomly, no username will be available for testing after masking. however, that exempt users are still masked in the associated applications tables
fndusmaexcr.sql contain how to insert exemptions users
4) Shutdown EBS Application Tier
5) In OEM Console Run Masking JOB for the script generated previously using the EBS_MASK user.
PS : Check "The selected target is not a production database" option on the schedule masking job page.
6)
Rerun 'Compile the objects in the database
select count(*) "nInvalidBefore", to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "TimeStamp"
from DBA_OBJECTS where STATUS = 'INVALID'
/
exec sys.utl_recomp.recomp_parallel;
select count(*) "nInvalidAfter", to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "TimeStamp"
from DBA_OBJECTS where STATUS = 'INVALID'
/
7) Start EBS R12 Application
8) Post-Masking Steps
You should now test the mask for any functional issues.
Comments
Post a Comment