I have a one row table that stores settings data, of which some of the data is sensitive and should always be redacted, including from the schema owner. One example of this is a password column .
The problem is that a stored procedure that queries the table needs some of the fields with sensitive info, including the password field, in order to perform the rest of the operations that it is intended for. The procedure will be run from a page process in Application Express.
My main question is : How can I create a redaction policy or other security feature that can hide/mask sensitive data from everyone, ( including myself) but make it accessible when called from a specific Application Express process, in this case the stored procedure mentioned above. I have encryption on the columns but can still read the actual data when I am logged in and would like it masked at all times, just in case someone gets access to my schema. Would like some of the fields to just read xxxxxxxx or something of that nature, but still be accessible to a query within an authorized application processes.
Also tried password hashing, but that is a one way streak ; I'm yet to find a way to query and unhash a stored password without providing one to compare the hashed stored password with. If you are wondering why I would need this functionality, it is because we are working in an environment in which one person has access to the sensitive information, but wants the processes to run seamlessly among different users and different sessions so we need to store access information like passwords and ID's in one place so that other users authorized within the environment can run the same procedure without the need of the person in charge to enter the password and other sensitive information for each person, for each session.
The one row setting table looks something like this
create table sensitive_data (col1 varchar2(100) Encrypt Salt,
col2 varchar2(100) Encrypt Salt);
--The Procedure is something like this;
create procedure Run_Apex_Process
----USES SENSITIVE DATA IN sensitive_data TABLE to run other operations
select col1 into v_col1 from sensitive_data;
----call other procedures using v_col1
Thanks in Advance.