eBusiness Suite Data Masking/Data Scrambling/Data Anonymization

eBusiness Suite Data Masking/Data Scrambling/Data Anonymization

Recently I got a chance to work on a eBusiness Data Masking requirement. Generally, few members try to differentiate between Data Masking, Data Scrambling and Data Anonymization, unfortunately that is bit misconception.

Definition what Oracle Provides:
Data masking, also known as data scrambling or data anonymization, is the process of obscuring sensitive information copied from a production database with realistic, scrubbed data based on masking rules, to a test or non-production database

I would like to share some learnings from this masking exercise:

1. FND_USER_MASKING_EXEMPTIONS/Exempt Users From Masking

By default seeded users(like SYSADMIN, GUEST) whose USER_ID is less than 1000 are exempted from masking. But if all other usernames are masked then testing users would be impacted, so prepare the list, who need access in the eBusiness and put them in FND_USER_MASKING_EXEMPTIONS.

Data Masking Pack Provides two SQLs
fndusmaexcr.sql - Creates the FND_USER_MASKING_EXEMPTIONS table.
fndusmaexpo.sql - Provides examples of how to populate the table.

FND_USER_MASKING_EXEMPTIONS >>> Definition >>> USER_ID, SOURCE, CURRENT_NAME, NEW_NAME, NEW_PASSWD
The CURRENT_NAME column contains the name of the user to exempt from standard masking.

2. What template needs to be used, JE(Standard) or JG, is decided by the table JE_ES_MODELO_190_ALL.
select OWNER  from all_tables  where TABLE_NAME = 'JE_ES_MODELO_190_ALL';

Use the standard version of the template if your production database includes the table JE_ES_MODELO_190_ALL in the JE schema. Use the JG version of the template if your production database includes the table JE_ES_MODELO_190_ALL in the JG schema.

High Level Steps for Data Masking is as follows

1. Compile all the objects before you start Generate Mask.
2. Generate Masks Include, Patching Activity for Data Masking Template Patch, Steps for Exempt Users, Create Tables using Pre Generate SQL, Edit Masking Definition, Remove Errors/Warnings.
3. If Environment is SSO Enabled, remove SSO using txkrun.pl deregister OR Delete Directly from FND_USER_PREFERENCES. If any LDAP operation is running like DIP(like OID to eBusiness synch), then there can be deadlock in the database and ORA-00060: deadlock detected while waiting for resource would be reported.
4. Execute the Mask.
5. Save the masking script and template, so that you can run on all instances where masking is required, and after each clone.

*** If You Forget to Provide Required Grant
Provide DBMS_CRYPTO/EXEMPT ACCESS POLICY Grant to SYSTEM, missing this become problemetic, as you need to recreate various triggers, taking the code from some other instance. Otherwise masking would not happen.
GRANT EXECUTE ON DBMS_CRYPTO TO SYSTEM;
GRANT EXEMPT ACCESS POLICY TO SYSTEM;

*** Stop the application, and start the Concurrent Managers Only
Note that several concurrent requests are started during the mask (Employee Update Program and DQM Staging Program). Verify that these programs complete successfully after bringing up the Concurrent Manager. Note that the Employee Update Program may log errors of "Update of Name was not done as the update would result in a duplicate supplier record".

In the Masking execution spool, you would see like following:
Completed Data Masking. Starting cleanup phase.                                 
Starting cleanup of generated procedures                                        
Completed cleanup of generated procedures                                       
Script execution complete                                                       
Program: fndusmaexpr                                                            
Process FND_USER_MASKING_EXEMPTIONS                                             
======================================                                          
... setup context.                                                              
... process records.                                                            
======================================                                          
End program - successful                                                        
request_id = 1152548                                                            
request_id = 1152549                                                            
request_id = 1152550                                                            
EBS drop table - map_scl_dk                                                     
EBS drop table - map_scl_nl_1                                                   

Make sure all these requests are completed successfully.

*** Customisation On Data Masking Template
For Custom columns you can add in this template rule, You can remove if you do not need the seeded template to mask some columns.

*** Add Datafile to SYSTEM Tablespace
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '+DATA01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '+DATA01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

*** Add Tempfile to Temp Tablespace
ALTER TABLESPACE TEMP11 add tempfile '+DATA01' SIZE 1024M;
ALTER TABLESPACE TEMP11 add tempfile '+DATA01' SIZE 1024M;

**** Rebuild all INVALID/UNUABLE Indexes
ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD;

**** After Masking None of the Business Users are able to Access the System
If I remove the employee from the user and reset the password then that was able to access the system. You can reassign back the employee to the user. This is not a feasible solution if NONLIVE instances are accessed by many users.

"Workflow Directory Services User/Role Validation" Parameters : 100000, Yes, No, No
"Workflow Directory Services User/Role Validation" Parameters : 100000, No, Yes, No   

1. Run the following requests in the order below: 
a. "Create FND_RESP WF Roles"
b. "Sync responsibility role data into the WF table.(obsolete in R12)"
c. "Synchronize WF LOCAL tables"
d. "Workflow Directory Services User/Role Validation"

Make sure Workflow Background Process is properly Scheduled. Follow this for best practice on this:
http://mkkoracleapps.blogspot.com/2012/12/workflow-related-scheduled-concurrent.html