Display encrypted information on OAC only

Question

Summary

We have an encrypted column in the Oracle database (12.2.0.1) . Based upon user login in OAC, we would like to display data to selected users. rest should see retracted information. Any idea how to achieve this in OAC

Version (include the version you are using, if applicable):

OAC 6.3

SiddharthDang-Oracle · Answer

@Sahithi Kolasani-Oracle We don't want decoded data to be able at the database level. Only specific users should be able to see data through OAC only. Once encrypted not even admin should be able to decrypt data.

Can we pass a password-like value from OAC to showcase decrypted results? else everything will remain encrypted only at DB level

Sahithi Kolasani-Oracle · Answer

Hi SiddharthDang-Oracle

Do you have encrypted and decrypted data in different columns of the same table/different tables?

If yes, we can write an opaque view in the physical layer something like this

select * from decoded_table_data where upper(user_name)=UPPER(:USER)

union all

select * from encoded_table_data where upper(user_name)<> UPPER(:USER)

This is going to give decrypted data only for the logged in user where as it gives encrypted data for other rows.

Please note it's just a pseudocode. Request you to change the code as per your table/column definitions.

Let me know your views.

Thank you!