0

My question is in general but related to Oracle DB. I have a single table with different companies as rows. Each row has company id and company registration number. I would like to encrypt company registraiton number, but i want to encrypt each company registration number with a unique key.

Is this is possible? I have only found oracle docs on encrypting columns of a table with one single key. My use case is different. Please advice.

1

It doesn't really matter to you how it's encrypted in the database. Database encryption is "transparent", meaning that you don't see the encryption mechanisms when you access the data through an authorized connection. You don't get to see or specify which key is used for which table or row; that's all secured inside the engine.

So if you truly need each company's info to be encrypted its own unique key, you'll have to encrypt the data in your application before writing it to your database. That quickly gets you into complex key management issues.

Since you haven't mentioned "why" you would want this, I'm assuming it's to isolate queries so that only authorized users can view or edit the data for a particular company. It means you'll have to build a mechanism to hand out the proper keys to authenticated users, so that the salespeople can edit only their own accounts, view their region's accounts, etc. You'll have to securely store and retrieve those keys, and transmit them to the encryption service. You'll have to rotate those keys according to your company's key management policies. You'll need to have a way to edit key ownership so that when an employee leaves their accounts can be reassigned to other people. You may even need to build an audit reporting mechanism. It's a very complex task to build a usable key management system.

But all this seems like it could be avoided if you lay out your actual requirements. Are you really trying to restrict access to certain rows to certain users? Look at Oracle's existing Access Controls, which can already perform that level of control for you. You wouldn't need to use custom encryption at all if you can use their built-in functionality to restrict access.

  • Your understanding for "why" is almost correct. The system is built with a purpose that each company can bring their own encryption key. So if company A has keyA, then keyA will be used to encrypt rows related to only company A. Basically the system should give a facility to the companies to use their own keys. The different keys will be stored in an HSM. – ZEE Mar 4 at 4:26
0

Oracle TDE can perform table and column level encryption. It cannot be used for encrypting rows in the same table with different keys.

As John mentions in his answer - you can write your own, but that will require a lot of work - and it's not clear if it will add any security since the application will likely need access to all the keys and need to be trusted. Encryption is not the answer for all security issues.

If these need to be rows in the same table, you should look at VPD or OLS for restricting access to specific rows via access control as an alternative to encryption.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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