Re: Encryption key storage

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 20 Jun 2006 10:24:36 -0700
Message-ID: <1150824276.508283.118400@b68g2000cwa.googlegroups.com>

G Quesnel wrote:
> The database is currently at Oracle 9.2.0.6 on Solaris.
> I have a few questions about my options to store encryption keys.
> We want to store the encryption keys in an LDAP directory, so that when
> we pass the full export of the database, it doesn't include the
> encryption keys. Our encrypt/decrypt package functions can read the
> keys from LDAP as required, but is there a way to retain/share the key
> values so we don't need to do an LDAP call for each individual session
> that wants to do an encrypt or decrypt.
> Could the key be stored in a static java variable, to be accessible by
> mutliple sessions ?
> (or would it's visibility be limited to that of a package variable -
> re-initalized for every session)
> Could the key be stored in a materialized view, to be accessible by
> mutliple sessions ?
> In either case, would the key values be included in the dump of a full
> database export ?
>
> I am also wondering about the possibility of storing the wrapped
> encrypt/decrypt package under the SYS schema, so that it would not be
> included in a full export.
>
> Thanks for your thoughts.

You may want to try application contexts accessed globally, introduced in 9.2 if I'm not mistaken. They are described in detail in Application Developer's Guide - Fundamentals, with other bits of information dispersed through SQL Reference and Supplied PL/SQL Packages and Types Reference. Here's a short example of use:

SQL> select banner from v$version;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production

SQL> conn / as sysdba
Connected.
SQL> grant create any context to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.

the keyword here is ACCESSED GLOBALLY SQL> create or replace context myctx using scott.setctx accessed globally;

Context created.

just a test procedure that sets '12345' as 'key' for user SCOTT with
client identifier of CLT1, which means that only SCOTT with
this identifier will see the value. Real logic can (and will) be much
more complex, of course. SQL> create or replace procedure setctx 2 as 3 begin 4 dbms_session.set_context('myctx','key','12345','SCOTT','CLT1'); 5 end; 6 /

Procedure created.

initialize the context SQL> exec setctx;

PL/SQL procedure successfully completed.

let's see if we can get the value SQL> select sys_context('myctx','key') from dual;

SYS_CONTEXT('MYCTX','KEY')

nope, we need to set the client identifier to be able to
fetch from that global context. SQL> exec dbms_session.set_identifier('CLT1');

PL/SQL procedure successfully completed.

SQL> select sys_context('myctx','key') from dual;

SYS_CONTEXT('MYCTX','KEY')

12345

reconnect, thus closing original session that initialized
the context. If it was local context, we'd lose all its
values by now and would need to reinitialize it. SQL> conn scott/tiger Connected.
first try to fetch the variable without identifying ourselves. SQL> select sys_context('myctx','key') from dual;

SYS_CONTEXT('MYCTX','KEY')

set the identifier needed to access the context SQL> exec dbms_session.set_identifier('CLT1');

PL/SQL procedure successfully completed.

bingo! SQL> select sys_context('myctx','key') from dual;

SYS_CONTEXT('MYCTX','KEY')

12345

Hth,

     Vladimir M. Zakharychev
     N-Networks, makers of Dynamic PSP(tm)
     http://www.dynamicpsp.com

Received on Tue Jun 20 2006 - 12:24:36 CDT