8 Replies Latest reply: Dec 1, 2006 8:55 AM by 515325 RSS

    Encryption using APEX

    528661
      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
          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
            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
              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
                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
                  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
                    WWV_CRYPT is for internal use only and is not exposed in recent versions.
                    Scott
                    • 7. Re: Encryption using APEX
                      Chet Justice
                      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
                        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