This discussion is archived
4 Replies Latest reply: Jun 18, 2011 12:24 AM by Peter Wahl RSS

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

431480 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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