I have the following requirement and we are on R12.1.3 with Oracle 11gR2 database on a Linux R5 machine.
1) Senstive data such as SSN and credit card needs to masked in the oracle seeded table like per_all_people_f etc.
2) This original data should not be visible either from the database (apps user) or via the application. They should both provide the decrypted data to the users.
3) There are a set of users that need point # 2 overidden. That is, they should be able to see the decrypted data usign sql on the tables or via the application (front end).
Have anyone stumbled upon a similar requirement in their implementations ever? Appreciate some examples or any pointers..
Requirement 2 doesn't make sense to me. You say that the original data should not be visible. But then you say that both the application and the database should provide decrypted data to the users. That doesn't make sense to me. Did you mean that both should provide encrypted data to the users?
Are you trying to mask the data? Or to encrypt the data?
Does the data actually need to be masked in the table? Or could you simply use a VPD policy (or some other access control mechanism) to limit which users have access to the data?
I have no idea whether Oracle supports creating a VPD policy (or otherwise manipulating) the data in a delivered Oracle eBusiness Suite database (I'm assuming that R12.1.3 is referring to some application in the eBusiness Suite). That would be a question for Oracle Support and/or one of the eBusiness Suite forums.
My bad on point#2. It refers to the encrypted data.
I am trying to mask/encrypt the data whichever is easier. But it should enable us to obfuscate the data from the users from both database queries and from applications, except for a set of users.
Suppose SSN is masked/encrypted in its underlying table per_all_people_f, I should not be able to see the decrypted data using a query on the per_all_people_f table. Nor should I be able to see the SSN while I am logged in Oracle applications front end. But let's say we have user ABC that belongs to the priveleged group, he should be able to both query the data and see the decrypted SSN and also be able to see the decrypted SSN when he is logged in through oracle applications front end.
A VPD policy on per_all_people_f can limit what columns a particular user can see data for.
As I said before, though, I have no idea whether Oracle supports creating a VPD policy on a delivered table in a packaged application.
Your best bet is to use a de-identification tool where you can keep referential integrity across different types of data bases and does not involve any SQL or other types of coding. For the third point, I would provision a separate data bases that was not de-identified and point the application to this one (providing that you have the proper security waivers signed)