0

I'm tasked to create encryption/decryption methods inside an Oracle database. By researching I came up to this SO discussion which provides a great code example using the DBMS_CRYPTO package.

However there is a security concern: in this design, the KEY/IV pair, the encryption algorithm, and the encrypted data are all stored in one place (the database). Making it too easy for gathering all the necessary pieces to get the real value.

For some reason the management turned down the option to store the keys outside Oracle db. The reason is occasionally we need to provide a data dump to the client. They cannot have encrypted data and they cannot decrypt it. The data dump is done by SQL so the decryption can be performed in the SELECT query.

Is there a best practice for this scenario?

Thank you for your time.

Cal
  • 747
  • 1
  • 13
  • 30
  • Your concern is justified: storing the keys in the same place as the data is generally a *bad* idea. Ideally, keys should be provided from an external source. That said, what is the business case/purpose for the encryption? What problem are you trying to solve? Maybe there's a better way... – pmdba Jan 21 '22 at 20:39
  • Does that include the oracle wallet? – OldProgrammer Jan 21 '22 at 21:26
  • @pmdba I have added the use case in the 3rd paragraph. – Cal Jan 21 '22 at 21:36
  • @OldProgrammer Let me research how to use wallet. In the meantime do you have some reading to share? Thanks! – Cal Jan 21 '22 at 21:36
  • 2
    what is the requirement to encrypt? at what level (i.e. data at rest, data in motion, application-level to hide from DBA, etc.) must it be encrypted? What problem is the encryption *solving*, and which edition and version of Oracle are you using? – pmdba Jan 22 '22 at 12:24
  • The first best practice is to copy examples from the manual instead of answers on SO with a low number of up-votes. Start from [this DBMS_CRYPTO](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CRYPTO.html#GUID-A3CFAB51-7CD7-41FC-841A-FBE1E59CB5C2) example. There are some weird cryptographic options that probably neither of us understand, so we should start from the official example. Also, many times the best way to encrypt your data is at the OS or hardware level. But like pmdba says, it depends on what your real requirement is. – Jon Heller Jan 22 '22 at 18:22
  • 1
    @Cal In particular who are you trying to protect the data from? Database edition, version, and licensed options could have a significant effect on your choices, too. – pmdba Jan 22 '22 at 20:14

0 Answers0