I'm being asked to provide a solution for my internal customer. All network communication is internal and no applications nor their servers are accessible via the internet.
- REQUESTOR application will have a list of SSNs for 1000+ people that they need information for (different list of 1000+ each day).
- REPORTER application can run SQL queries and provide formatted output.
- DATAOWNER application has the information needed, stored in a RDBS, including a plaintext SSN column.
REQUESTOR cannot directly request the info from DATAOWNER. The only way REQUESTOR has to request information is via a URL to REPORTER. That's where I come in.
I have no ability to change the capabilities/configuration of either the REQUESTOR nor the DATAOWNER applications, other than giving info to the REQUESTOR, on how to prepare and format the URL.
I can create a report and configure REPORTER to accept a URL like
https://REPORTER/TheReport?argument1=123456789
I don't want the SSN on the URL in plaintext though, as it will be logged in REPORTER's web logs and who knows where else.
My idea for a solution is to have REQUESTOR do a SHA256 hash of a concatenation of the SSN and a secret value that changes periodically, and use that digest on the URL. When REPORTER receives the request, it performs a SQL query against DATAOWNER like (pseudocode):
select [DesiredFields] from table
where SHA256FUNCTION(CONCATENATE([SSNcolumn],<TheSecretValue>)) = $argument1.
I believe this doesn't add any risk of exposing SSNs, as all processing using the plaintext values occurs on servers that already have access to them in plaintext. REPORTER application never sees the plaintext SSN directly, though it does have to pass the output of the SQL query, which may or may not include the SSN. However, due to REPORTER's software design, no query results nor formatted output is stored.
While I'm familiar with some aspects of crypto, I'm definitely no expert. I'd appreciate any comments on this approach, either good or bad.
Thank you
