0

My requirement is to do column level encryption.

Tried below option TDE - data is not encrypted to one who has access to database. Please correct me if I'm wrong. DBMS_CRYPTO package, this works but client wants to store encryption key outside Oracle database. I'm not able to find solution for storing key outside database.

Any help on this is highly appreciated.

Var 2010
  • 1
  • You are correct: TDE only encrypts data at rest, not data in use by SQL (that's why it's "transparent" to the user). Is that a problem? Can you control access to the sensitive data rather than encrypt it at the application level? What is the problem you are trying to solve? – pmdba Nov 09 '22 at 01:32
  • Note that encrypting the data so that it can't be read by someone who has access to the database means that you won't be able to *anything* with that data until you decrypt it: you can't index it, use it for joins, have table constraints on that column, or search on it using SQL. Be very sure that you *need* this level of encryption before going that route, because you're potentially giving up a lot of things to get there. While there *are* sometimes good reasons for the encryption, Oracle generally steers people towards access controls rather than encrypting at the app level. – pmdba Nov 09 '22 at 01:33
  • It's only one table column to be encrypted at the database. Any developer/DBA should be able to see encrypted data not the plain text when they do select * from table. I was able to do that using dbms_crypto but now problem is I want to store key outside database maybe a wallet. Not sure how to do that – Var 2010 Nov 09 '22 at 07:14
  • Outside the database isn't enough: if the key is still on the DB server then the DBA can still get to it and use it. Using DBMS_CRYPTO the key may still be exposed in the SQL cache where the DBA can see it, no matter where you store it. The use cases for this I've seen in the past place the key on the app server rather than the DB server and use an encryption algorithm native to the app framework rather than DBMS_CRYPTO. That way it is impossible to decrypt the data except through the app. Using DBMS_CRYPTO there's always a chance that the DBA can view the data. – pmdba Nov 09 '22 at 13:56
  • Oracle's solution - if you have Enterprise Edition - to preventing DBAs from seeing sensitive data is to use the Database Vault option, which again is access control and not encryption. Short of that, assume your DBA can see everything with a little effort and that the DBA needs to be a trusted individual. Also, everything about ability to use the encrypted data in SQL that I said holds true, no matter how you encrypt the data: no indexing, constraints, joins, or or search predicates on that data are possible. – pmdba Nov 09 '22 at 14:00

1 Answers1

0

Perhaps two different things here.

To do TDE at column level already uses an external key store, namely a wallet. The location is specified by ENCRYPTION_WALLET_LOCATION in your sqlnet.ora file, and you'd open the wallet when the database starts, eg

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "thePasswordIchose";

Conversely, if you are looking at doing some "home grown" encryption of data using DBMS_CRYPTO, then key management also becomes your own responsibility. You could store the key however/wherever you like, but its your job then to manage it and pass it securely into the DBMS_CRYPTO routines

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • I tried following the steps given in this url but still as a DBA I'm able to see column data when I do select * from table. https://smarttechways.com/2021/10/05/configuring-transparent-data-encryption-tde-in-oracle-19c/comment-page-1/#comment-28737 – Var 2010 Nov 09 '22 at 02:03