This discussion is archived
8 Replies Latest reply: Dec 1, 2006 6:55 AM by 515325 RSS

Encryption using APEX

528661 Newbie
Currently Being Moderated
Just wondering if anyone is aware of any examples that demonstrate how to use the WWV_CRYPT package?

I'm interested in insert, update and select statements.

Alternatively, can anyone point me in the direction of other suitable encryption methods that can be used within APEX? That will also be appreciated.

Many thanks.

James
  • 1. Re: Encryption using APEX
    Chet Justice Oracle ACE
    Currently Being Moderated
    What exactly are you trying to do?

    Where did you find the reference to that package? I couldn't find it in the docs...

    chet
  • 2. Re: Encryption using APEX
    528661 Newbie
    Currently Being Moderated
    Hi Chet,

    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!

    Many thanks

    James
  • 3. Re: Encryption using APEX
    Chet Justice Oracle ACE
    Currently Being Moderated
    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
  • 4. Re: Encryption using APEX
    528661 Newbie
    Currently Being Moderated
    Hi Chet,

    thanks for the info. We're aware of DBMS_CRYPTO although we haven't used it yet so we will give it a go.

    Maybe WWV_CRYPT is redundant if nobody's using it!

    Thanks again.
  • 5. Re: Encryption using APEX
    Chet Justice Oracle ACE
    Currently Being Moderated
    No problem.

    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:
    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           
    and the package depends on:
    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
    chet
  • 6. Re: Encryption using APEX
    60437 Employee ACE
    Currently Being Moderated
    WWV_CRYPT is for internal use only and is not exposed in recent versions.
    Scott
  • 7. Re: Encryption using APEX
    Chet Justice Oracle ACE
    Currently Being Moderated
    Scott,

    Did you catch that part above:

    "The reference to WWV_CRYPT comes from the Oracle HTML DB Handbook by Oracle Press."

    That's a little disconcerting I think...(not that you have any control, just saying)

    chet
  • 8. Re: Encryption using APEX
    515325 Newbie
    Currently Being Moderated
    HI chet,

    i've followed this post to implement a crypting strategy inside an app of mine.
    However my question is: if using blob should i always convert to row datatype?
    An example would be great.

    thanx