• From: Brent Day <coloradodba@xxxxxxxxx>
  • To: oracle@xxxxxxxxxxx
  • Date: Wed, 8 Oct 2014 22:25:03 -0600


I am working through the same issue and just yesterday submitted an
enhancement request SR with Oracle. The engineer created a new
bug/enhancement request today as "19780358.- need PL/SQL API to get
password from wallet".

The functionality would provide PL/SQL functionality similar to the
following white paper:

Wrapping a package in another "secured" schema only slows down a malicious
user. If a user is granted execute then they can see the package source
using "selet * from all_source order by line", Once the user has that data
it is very easy to unwrap using any number of easy to access tools online.
There is even an online version where you can paste wrapped code and get
the unwrapped output. Personally I hate "wrapped" code as it only provides
an illusion of security. I have not looked at 12c functionality yet and
hope Oracle changes this.

To address what I think might be a missing element, I would approach a
solution that would abstract our real encrypt/decrypt functions by making
those internal functions only known and available to the "secured" schema.
Then I would create functions for users that would call the internal
function. Something like this:

secure_user schema
     real_decypt_function(p_string in varchar2)
           has encryption key
            decrypts string
            returns decrypted string

      decrypt_function(p_string in varchar2)
            return real_decrpt_function(p_string)

grant execute to decrpt_function to user_a (do not grant execute on

user_a calls secure_user.decrypt_function('some encrypted string'); but
would never be granted access to the "real_decrypt_function" so the would
not have access to the wrapped code.

Of course anyone with access to the secure_user could unwrap the package
but this should limit exposure to all but authorized and the most malicious
of users that have actually hacked into your database. Remember to
carefully consider invoker vs definer rights as well on your
function/procedure definitions. If a hacker/malicious user get elevated
access like DBA it could still be game over in terms of protecting your
keys that are in a wrapped package but at that point you have a much bigger

As with everything, test and verify and implement at your own risk - no
warranties implied :).


> Would moving the decrypt function out of the data owner schema prevent
> meaningful access
> to the encrypted data by the owner schema?
> Am I missing anything else?
> Dave
> --
> Dave Morgan
> Senior Consultant, 1001111 Alberta Limited
> dave.morgan@xxxxxxxxxxx
> 403 399 2442
> --

Other related posts: