This content has been marked as final. Show 8 replies
Firstly all I am looking to do is store an encypted varchar column in the database, so that the DBA or indeed anyone else using SQL*Plus can't get hold of the unencrypted data. I only want to be able to retrieve the unencrypted column from within ApEx. Inserts and updates to the column should only be performed through ApEx and use the encryption function that I specify.
The reference to WWV_CRYPT comes from the Oracle HTML DB Handbook by Oracle Press. Looking at the database objects it would appear that WWV_CRYPT has been around for a while as it is included with the FLOWS_010600 schema and beyond.
The reason I am posting this is that the HTML DB Handbook alludes to this being a fairly standard method, so I was hoping someone had actually used it!
I haven't read the book. I couldn't find any instances of people using that package to encrypt/decrypt data in ApEx. I wonder if it is only used internally?
Anyway, I use DBMS_CRYPTO to encrypt/decrypt my data: http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_crypto.htm
It's fairly straightforward:
CREATE OR REPLACE PACKAGE BODY p_encrypt AS --DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE --IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED --THE WRAP UTILITY IS LOCATED IN THE <ORACLE_HOME>\BIN DIRECTORY (WRAP.EXE) G_KEY RAW(32) := UTL_I18N.STRING_TO_RAW( 'some_random_string_stuff_goes_here', 'AL32UTF8' ); FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW IS l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, 'AL32UTF8' ); l_encrypted RAW(32); BEGIN NULL; l_encrypted := dbms_crypto.encrypt ( src => l_ssn, typ => DBMS_CRYPTO.DES_CBC_PKCS5, key => G_KEY ); RETURN l_encrypted; END encrypt_ssn; FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2 IS l_decrypted RAW(32); BEGIN l_decrypted := dbms_crypto.decrypt ( src => p_ssn, typ => DBMS_CRYPTO.DES_CBC_PKCS5, key => G_KEY ); RETURN UTL_I18N.RAW_TO_CHAR( l_decrypted, 'AL32UTF8' ); END decrypt_ssn; END p_encrypt; / show errors
I think you are better off using the dbms_crypto package in your app. That's one less dependency on ApEx specific packages you have to worry about.
I'm still guessing, due to the lack of hits performing a search, that wwv_crypt is used mainly for internal processing. Here are the direct dependencies:
and the package depends on:
WWV_CRYPT PACKAGE BODY WWV_FLOW_SECURITY PACKAGE BODY WWV_FLOW_USER_API PACKAGE BODY WWV_FLOW_UTILITIES PACKAGE BODY WWV_BIU_FND_USER TRIGGER
REFERENCED_OWNER TYPE REFERENCED_NAME ------------------------------ ----------------- --------------------------- PUBLIC PACKAGE BODY DUAL SYS PACKAGE BODY STANDARD SYS PACKAGE STANDARD SYS PACKAGE BODY DBMS_STANDARD PUBLIC PACKAGE BODY UTL_RAW PUBLIC PACKAGE BODY DBMS_LOB FLOWS_020200 PACKAGE BODY WWV_FLOW_LANG FLOWS_020200 PACKAGE BODY WWV_CRYPT FLOWS_020200 PACKAGE BODY UTL_RAW FLOWS_020200 PACKAGE BODY DUAL FLOWS_020200 PACKAGE BODY DBMS_LOB