7. Data Privacy Option for Oracle E
Contents
1. 60 Oracle E Business module mask the data and insert the data into the target database Module Object Object Name Core Human Resources Access Definition ADCHR11510DP Management Systems HRMS Extract Request ERCHR11510DP Insert Request IRCHR11510DP Table Map TMCHR11510DP Column Map CMPHN11510DP CMADR11510DP CMCHR11510DP e Access Definition An Access Definition identifies the Start Table related tables primary keys relationships and selection criteria that define the data you want to extract from your source database e Extract Request An Extract Request initiates the Extract Process which allows you to copy a set of related rows from one or more tables and stores this data in an Extract File The Extract Request references an Access Definition that defines the data to be extracted e Insert Request An Insert Request initiates the Insert Process which copies data from a source file into specified destination tables The Insert Request specifies an Extract File containing the data you want to insert into your target database and the parameters needed to run the process The Insert Request references a Table Map to map the destination for the data to be inserted e Table Map A Table Map defines specifications for correlating source and destination tables of compatible data A Table Map can map tables that have different names modify table names Optim TDM and DP Solutions2. Default Destination Qualifier window Close the Table Map Editor and save changes 7 On the Insert Request Editor General tab select Insert in the Process Options group box when you request the first Insert for a module For subsequent Insert Requests select Update Insert 8 Inthe Disable Triggers and Disable Constraints group boxes select Always 9 Select File gt Run to insert data from the Extract File into your target database Installation and Configuration Guide 67 7 Data Privacy Option for Oracle E Business Suite 10 When the Insert Process is complete you can view the results in the Insert Process Report 11 Close the Insert Request Editor 7 3 4 Verify Masked Data Be sure to verify that your sensitive data has been successfully masked Use Optim Compare or another method to confirm that the values in your target database have been replaced with meaningful yet fictional data For more information on using Optim Compare refer to the Optim Compare User Manual 68 Optim TDM and DP Solutions for Oracle E Business Suite 7 4 Scenario Core HRMS Module 7 4 Scenario Core HRMS Module The following scenario illustrates how to use the Data Privacy option in conjunction with the Test Data Management Solution for Oracle E Business Suite The scenario assumes that you have already installed Optim and configured the DB Aliases used in this scenario as described in Chapter 2 Installing Optim The s
3. This column is set from a list of town or city names Zip PER_ADDRESSES This field is masked as a valid value based on City and State Installation and Configuration Guide 83 7 Data Privacy Option for Oracle E Business Suite Column Name County Phone Number License Number Work Phone 7 5 4 Data Privacy Objects Control Table Table Name PER_ADDRESSES PER_ADDRESSES PER_PHONES PER_ALL_PEOPLE_F PER_ALL_PEOPLE_F Masking Type Depending on the City State and Zip Code combination the county value is populated using the algorithm or lookup values Phone numbers are replaced with realistic random data If the source values are populated then a random number or null is replaced on the target database There is no Oracle application integrity built on this column value Phone numbers are replaced with realistic random data The OPTIM_EBS_DP_LKP_ CONTROL table is the control table which allows the end users to control the Source and Destination Lookup tables Following is the structure of the table Column Name SEQ MODULE NAME TABLE_NAME 84 Data type NUMBER VARCHAR 2 3 VARCHAR2 60 VARCHAR2 30 Default NOT NULL NOT NULL NOT NULL Column Description Unique identifier Module name CHR Table information Name of the table Optim TDM and DP Solutions for Oracle E Business Suite 7 5 Data Privacy Reference Material Column Name QUICKCODE CCODE ACTIVE
4. and Addresses in the TABLE_NAME column the values in the SQLTEXT column must also be changed to reflect the modifications If the ACTIVE column value for a row is set to N then that table will not be used in a master lookup table or materialized view By default the Data Privacy option masks e mail addressees by generating a user name from the first initial and last name of the masked entry from the name lookup The default domain name is xyzco com To make changes to the default settings you must manually edit the control table Optim TDM and DP Solutions for Oracle E Business Suite
5. from Gold Master Database Production Database Gold Master Database 1 Clone and Truncate 2 cant A a 1 Clone Production Target Database 2 Extract 3 Insert 3 Insert 4 Reset Extract with DP Extract Sequences File Optional File l To set up a masked Gold Master database to use as the source for your target environment perform the following steps 1 Create a clone of the production database 2 Extract the data to be privatized from the clone by running an Optim Extract Request 3 Insert the extracted data into the cloned database using the data privacy definitions in the Data Privacy Column Map This step creates your Gold Master database Installation and Configuration Guide 57 7 Data Privacy Option for Oracle E Business Suite 58 You can use the Gold Master database as your secure source for your target environment To create a target environment perform the following steps 1 Clone the masked Gold Master database to a target database 2 Extract the masked data from Gold Master using the Optim Test Data Management Solution for Oracle E Business 3 Insert the extracted data into the target database 4 You can then run truncate scripts to reduce the database size and run reset scripts to reset the target database sequences optional Note For more information on truncating your database see Appendix B Truncating Target Ta
6. source and you can generate values with a length different from the source The source data format and character type determines the format and character type of the destination data If the source data is uppercase alphabetic characters the output generated at the destination is uppercase alphabetic For more information on the TRANS COL function see the TRANS COL Data Privacy Function support document at the following site http www 01 ibm com support docview wss rs 0 amp context SSCGNPF amp context SSCGNPH amp co ntext SSC253J amp dc DB550 amp dc DB560 amp uid sw g21424217 amp lo c en_US amp cs utf 8 amp lang 80 Optim TDM and DP Solutions for Oracle E Business Suite 7 5 Data Privacy Reference Material 7 5 2 Oracle HRMS Database Tables Below are the Oracle Core HRMS Schema tables that contain the personal information that is masked using the Data Privacy option e PER _ALL PEOPLE _F e PER PHONES e PER_ADDRESSES PER_ALL_PEOPLE_F The PER_ALL_PEOPLE_F table holds personal information for individuals associated with the HR department Individuals include employees applicants ex employees ex applicants contacts and other site specific categories of people PER_PHONES The PER_PHONES table holds phone numbers for current employees former employees and other contacts PER_ADDRESSES The PER_ADDRESSES table holds address information for current and ex employees and other contacts See Oracle documentation for
7. 1 xF 7 o Control File JIRCHRI 150DP lt 5EQ4 gt CF 7 H El Table Map Options m Process Options _ p Disable Triggers C Local Named Insert Always Table Map Name C Mixed Never A450AE TMCHR11510DP aja C Update Only Prompt Update Insert T Always View Table Map I Lock Tables Disable Constraints 4 e Options IV Process File Attachments all Tables Delete Commit Commit Frequency Prompt C Mixed After Each Table No Tables On Completion Discard Row Limit I Always Call Create 0 Ready Z 4 Enter a Control File name or select an existing one 5 Inthe Table Map Options group box select Named Click the browse button to open the Select a Table Map window In the Identifier pane double click the identifier associated with the Table Map A4SOAE Double click the grid row for the appropriate Table Map see the table in Section 7 2 3 Create Data Privacy Objects on page 63 to populate the Table Map name on the Insert Request Editor window Installation and Configuration Guide 77 7 Data Privacy Option for Oracle E Business Suite 11 On the Insert Request Editor select Tools gt Edit Table Map to open the Table Map Editor Click the Destination Qualifier down arrow and select the qualifier for tables in your target database You can also click the browse button to open the Select a Default Destination Qualifier window Close the Table Map Editor and save changes On
8. 7 Data Privacy Option for Oracle E Business Suite This section contains information on using the Optim Data Privacy option in conjunction with the Optim Test Data Management Solution for Oracle E Business Suite The data privacy option enables you to mask sensitive data on non production instances of an Oracle E Business database including test training and development environments The data privacy definitions provide the means to easily de identify mask and transform sensitive data in the HRMS module of Oracle Applications version 11 5 10 If your current Test Data Management configuration does not include the Data Privacy option and you would like that functionality contact your IBM sales representative This chapter includes the following topics e Data Privacy Overview e Installing the Data Privacy Option e Data Privacy Implementation e Scenario Core HRMS Module e Data Privacy Reference Material Installation and Configuration Guide 53 7 Data Privacy Option for Oracle E Business Suite Prerequisites The Data Privacy option is an added feature of the Test Data Management Solution and has the same hardware and software requirements as outlined in Section 1 1 Hardware and Software Requirements For more information see the Detailed System Requirements document for the Test Data Management Solution for Oracle E Business Suite at the following site http www ibm com support search wss tc SSC4TGP amp sort de
9. DE Y 4005 CHR ES Addresses OPTIM_ES ADDRESSES DA ES Y 4007 CHR FR Addresses OPTIM_FR_ADDRESSES DA FR N 4006 CHR IT Addresses OPTIM_IT ADDRESSES DA IT Y 4000 CHR US Addresses OPTIM_US_ ADDRESSES DA US Y 9001 CHR E mail Format OPTIM_EBS_DP_NAMES DE Y _LKP_MASTER 9002 CHR Known As OPTIM_EBS_DP_NAMES DK Y Format _LKP_MASTER The Optim Data Privacy option will lookup data from these target tables and mask your source table data The control table also inserts an SQLTEXT column To find the existing values for the SQLTEXT column use the following command SQL gt Select Table_Name SQLTEXT from OPTIM_EBS_DP_LKP_CONTROL Lookup Tables Data in the name and address lookup master tables are loaded by executing the SQL string in the Control Table The names lookup master table OPTIM_EBS_DP_NAMES_LKP_ MASTER has the following structure Column Name Data type Default Column Description NID VARCHAR2 30 Identifier The last digit Indicates gender M Male F Female LASTNAME VARCHAR2 100 Last Name FIRSTNAME VARCHAR2 30 First Name E_ CODE VARCHAR2 3 US Ethnicity Code AU CA FR IT DE ES US GB ETHNICITY VARCHAR2 50 American Ethnicity Australian Canadian French Italian German Spanish American UK 86 Optim TDM and DP Solutions for Oracle E Business Suite 7 5 Data Privacy Reference Material The address lookup master table OPTIM_EBS_DP_ADDRD_LKP_MASTER has the following structure Column Name SEQ ADDRESS_LINE1 ADD
10. Open 2 Inthe Identifier pane on the Open window expand Extract Request and double click the identifier associated with the import file A4SOAE Double click the grid row for the Installation and Configuration Guide 65 7 Data Privacy Option for Oracle E Business Suite 9 appropriate Extract Request Refer to the table in Section 7 2 3 Create Data Privacy Objects on page 63 On the Extract Request Editor General tab enter a name for the Extract File or use the specified default Verify that the correct Access Definition is specified Refer to the table in Section 7 2 3 Create Data Privacy Objects on page 63 Select Tools gt Edit Access Definition to open the Access Definition Editor Ensure Default Qualifier contains the correct DB Alias and Creator ID for your source database Close the Access Definition Editor On the Extract Request Editor Objects tab verify that only Objects are selected not Extended Objects Select File gt Run to create the Extract File On the Extract File Variable Values dialog enter values to identify the selection criteria that Optim will use to extract user data Set the boundaries for the Person IDs that will be used to generate privatized data from the view Set the upper and lower limits of the PERSON_FROM and PERSON_TO variable values When the Extract Process is complete you can view the results in the Extract Process Report Close the Extract Request Editor 7 3 3 Mask a
11. RESS_LINE2 ADDRESS_LINE3 CITY COUNTY STATE POSTAL_CODE COUNTRY Note Data type NUMBER VARCHAR2 100 VARCHAR2 100 VARCHAR2 100 VARCHAR2 100 VARCHAR2 100 VARCHAR2 4 VARCHAR2 16 VARCHAR2 2 Default CA US Column Description Identifier Address Line 1 Address Line 2 Address Line 3 Town or City County For US Address only State For US Address only Zip Code Two digit Country Code There are city county state combinations in Optim Data Privacy tables which do not exist as combinations in Oracle If addresses are masked with an invalid combination you will get a note error message in the Addresses Form The error can be avoided by selecting appropriate combination of city county and state Modifying the Control Table For greater customization you may modify the values of the control table To manually change the values run the following processes in sequence 1 2 3 Insert data into control table Insert data into the Names Master Lookup table Insert data into the Address Master Lookup table 4 Create materialized views Installation and Configuration Guide 87 7 Data Privacy Option for Oracle E Business Suite 88 Notes While making changes to the control table remember that the value for MODULE is always CHR for HRMS Data The value in the CCODE should match the Oracle Country Code If you manually change any target table names for Names
12. SQLTEXT DESCRIPTION SEQ Mod 1001 CHR 1002 CHR 2001 CHR 2002 CHR 2003 CHR 2004 CHR 2005 CHR 2006 CHR 2007 CHR 2008 CHR 4001 CHR 4002 CHR Data type Default VARCHAR2 10 VARCHAR2 30 CHAR 1 NOT NULL Y VARCHAR2 4000 NOT NULL VARCHAR2 100 Column Description Source destination indicator also indicates whether name or address SN Source Name DN Destination Name SA Source Address DA Destination Address DK Destination Known As DE Destination Email Address Country Code 2 Digit should match with Oracle Country Code Y N value to enable disable the use of the particular table SQL string Description additional information By default the Data Privacy option inserts the following rows in the Control Table NAME TABLE_NAME Names Source PER_ALL_PEOPLE_F Address Source PER_ADDRESSES AU Names OPTIM_AU_LASTNAME CA Names OPTIM_CA_LASTNAME DE Names OPTIM_DE_LASTNAME ES Names OPTIM_ES_LASTNAME FR Names OPTIM_FR_LASTNAME IT Names OPTIM_IT_LASTNAME UK Names OPTIM_UK_LASTNAME US Names OPTIM_US_LASTNAME AU Addresses OPTIM_AU_ADDRESSES CA Addresses OPTIM_CA_ADDRESSES Installation and Configuration Guide QUICK CCODE ACTIVE CODE SN Y SA Y DN AU Y DN CA N DN DE N DN ES Y DN FR Y DN IT Y DN GB Y DN US Y DA AU Y DA CA Y 85 7 Data Privacy Option for Oracle E Business Suite 4003 CHR UK Addresses OPTIM_UK_ADDRESSES DA GB Y 4004 CHR ODE Addresses OPTIM_DE_ADDRESSES DA
13. accesses multiple lookup tables and creates a view with data from different geographies As with masking names Optim matches the unique Oracle IDs from the various source tables and the destination table The resulting view maintains geographically realistic address data that will be used in masking your data The Data Privacy option provides a script in the sql folder of the installation directory to create the objects that are necessary for using the option Installation and Configuration Guide 63 7 Data Privacy Option for Oracle E Business Suite 64 Use SQL Plus to run the create_optim_ebs_dp_objects sql script and create Data Privacy objects including control tables lookup tables and materialized views The script creates the following objects Object Type Object Name Table OPTIM_EBS_DP_LKP_CONTROL Table OPTIM_EBS_DP_NAMES _LKP_MASTER Table OPTIM_EBS_DP_ADDRS_LKP_MASTER Package OPTIM_EBS_DP_LKP_PKG Package Body OPTIM_EBS_DP_LKP_PKG Procedure INSERT_CNTRL_DATA Procedure INSERT_NAMES_DATA Procedure INSERT_ADDRESSES_DATA Procedure CREATE_MVIEWS Materialized View OPTIM_EBS_DP_NAMES_LKP_MV Materialized View OPTIM_EBS_DP_ADDRESSES_LKP_MV Many of these objects can be customized to meet the particular data privacy needs of your organization See Data Privacy Objects on page 84 for more information on customizing data privacy objects With the Data Privacy objects set up you can now implement the Data P
14. asking technique that alters the column value using a Default Adjustment Update the value by using the following the Age Function AGE DEF RU NEXTPAYDAY A4SOAE CMCHR11510DP Column Map Editor lol x File Edit Tools Options Help allaa sa Blea ae Source Destination Extract File D 1BM datalpeople_address _phones_25_beforec Table Name Table Name PSTAPP HR PER_ALL_PEOPLE_F PSTAPP HR PER_ALL_PEOPLE_F Description Procedure ID Server Name Column Map for HR People 11510 Local PERSON_ID 2 EFFECTIVE_START_DATE 3 EFFECTIVE_END_DATE 4 BUSINESS_GROUP_ID __5 PERSON_TYPE_ID __B LOOKUP PERSON_ID DEST LAST_NAME APPS OPTIM_EBS_DP_NAMES_LKP_MV SEQ SOURCE LA START_DATE __ 8 APPLICANT_NUMBER 9 COMMENT_ID 40 CURRENT_APPLICANT_FLAG 44 CURRENT_EMP_OR_APL_FLAG _12 CURRENT_EMPLOYEE_FLAG 43 DATE_EMPLOYEE_DATA_VERIFIED teady ia Ni 6 Save and close the Column Map Editor Installation and Configuration Guide 75 7 Data Privacy Option for Oracle E Business Suite 7 4 3 Insert the Extract File Once you have your Extract File use Optim to insert the HRMS personal data into the target database using the Data Privacy definitions 1 On the Optim main window select File gt Open 2 Inthe Identifier pane on the Open window expand Insert Request and double click the identifie
15. b clear Use new relationships Note the Status value for each listed relationship e Blank indicates that the relationship is defined and not new Optim TDM and DP Solutions for Oracle E Business Suite 7 2 Installing the Data Privacy Option e New indicates that the relationship is new e Unknown indicates that the relationship is not defined because the table has been deleted from the database or Optim Directory or the Default Qualifier on the Tables tab has been changed You can change the Default Qualifier dbalias tableowner to ensure the dbalias portion matches the DB Alias for your source database You can also import the Optim definitions again for the source DB Alias e Ref indicates that the relationship is ignored because the parent or child table is specified as a reference table on the Tables tab 7 Close the Access Definition Editor 7 2 3 Create Data Privacy Objects To mask name and address columns the Data Privacy option first creates master lookup tables with realistic yet fictionalized entries from the sample data that was loaded during Optim installation The Data Privacy option uses a view of these lookup tables as the foundation for lookup based masking functions To mask name entries Optim matches unique Oracle ID numbers from the master lookup table and the destination table Optim uses the corresponding values from the lookup table to create a view To mask address entries Optim
16. bles on page 123 For more information on resetting database sequences see Resetting Sequences on page 31 Optim TDM and DP Solutions for Oracle E Business Suite 7 2 Installing the Data Privacy Option 7 2 Installing the Data Privacy Option Before you can successfully mask the Core HRMS data you must properly install and configure the Optim Data Privacy option Perform the following steps 1 Install and configure the Test Data Management Solution 2 Import data privacy definitions 3 Create data privacy objects 7 2 1 Install and Configure the Test Data Management Solution Before you can use the Data Privacy option you must install and configure Optim as described in Chapter 2 Installing Optim The Data Privacy option uses many of the Optim Data Privacy tables These tables must be loaded when configuring the first workstation 7 2 2 Import Data Privacy Definitions The Optim Data Privacy option installation includes an import file that contains the Optim data privacy definitions needed to extract and mask Core HRMS data from your Oracle E Business source database and insert the data into your target database Import the file into the Optim Directory and verify that the definitions are imported successfully The import file contains the following definitions which are needed to extract the complete business object defined by the Installation and Configuration Guide 59 7 Data Privacy Option for Oracle E Business Suite
17. cenario also assumes you have the Data Privacy option as part of your Test Data Management Solution configuration and have created a target database with a subset of your Core HRMS records The personal information you will extract from your database was created by the HRMS module This scenario includes the following steps 1 Extract Core HRMS data from the source database 2 Edit the Optim Data Privacy definitions 3 Mask and insert data in the target database Installation and Configuration Guide 69 7 Data Privacy Option for Oracle E Business Suite 7 4 1 Extract Core HRMS Data from the Source Database Once you have set up the environment use Optim to extract Core HRMS personal data from your source database 1 On the Optim main window select File gt Open 2 Inthe Identifier pane on the Open window expand Extract Request and double click the identifier associated with the Extract Request A4SOAE Double click the grid row for the Extract Request ERCHR11510DP to open the Extract Request Editor amp Open an Extract Request oj xj Identifier Extract Request 4 Access Definition Identifier Secured Modified By Modified Date gt 4 ote ria pie ep Archive Request E Column Map ERCHR11510DP PEB1151Olioracle 36 2010 4 37 43 PM Extrac al Column Map Proc Ba Compare Request amp By Convert Request a DB Alias Fail Delete Request Z Edit Definition Eff Extract Request C ERE ic Insert Request Load R
18. e Column Map lookup function populates the destination column based on a view that is created from the source table using an SQL script The Lookup Function selects values based on the entries in the source table Hash Lookup Function The Hash Lookup Function obtains the value for a destination column from a lookup table according to a hashed value derived from a source column The Hash Lookup Function allows you to consistently mask data using the same source and lookup tables in any environment AGE Function The Age Function is used to age values in a source column The source column can contain character numeric date or timestamp data Note that a CHAR or VARCHAR column has a maximum Installation and Configuration Guide 79 7 Data Privacy Option for Oracle E Business Suite length of 256 bytes The Age Function is formatted as AGE parameters TRANS COL Function The TRANS COL function can mask data that has no inherent format or a format that is not widely known TRANS COL maintains the format and character type of the source data at the destination Data types CHAR VARCHAR and non float numeric can be masked with TRANS COL Alphabetic and numeric characters are masked but any other characters in the source data are copied to the destination without being changed You can generate unique values for unique source data For non unique source data you can generate a different value for each occurrence of the same
19. equest E E Primary Key ic Tp Relationship 23 Report Request Restore Request EG Table Map ij Enter pattern for Extract Request IDENTIFIER NAME A450AE X Cancel Refresh Help m Double click on an item to display its contents ZAI 70 Optim TDM and DP Solutions for Oracle E Business Suite 7 4 Scenario Core HRMS Module 3 On the Extract Request Editor General tab enter a name for the Extract File or use the specified default Verify that the correct Access Definition is specified ADCHR11510DP Click the browse button to select the Access Definition name File Edit Tools Options Help A4SOAE ERCHR11510DP Extract Ri Editi lol x Blslalal al Ale alale mele Description Extract Request for HRMS DP 11510 Server Name Local General Objects Point and Shoot Variables Object List Notify Extract File ERCHR1150DP lt 5EQ4 gt XF z E r Access Definition Options Local Named Access Definition Name A450AE ADCHR11510DP kd a m Items to Extract Row Limit T Run Convert After Extract C Data IV Compress Extract File Objects Database Connections Iv Generate Statistical Report Both fi IV Process File Attachments Ready 5 A 4 Select Tools gt Edit Access Definition to open the Access Definition Editor Change the Default Qualifier to the correct DB Alias and Creator ID for your source database 5 On the Extract File Variable Valu
20. es dialog enter Person ID values for PERSON_FROM and PERSON_TO The range Installation and Configuration Guide 71 7 Data Privacy Option for Oracle E Business Suite 72 between the two values should equal or exceed the number of entries being masked A4SOAE ERCHR11510DP Extract Request Editor File Edit Tools Options Help ejslala 6 AB alale Melel Description Extract Request for HRMS DP 11510 Server Name Local General Objects Point and shoot Variables object List Notify Prompt String Value Enter Person ID From 1 Enter Person ID To 14000 PERSON_FROM PERSON_TO Select File gt Run or click the Run icon to create the Extract File Disregard any warnings about NEW relationships When the Extract Process is complete you can view the results in the Extract Process Report Close the Extract Request Editor Optim TDM and DP Solutions for Oracle E Business Suite 7 4 Scenario Core HRMS Module 7 4 2 Edit the Data Privacy Definitions Before inserting your data edit the Optim HRMS definition for privatizing age information using the Column Map Editor 1 On the Optim main window select Definitions gt Column Map 2 On the Column Map Editor window select File gt Open 3 Select the Column Map with PER_ALL_PEOPLE_F in the Table column open a Column Map ioj x Identifier Column Map Bal Dest Source Secured DBAlia
21. for Oracle E Business Suite 7 2 Installing the Data Privacy Option exclude tables from a process or include Column Maps for greater control over the data Table Maps can be used in a Convert Insert or Load process or with the Create Utility Column Map A Column Map defines the specifications for mapping columns of compatible data between source and destination tables You can map columns that have different names modify data or exclude columns from a process You can include one or more Column Maps in a Table Map when you create a request to compare convert insert load or restore data Import the Optim Data Privacy Definitions Use the following procedure to import the data privacy definitions into your Optim directory 1 Zs In the Optim main window select Utilities gt Import In the Import window Process tab click the Input File browse button to open the Open window Open OAHRCHR11510DP txt from the Import gaAD folder Select all available check boxes in the Definitions box Select Overwrite existing definitions if definitions already exist in the Optim directory On the DB Aliases tab enter the DB Alias for your source and target databases in New DB Alias The Optim definitions include the default source DB Alias PSTSRC and target DB Alias PSTAPP Select File gt Run or click the Run icon Click the Show process log icon w to check for errors and view the status of the Import process C
22. lose the Import window Installation and Configuration Guide 6l 7 Data Privacy Option for Oracle E Business Suite Validate Imported Definitions 62 When you have finished importing the Data Privacy definitions you can verify that the definitions were imported successfully using the following procedure 1 From the Definitions menu on the Optim main window select Access Definition to open the Access Definition Editor 2 From the File menu on the Access Definition Editor select Open to open the Open an Access Definition window 3 In the Identifier box in the Open an Access Definition window double click the identifier for the Access Definition A4SOAE 4 Inthe Access Definition grid double click the Access Definition name to open the Access Definition in the editor 5 On the Tables tab note the Type value for each listed table e Table indicates that the import was successful e DB Alias Unknown indicates that the DB Alias referenced in the Access Definition does not exist Check and correct the Default Qualifier dbalias tableowner to ensure the dbalias portion matches the DB Alias you created for your source database e Unknown indicates that the table does not exist or the tableowner referenced in the Access Definition is incorrect Check and correct the Default Qualifier dbalias tableowner to ensure the tableowner portion matches the schema name for the table 6 On the Relationships ta
23. nd Insert the Extract File Data Use the following procedure to mask and insert your data into the target database 1 2 66 On the Optim main window select File gt Open In the Identifier pane on the Open window expand Insert Request and double click the identifier associated with the Optim TDM and DP Solutions for Oracle E Business Suite 7 3 Implementing the Data Privacy Option Insert Request A4SOAE Double click the grid row for the Insert Request to open the Insert Request Editor Refer to the table in Section 7 2 3 Create Data Privacy Objects on page 63 3 On the Insert Request Editor enter the Server Name On the General tab select the name of the Source File or click the Retrieve last created Source File button to retrieve the name of the last created Extract File 4 Enter a Control File name or select an existing one 5 Inthe Table Map Options group box select Named Click the browse button to open the Select a Table Map window In the Identifier pane double click the identifier associated with the Table Map A4SOAE Double click the grid row for the appropriate Core HRMS Table Map to populate the Table Map name on the Insert Request Editor 6 On the Insert Request Editor select Tools gt Edit Table Map to open the Table Map Editor Click the Destination Qualifier down arrow and select the qualifier for tables in your target database You can also click the browse button to open the Select a
24. pertinent information regarding the handling of primary addresses Installation and Configuration Guide 81 7 Data Privacy Option for Oracle E Business Suite 7 5 3 Oracle HRMS Columns for Masking The following columns in the Core HRMS tables will be considered for masking Column Name Table Name Last Name PER_ALL_PEOPLE_F First Name PER_ALL_PEOPLE_F Middle Name PER_ALL_PEOPLE_F Known As PER_ALL_PEOPLE_F Previous Name PER_ALL_PEOPLE_F Email ID PER_ALL_PEOPLE_F Employee Number PER_ALL_PEOPLE_F National Identifier SSN PER_ALL_PEOPLE_F Masking Type Values are masked with a meaningful Last Name from the Lookup Table Values are masked with a meaningful First Name from the Lookup Table If the source columns are populated the new value is randomly picked name or initial from the Lookup View based PERSON_ID Values are randomly substituted only if source is not null If the record qualifies for Previous Name non null values from the source data are populated from Lookup values Lookup values are populated based on person ID and the new masked name assigned to the non production environment User can determine the e mail suffix based on lookup values setup The Optim Random function is used to mask the data in this column Replacement values must conform to the same formatting as the original source value The masking technique will synchronize data to the EMPLOYEE_NUMBER in the PER_ALL_ASSIGNMENTS_F
25. r associated with the Insert Request A4SOAE Double click the grid row for the appropriate Insert Request see the table in Section 7 2 3 Create Data Privacy Objects on page 63 to open the Insert Request Editor max Identifier Insert Request F Access Definition f Archive Request Column Map Column Map Proc Compare Request By Convert Request a DB Alias 367 Delete Request A Edit Definition ff Extract Request Insert Request BH Load Request E Primary Key i Relationship z Identifier Modified By Modified De E IRCHR1 1510DP Q92EBS51 OTDMBioracle 3 16 2010 11 09 ct Enter pattern for Insert Request IDENTIFIER NAME A450AE 7 Cancel Refresh Help rea Double click on an item to display its contents 7A 3 On the Insert Request Editor enter the Server Name On the General tab select the name of the Source File or click the 76 Optim TDM and DP Solutions for Oracle E Business Suite 7 4 Scenario Core HRMS Module Retrieve last created Source File button to retrieve the name of the last created Extract File A4S0AE IRCHR11510DP Insert Request Editor 15 x File Edit Tools Options Help Blslala 4 Al ae telel Description Server Name Insert Request for HRMS DP 11510 Local General age Function Global Aging Currency Report Options Notify Source File ERCHR11500P0
26. rivacy option Optim TDM and DP Solutions for Oracle E Business Suite 7 3 Implementing the Data Privacy Option 7 3 Implementing the Data Privacy Option To implement the Data Privacy option you must set up your target database environment Once you have the proper environment in place extract data from the production database and insert the extracted data into the target database using the provided data privacy definitions The result is a privatized database with usable data 7 3 1 Set Up Environment First you must set up your Oracle E Business target database environment 1 Clone the source production Oracle E Business database to create the target database for example development testing or training This clone contains the current production data 2 Use the Test Data Management truncate scripts on the target database to truncate the tables from which you will extract data in the source database See Appendix B Truncating Target Tables Reorganize the target table spaces to reduce the size of the database 3 Ensure that the Optim Server is configured with connectivity to both the source and target databases see Chapter 2 Installing Optim Import the Optim definitions see Import Data Privacy Definitions on page 59 7 3 2 Run the Extract Request Next run an Extract Request using Optim Use the following procedure to extract data from the source database 1 On the Optim main window select File gt
27. s Creator ria pia ria JHR PER_ADDRESSES PSTAPP HR PER_ALL_PEOPLE_F PSTSRCGL GL PER_PHONES PSTAPP Enter pattern for Column Map IDENTIFIER NAME AFSOAE X Cancel Refresh Help M Double click on an item to display its contents 4 Inthe Source section of the Respecify Column Map Tables window browse for your Extract File or select the file from Installation and Configuration Guide 73 7 Data Privacy Option for Oracle E Business Suite the drop down menu Select the PER_ALL_PEOPLE_F table from the Table list and click OK A4SOAE CMCHR11510DP Respecify Column Map Tables oxi Server Name Validation Rules ilocal MoyefArchive Compare m Source File Name JERCHRI 150DP0010 XF 7 fd Table PSTAPP HR PER_ALL PEOPLE F Database Table Mame m Destination File Hame Extract Table Database Table Name PSTAPP HR PER_ALL_PEOPLE_F m a I Use Column Attributes from Source Table if Table Does Not Exist coc J e Ready a 5 Inthe Column Map Editor update row 14 with a new Data Privacy definition The default masking technique is to age a column value by 2 years 6 months 40 weeks and 15 days and apply a rule using the following Age Function 74 Optim TDM and DP Solutions for Oracle E Business Suite 7 4 Scenario Core HRMS Module AGE 2Y 6M 40W 15D RU NEXTPAYDAY Instead use a different m
28. s amp dc DA420 amp dtm 54 Optim TDM and DP Solutions for Oracle E Business Suite 7 1 Data Privacy Overview 7 1 Data Privacy Overview Every company is required to protect personal employee information such as addresses and Social Security numbers Use the Optim Data Privacy option to efficiently maintain a working database of employee information that is secure and safe to use for test development and training environments Different approaches can be used to mask data in your Oracle E Business database The following approaches involve extracting and inserting data into a target database e You can mask the extracted data from an Optim Extract Request when you insert it into your target database e When you use Data Privacy in conjunction with Test Data Management a more secure approach is to mask the extracted data when you insert data into a clone of your production database The masked clone is then used as a source for subsequent non production databases environments Installation and Configuration Guide 55 7 Data Privacy Option for Oracle E Business Suite 7 1 1 Data Privacy Implementation Architecture 56 To implement the Data Privacy option extract data from your production database and mask sensitive data as you insert it into your target database During the insert process Optim uses the provided Data Privacy definitions to mask your data Production Database C Loreto Target Database 1 E
29. table and other applicable areas This column uses the Optim Trans Col Function for U S geography data to mask government ID data 82 Optim TDM and DP Solutions for Oracle E Business Suite 7 5 Data Privacy Reference Material Column Name Table Name Masking Type Date of Birth PER_ALL_PEOPLE_F This key column is used as a unique identifier in many cases The data is masked and the validity is checked Birth dates are replaced by a range of random dates ensuring that the age is not too old or young Town of Birth PER_ALL_PEOPLE_F Standard Optim masking techniques of either Lookup or Random functions are used Region of Birth PER_ALL_PEOPLE_F Standard Optim masking techniques of either Lookup or Random functions are used Country of Birth PER_ALL_PEOPLE_F Standard Optim masking techniques of either Lookup or Random functions are used Address Line 1 PER_ADDRESSES The first line is given a meaningful yet fictional street address and the remainder is set to null unless there is a requirement for multi line street addresses Address Line 2 PER_ADDRESSES The first line is given a meaningful yet fictional street address and the remainder is set to null unless there is a requirement for multi line street addresses Address Line 3 PER_ADDRESSES The first line is given a meaningful yet fictional street address and the remainder is set to null unless there is a requirement for multi line street addresses City PER_ADDRESSES
30. the Insert Request Editor General tab select Insert in the Process Options group box when you request the first Insert process for a module For subsequent Insert Requests select Update Insert In the Disable Triggers and Disable Constraints group boxes select Always Select File gt Run to insert data from the Extract File into your target database When the Insert Process is complete you can view the results in the Insert Process Report Close the Insert Request Editor 7 4 4 Verify the Target Database Log on to Oracle E Business and access the target database Verify that the personal data has been masked Select the HRMS system and navigate to the employee information screen Verify that the HRMS data has been masked You now have a working database with privatized and usable data 78 Optim TDM and DP Solutions for Oracle E Business Suite 7 5 Data Privacy Reference Material 7 5 Data Privacy Reference Material This section covers the following information e Masking methods e Oracle HRMS Database Tables e Oracle HRMS Columns for Masking e Data Privacy Objects 7 5 1 Masking Methods Depending on the column the Data Privacy option masks sensitive data using one of several functions For detailed information on each function refer to the Optim Common Elements Manual Lookup Function For names and addresses the Data Privacy option uses lookup tables to mask information while keeping the data usable Th
31. xtract l 2 Insert Load In this approach you must first set up your test environment If you are using the Data Privacy option with the Test Data Manage ment Solution you will have already created a database clone of the production database and run scripts to truncate tables reorga nize table spaces and reset sequences in your new target database See Section 1 2 Creating an Oracle E Business Target Environ ment and Appendix B Truncating Target Tables for more infor mation on setting up your test environment With the test environment in place you can mask data using the Data Privacy option To do this perform the following steps 1 Extract the production data you will mask using an Extract Request 2 Insert the extracted data into the target database using the Data Privacy definitions provided in the Data Privacy Column Map This step masks your data Optim TDM and DP Solutions for Oracle E Business Suite 7 1 Data Privacy Overview 7 1 2 Data Privacy Implementation Architecture with TDM An alternate method for implementing the Data Privacy option masks the data in a cloned master database After masking the new database the Gold Master database is used as the source for test development or training environments This approach provides additional flexibility and security when distributing masked data across multiple non production databases Create Gold Master Database Create Target Environment
Download Pdf Manuals
Related Search