When you are building and testing custom reports in Oracle Cloud BI, often important information is inadvertently included, such as Social Security Numbers or birth dates. When you are working on these reports, it is typical for the developer to either not include or hardcode “XXXXX” in place of this data to prevent inadvertent disclosure. There is a simple trick that helps you to hide critical information in a development only environment but allows it to show in production.
The ASK_APPLICATION_CLOUDS table contains information regarding the current POD. We can use the TYPE column = ‘DEVELOPMENT’ to determine whether the code is executing against a development or production pod.
So, by example,
SELECT eeid , nvl(SSNOVER, ssn) ssn , last_name , first_name , middle_name FROM ( SELECT DISTINCT nvl(ppf.person_number, '') AS eeid , nvl(replace(nid.national_identifier_number, '-', ''), '') AS ssn , nvl(ppn.last_name, '') AS last_name , nvl(ppn.first_name, '') AS first_name , nvl(substr(ppn.middle_names, 1, 1), '') AS middle_name , (select 'XXXXXXXXX' from ASK_APPLICATION_CLOUDS where TYPE = 'DEVELOPMENT') AS SSNOVER FROM per_all_people_f ppf , per_national_identifiers_v nid WHERE ppf.person_id = nid.person_id AND nid.national_identifier_type = 'SSN' AND nid.legislation_code = 'US' )
The above query, when used in a Data Model, will only show the actual Social Security number of the employee in a Production environment and not in Development. Hopefully this quick little tip will help you streamline your working processes while making your data more secure.
If there are any topics you would like to hear our Oracle experts talk about, or questions you might have about optimizing your system and your use of it, send us a message. We would be happy to answer your questions and post more content that you’ll find useful and interesting.