4 Replies Latest reply: Jun 18, 2011 2:24 AM by Peter Wahl-Oracle RSS

    How to encrypt column of some table with the single method ?

    ky*431480*ix
      How to encrypt column of some table with the single method ?
        • 1. Re: How to encrypt column of some table with the single method ?
          605566
          How to encrypt column of some table with the single
          method ?
          How to encrypt column of some table with the single
          method ?

          using dbms_crypto package

          Assumption: TE is a user in oracle 10g
          we have a table need encrypt a column, this column SYSDBA can not look at, it's credit card number.

          tha table is
          SQL> desc TE.temp_sales
          Name Null? Type
          ----------------------------------------- -------- ----------------------------
          CUST_CREDIT_ID NOT NULL NUMBER
          CARD_TYPE VARCHAR2(10)
          CARD_NUMBER NUMBER
          EXPIRY_DATE DATE
          CUST_ID NUMBER

          1. grant execute on dbms_crypto to te;

          2. Create a table with a encrypted columns

          SQL> CREATE TABLE te.customer_credit_info(
          2 cust_credit_id number
          3      CONSTRAINT pk_te_cust_cred PRIMARY KEY
          4      USING INDEX TABLESPACE indx
          5      enable validate,
          6 card_type varchar2(10)
          7      constraint te_cust_cred_type_chk check ( upper(card_type) in ('DINERS','AMEX','VISA','MC') ),
          8 card_number blob,
          9 expiry_date date,
          10 cust_id number
          11      constraint fk_te_cust_credit_to_cust references te.customer(cust_id) deferrable
          12 )
          13 storage (initial 50k next 50k pctincrease 0 minextents 1 maxextents 50)
          14 tablespace userdata_Lm;

          Table created.

          SQL> CREATE SEQUENCE te.customers_cred_info_id
          2 START WITH 1
          3 INCREMENT BY 1
          4 NOCACHE
          5 NOCYCLE;

          Sequence created.

          Note: Credit card number is blob data type. It will be encrypted.

          3. Loading data encrypt the credit card number
          truncate table TE.customer_credit_info;

          DECLARE
          input_string VARCHAR2(16) := '';
          raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(CONVERT(input_string,'AL32UTF8','US7ASCII'));
          key_string VARCHAR2(8) := 'AsDf!2#4';
          raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(CONVERT(key_string,'AL32UTF8','US7ASCII'));
          encrypted_raw RAW(2048);
          encrypted_string VARCHAR2(2048);

          BEGIN

          for cred_record in (select upper(CREDIT_CARD) as CREDIT_CARD,
          CREDIT_CARD_EXP_DATE,
          to_char(CREDIT_CARD_NUMBER) as CREDIT_CARD_NUMBER,
          CUST_ID
          from TE.temp_sales) loop

          dbms_output.put_line('type:' || cred_record.credit_card || 'exp_date:' || cred_record.CREDIT_CARD_EXP_DATE);
          dbms_output.put_line('number:' || cred_record.CREDIT_CARD_NUMBER);

          input_string := cred_record.CREDIT_CARD_NUMBER;

          raw_input := UTL_RAW.CAST_TO_RAW(CONVERT(input_string,'AL32UTF8','US7ASCII'));

          dbms_output.put_line('> Input String: ' || CONVERT(UTL_RAW.CAST_TO_VARCHAR2(raw_input),'US7ASCII','AL32UTF8'));

          encrypted_raw := dbms_crypto.Encrypt(
          src => raw_input,
          typ => DBMS_CRYPTO.DES_CBC_PKCS5,
          key => raw_key);

          encrypted_string := rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_raw)) ;

          dbms_output.put_line('> Encrypted hex value : ' || encrypted_string );

          insert into TE.customer_credit_info values
          (TE.customers_cred_info_id.nextval,
          cred_record.credit_card,
          encrypted_raw,
          cred_record.CREDIT_CARD_EXP_DATE,
          cred_record.CUST_ID);

          end loop;

          commit;
          end;
          /

          4. Check credit card number script
          DECLARE
          input_string VARCHAR2(16) := '';
          raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(CONVERT(input_string,'AL32UTF8','US7ASCII'));
          key_string VARCHAR2(8) := 'AsDf!2#4';
          raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(CONVERT(key_string,'AL32UTF8','US7ASCII'));
          encrypted_raw RAW(2048);
          encrypted_string VARCHAR2(2048);
          decrypted_raw RAW(2048);
          decrypted_string VARCHAR2(2048);

          cursor cursor_cust_cred is select CUST_CREDIT_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE, CUST_ID
          from TE.customer_credit_info order by CUST_CREDIT_ID;

          v_id customer_credit_info.CUST_CREDIT_ID%type;
          v_type customer_credit_info.CARD_TYPE%type;

          v_EXPIRY_DATE customer_credit_info.EXPIRY_DATE%type;
          v_CUST_ID customer_credit_info.CUST_ID%type;

          BEGIN
          dbms_output.put_line('ID Type Number Expiry_date cust_id');

          dbms_output.put_line('-----------------------------------------------------');

          open cursor_cust_cred;

          loop
               fetch cursor_cust_cred into v_id, v_type, encrypted_raw, v_expiry_date, v_cust_id;
          exit when cursor_cust_cred%notfound;

          decrypted_raw := dbms_crypto.Decrypt(
          src => encrypted_raw,
          typ => DBMS_CRYPTO.DES_CBC_PKCS5,
          key => raw_key);

          decrypted_string := CONVERT(UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw),'US7ASCII','AL32UTF8');

          dbms_output.put_line(V_ID ||' ' ||
          V_TYPE ||' ' ||
          decrypted_string || ' ' ||
          v_EXPIRY_DATE || ' ' ||
          v_CUST_ID);

          end loop;

          close cursor_cust_cred;

          commit;
          end;
          /
          • 2. Re: How to encrypt column of some table with the single method ?
            damorgan
            Several possibilities. One, as Jiwen---DBA stu... mentions, is to roll your own using DBMS_CRYPTO.

            I have working demos here:
            http://www.psoug.org/reference/dbms_crypto.html

            The other, and the one I prefer though it does require additional licensing, is Transparent Data Encryption (TDE).

            I have working demos (for 11g) here:
            http://www.psoug.org/reference/tde.html

            Also, in 11g, we have encrypted tablespaces which are a huge help.

            Demo here:
            http://www.psoug.org/reference/tde.html#tdets
            • 3. Re: How to encrypt column of some table with the single method ?
              826439
              Thanks Jiwen.

              But in my case table already exist with huge number of rows.

              Thanks.
              • 4. Re: How to encrypt column of some table with the single method ?
                Peter Wahl-Oracle
                There are years between each post, how come? Please tell me what database version you are using and we'll find a solution quickly.

                Best, Peter