Using Wrap Utility to Hide the SQL Code in Oracle

At times we may need to encrypt sensitive information in certain columns in Oracle Database. Oracle provides a DBMS_CRYPTO package for the purpose. More details about this package can be found in Oracle Documentation here.

The approach I followed is as follows –

  • Create a custom package using features provided in DBMS_CRYPTO, which will consists for functions to encrypt / decrypt varchar and blob type columns.
  • Use this package to encrypt / decrypt the data in the fields.

For a reference implementation of such a code, you can refer to the Oracle Documentation mentioned above.

One key aspect is the use of a ‘Key’ in the encryption logic. In the sample code given on the documentation, a random key has been chosen. This random key is fine as long as you do not recompile the package. If the package is recompiled then the key changes and hence invalidates all the encrypted data which can not be decrypted in the absence of the old key.

If we keep the key as fixed, then anyone looking at the source code of the package can see the key and thus potentially compromising on the security aspects.

To resolve this, fortunately Oracle provides a wrap utility to hide the source code.

$ wrap iname=enc_dec.sql oname=enc_dec.sql.wrp

SQL> @enc_dec.sql.wrp;

Now anyone looking at the source code of the package will find gibberish understood by Oracle only. However, the original source with the key has to be kept safe, just incase you need it back again.

This entry was posted in Databases. Bookmark the permalink.

Leave a Reply