Encryption of PII data in database logic in store procedures and desktop application

I have an old desktop application (Delphi) with logic in stored procedures (Oracle). The application is storing some personal data including salaries. I want to encrypt data so someone with db access cannot check salaries of other workers. I face 2 problems:

desktop application: I cannot deliver encryption key with application because it can be easily revealed. I need to create some “proxy server” storing encryption key in middle of desktop application and sql server. This will make architecture more complex and will potentially create problems with performance.
Stored procedures: this will require a lot of changes but I this is doable with passing encryption key to store procedures from external system. Biggest problem here is that there are a lot of DB JOB and I don’t see any solution for this.

In general, I think this would take huge amount of time and probably better solution would be to rewrite whole system.

New contributor

The simple answer here is to restrict access to the data at the DB level in Oracle. This assumes that you have a distinct oracle login for each user of the application which the application uses to access the DB.

It’s not totally clear what your plan for encryption looks like but it does seem like a good solution would be complex and difficult to get working. If you were going to go down that path, a stored proc might not be a terrible way to go but you really end up with the same issue in the end: controlling access to the stored proc or keys.

UPDATE:

As Ewan notes in the comments, simply restricting access to the tables will not prevent someone with full admin rights to the DB from accessing the table. I don’t think there’s a way to completely eliminate anyone from ever having full admin access to the DB but there might be reasonable controls over that which can be put in place.

This kind of thing tends to be tricky, and a client-server architecture makes it more difficult, which is likely why you are talking about rewriting the whole system. If you want to avoid that, one option would be to segregate this one aspect on the program into a more N-tier solution. That is: set up a HTTP host which has access to the key(s) for encryting and decrpyting the data. The application then, would not directly retrieve the data from the DB or have acces to the keys, it would call a webservice which would determine the user’s authorization and display the decrypted data they have access to see.

An astute reader may note that this amounts to moving the problem to a different server. That is true and by itself doesn’t address the root issues of how you restrict the access of privileged users (i.e., admins) to the data. What is does is give you much more flexibility while limiting the impact to the rest of the system. Of course, if this is the main thing the system does, then maybe a full rewrite is the answer.

“The secretary is responsible for keeping my files, but I don’t want her to be able to read the files”. That’s the essence of the problem here.

The word “secretary” is related to the word “secret”, and means an officer who is entrusted with the private information of the principal (either a person or an organisation).

The modern sysadmin is effectively the remainder of the senior ranks of what used to be the clerical, secretarial, or record-keeping staff of an organisation.

It’s a surprisingly common scenario today that management finds it necessary to entrust supervision of its data processing machinery to someone who they don’t actually trust to handle that data – as if the need for trustworthiness in such a role was an afterthought.

If you’re dealing with an old application that was written with certain assumptions in mind regarding the security architecture, you may really have little other option but to continue obeying that architecture – which means granting direct access to the database only to trusted staff, in accordance with the assumption around which the original application was designed.

However, standard database engines like Oracle have significant facilities for restricting access to data, so I’d be surprised if it wasn’t possible to create a specific login and clamp down on what it can access. It’s not clear why you haven’t considered this option, before considering fancy encryption schemes.

Nevertheless, there are often ways around the limitations if staff have sufficient physical access to the computer or overall permissions within the server environment, and if they have determination to access the data rather than just idle curiosity. The need to select an appropriate person for the most senior supervisory roles is never completely avoided.

I don’t think encryption is appropriate here. Redaction is probably a better bet. Then whoever is logged into the system will either see the data or not. If it was an SSN or some other PII then encryption is a better choice because the data should be protected at all times (in transit/at rest).

Here’s an example:

https://blogs.oracle.com/cloudsecurity/post/columnlevel-security-oracle-databases-data-redaction

In the summary section of the article, it indicates what level licensing is needed for data redaction feature if using on-prem.

One could also write some code that could check which user is logged in and then redact or mask the data appropriately. For example:

if (user != admin || user.role != manager) {
 salary = mask(salary);
}

It would take too much effort to do that for certain fields, even if the application is ancient.

“desktop application: I cannot deliver encryption key with application because it can be easily revealed”

Public Key Cryptography

Give the public key to the desktop and keep the private key for your admin application, or store it in an OS protected keystore with user control access.

So for example, if we assume your application is deployed to a secretaries PC, it must be able to read and write the secret data, it runs locally and the DB is on prem in another room:

Your flow for adding an employee might be

HR user logs in
Clicks “add employee”
Fills in the form with name and secret salary details
clicks “save”
app requests public key from keystore
OS checks logged in users permissions and returns key
app encrypts secret details
app uploads encrypted data to DB

User try’s to read data would be

Disgruntled Worker logs in
clicks “search employee details”
app requests data from DB
DB returns encrypted data
app shows blanked fields
User edits memory to get UI to show the data
app requests private key from OS
OS checks users permissions
OS says No
app throws error

User steals PC

Disgruntled Worker steals PC and takes it home
hacks motherboard TPM, windows security etc and gains admin access
copies private key
adds to their users keystore
repairs pc
can’t get salary data because it’s on the database.

User steals database

Disgruntled Worker steals database server and takes it to home lab
can’t decrypt because key is in another castle

Database Admin gets curious

DBA logs directly into database
select * from employees where id=’boss’
gets encrypted data
decryption key is not kept on the database.

So to gain access, I have to steal the key and also the database which are both in a secure office.

Admittedly if you are putting the key on every laptop in the company and people take them home. it’s maybe not the best idea. But from the description it sounds like the OP has a pretty basic setup.

Also, this doesn’t solve the “SProc needs to perform salary calculations” problem. But this is literally a contradiction in terms. You can’t have someone with access to the database not be able to see the data but also be able to see the data.

If that is not secure enough, then you can use my first suggestion. A different app for decryption. that way you can keep the private key on a single special PC in a locked room. The downside is your normal app cant view the data at all.

Filed under: softwareengineering - @ 20:28

Thẻ: architecture, oracle, sql

Thiết kế website giá rẻ

Danh mục

Encryption of PII data in database logic in store procedures and desktop application