10 Replies Latest reply: Feb 28, 2013 12:12 PM by 950462 RSS

    How to encrypt and decrypt a table column

    950462
      Hi,

      I have a table in oracle 10g production with column Emp_salary and Address and some other columns as well.I want to encrypt emp_salary and address column for vendors use.
      But after vendors done his job then I need to backup those columns normal so everyone can view the value of both columns (emp_sal and address).I need your help in this regards.

      Thanks
        • 1. Re: How to encrypt and decrypt a table column
          jeneesh
          Check the supplied package DBMS_CRYPTO

          And you would have got lot and lot samples if you had bothered to at least GOOGLE it..

          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3186909500346211026
          • 2. Re: How to encrypt and decrypt a table column
            Purvesh K
            I used DBMS_Obfuscation_Toolkit to achieve it.

            Check this post {message:id=10835804}
            • 3. Re: How to encrypt and decrypt a table column
              jeneesh
              Purvesh K wrote:
              I used DBMS_Obfuscation_Toolkit to achieve it.

              Check this post {message:id=10835804}
              Do NOT use dbms_obfuscation_toolkit - it is obsolete.it is deprecated as of Oracle 10g and above, replaced by DBMS_CRYPTO
              • 4. Re: How to encrypt and decrypt a table column
                Purvesh K
                Yes, I missed that.

                Especially the Documentation did not specifically say so, DBMS_OBFUSCATION_TOOLKIT; However, another link for DBMS_CRYPTO mentions it.

                Thanks for correcting me.
                • 5. Re: How to encrypt and decrypt a table column
                  950462
                  Scenario is little bit changed data should be encrypted but only can be viewed as normal way by Application Form but not any other tools like sql developer,Toad etc.

                  inshort application user can view the data but vendor can not view.
                  • 6. Re: How to encrypt and decrypt a table column
                    Manik
                    VPD + encryption

                    Cheers,
                    Manik.
                    • 7. Re: How to encrypt and decrypt a table column
                      KPR
                      Hi ..

                      earlier I have used the below script for the same requirement...if it is useful for you ... U can ...!!
                      CREATE OR REPLACE PACKAGE DUMMY_CRYPTO
                          AS
                             FUNCTION ENCRYPT (VAL IN VARCHAR2) RETURN VARCHAR2;
                             FUNCTION DECRYPT (VAL IN VARCHAR2) RETURN VARCHAR2;
                          END;
                      
                      
                      
                      CREATE OR REPLACE PACKAGE BODY DUMMY_CRYPTO
                          AS
                             FUNCTION ENCRYPT (VAL IN VARCHAR2) RETURN VARCHAR2
                             IS
                                L_KEY               RAW(128) := UTL_RAW.CAST_TO_RAW('ORACLE NETWORK TECH');
                                L_VAL               RAW(128) := UTL_RAW.CAST_TO_RAW(VAL);
                                L_ENCRYPTED_RAW     RAW(2048);
                               
                             BEGIN
                                L_ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => L_VAL, 
                                                               TYP => DBMS_CRYPTO.DES_CBC_PKCS5, 
                                                               KEY => L_KEY);
                                RETURN UTL_RAW.CAST_TO_VARCHAR2(L_ENCRYPTED_RAW);
                            END;
                       
                         FUNCTION DECRYPT (VAL IN VARCHAR2) RETURN VARCHAR2
                            IS
                             L_KEY               RAW(128) := UTL_RAW.CAST_TO_RAW('ORACLE NETWORK TECH');  
                             L_VAL               RAW(128) := UTL_RAW.CAST_TO_RAW(VAL);
                             L_DECRYPTED_RAW     RAW(2048);
                            BEGIN
                              L_DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => L_VAL, 
                                                               TYP => DBMS_CRYPTO.DES_CBC_PKCS5, 
                                                               KEY => L_KEY);
                               RETURN UTL_RAW.CAST_TO_VARCHAR2(L_DECRYPTED_RAW);
                            END;
                         END DUMMY_CRYPTO;
                      
                      
                      
                      EXECUTE THIS QUERY
                      ******************
                      
                      SELECT DUMMY_CRYPTO.ENCRYPT(WEB_LOGIN), 
                                 DUMMY_CRYPTO.DECRYPT(DUMMY_CRYPTO.ENCRYPT(WEB_LOGIN)) 
                      FROM TABLE_NAME;
                      
                      
                      
                      SELECT DUMMY_CRYPTO.ENCRYPT(WEB_PASSWORD), 
                                 DUMMY_CRYPTO.DECRYPT(DUMMY_CRYPTO.ENCRYPT(WEB_PASSWORD)) 
                      FROM TABLE_NAME;
                      KPR
                      • 8. Re: How to encrypt and decrypt a table column
                        950462
                        Hi,
                        Thanks for your response. This is not working in my scenario.Actually I want to encyrpt/hide columns in database for all users except those who are connecting through FORMS.
                        Our end user access the data using Forms so they can view the all columns of table, but when any other user including DBA/Vendors try to access the table from any tool then he can access only those columns which are not encrypted or hide. Encrypted columns are only display for FORMS user not for others. After that exercise we can decrypt the encrypted columns.
                        again thanks for your reply and time.
                        • 9. Re: How to encrypt and decrypt a table column
                          sukhijank
                          For this, you may not need to encrypt/decrypt data.
                          You may try to

                          1) create views (based on selected columns for which you want to give the access to vendor) on your tables.
                          2) Create another user in database (by which Vendor will log on)
                          3) Provide necessary privillages on the view to newly created user.

                          Thanks,
                          Naresh
                          • 10. Re: How to encrypt and decrypt a table column
                            950462
                            Naresh,

                            Thanks, First thing is that we have to keep the table name same for vendor as well, so if we create view then it will be different name.
                            I already created a view with different name and hide those columns and then created a public synonym for that view ( same name as table) and also created a user for vendor
                            give necessary permission to him. But I my question is that the table column be encrypt and vendor can see the name of column but not data. But the Form user (end user) can
                            view all data as their normal practice.