3

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:

  1. 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.
  2. 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.

10
  • 3
    You have asserted some things, but failed to ask an actual question. You've decided you need to rewrite the whole system, what do you need help with? Commented May 20 at 13:23
  • 3
    It seems that you accept situation that some unauthorized people may access your db. Why? Just don't let them. Problem solved.
    – freakish
    Commented May 20 at 13:43
  • 1
    @PhilipKendall The question is presumably "what should be done about the two problems listed."
    – bdsl
    Commented May 20 at 13:53
  • @freakish: While I agree with the spirit of what you're saying, your words can also be taken to never need a second line of defense either - which is precisely the mistake made by people who e.g. store plaintext passwords. I think your wording needs more nuance to avoid the advice being understood in a harmful way.
    – Flater
    Commented May 22 at 4:28
  • @Flater I'm not sure where you see analogy between what I said and storing plain text passwords. It's the opposite: restricting access is more secure than encryption. Because encryption enforces you to setup key infrastructure, which is another point of failure.
    – freakish
    Commented May 22 at 4:31

4 Answers 4

4

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.

40
  • this doesn't seem to answer the question "I want to encrypt data so someone with db access cannot check salaries of other workers"
    – Ewan
    Commented May 21 at 12:45
  • @Ewan Huh? Having access to the DB doesn't mean you have access to all tables in it. Oracle allows you grant access on a table-by-table basis (at the very least) I have access to a Oracle DB where I don't even have the rights to see that some tables exist, much less access them.
    – JimmyJames
    Commented May 21 at 12:54
  • sure but presumably there is a DBA somewhere who needs full access for maintenance, but isn't allowed to see salaries
    – Ewan
    Commented May 21 at 13:08
  • You need to do both, granular access rights, and encrypted data at rest.
    – Ccm
    Commented May 21 at 13:35
  • @Ewan If that is the requirement, then yes, access controls won't be good enough. Access auditing might be an option. It's not clear who needs access to the data exactly, though, beyond (presumably) HR staff.
    – JimmyJames
    Commented May 21 at 13:42
2

"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.

22
  • its normal to encrypt private data to prevent theft style attacks. ie I hack into Sony's PlayStation users database because i guess the admin pass, I download everything, but everyone's emails and personal data fields are encrypted and the key is not in the database, so I cant read them
    – Ewan
    Commented May 21 at 13:14
  • @Ewan, but private to whom, and who decides to whom the organisation's data is private? Most standard encryption schemes are designed to defend the sysadmins from various kinds of subversion of their authority, not to prevent sysadmins themselves from viewing data or making decisions about access.
    – Steve
    Commented May 21 at 18:38
  • private as in the end users "personal private information", (in this case the salary), whom as in everyone who doesn't require access in order to provide the services purchased/agreed to by the customer (in this case HR or the accounts dept). Sure salary is a bit of an edge case, employees aren't customers. imagine credit card numbers instead
    – Ewan
    Commented May 21 at 20:07
  • @Ewan, I've worked in a few banks and I can assure you there was nothing stopping the very lowest clerk, hired a week ago, from rifling your personal info including your credit card numbers. For very good reason, of course, that the clerks had various legitimate reasons to access the data when solving problems. The issue at hand is who has the practical power to decide who sees what, and what requirements justify access. In general regarding data belonging to the organisation, the sysadmins have that practical power.
    – Steve
    Commented May 21 at 22:46
  • "To comply with PCI DSS requirements, companies must use strong encryption algorithms like Advanced Encryption Standard (AES) or Triple Data Encryption Standard (3DES) to protect sensitive information during storage, transmission and processing. Additionally, they must have secure key management practices in place and use only approved cryptographic modules"
    – Ewan
    Commented May 22 at 10:25
1

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.

0

"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.

13
  • OS protected keystores are usually not considered secure enough for PII.
    – Ccm
    Commented May 21 at 8:06
  • you got a reference for that?
    – Ewan
    Commented May 21 at 12:37
  • It just seems like you rely on the os access management for a whole tonne of stuff which wouldn't be allowed if it was true.
    – Ewan
    Commented May 21 at 12:39
  • for example, jimmy james' answer effectively does the same thing on the DB. if you log in with admin, it decrypts the data for you
    – Ewan
    Commented May 21 at 12:43
  • I don't see how using a public key for decryption prevents access.
    – JimmyJames
    Commented May 21 at 12:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.