Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Encryption

Posted on 2011-02-15
8
Medium Priority
?
1,653 Views
Last Modified: 2012-05-11
CREATE OR REPLACE procedure saveSecure(v_NCC number,v_ccv_secure_group_id number,
v_ccv_secure_variable_id number,
V_LAST_MODIFIED_DATE timestamp,v_value in long raw,encrypt_decrypt_key in varchar2) as v_temp integer;
begin
for r in(select distinct NCC,vcc_group_id,ccv_variable_id from CCV_SECURE where
NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id )loop
--row exists
update CCV_SECURE set NCC = v_NCC, vcc_group_id=v_ccv_secure_group_id,ccv_variable_id=v_ccv_secure_variable_id,
last_modified_date=v_last_modified_date, SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               ) WHERE  NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id;
--DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||SQL%ROWCOUNT);
     return;
  end loop;
  -- row doesn't exist
INSERT INTO CCV_SECURE(CCV_SECURE_ID,NCC,vcc_group_id,ccv_variable_id,LAST_MODIFIED_DATE,SECURE_VALUE)
    VALUES (CCV_SECURE_SEQ.nextval,v_NCC,v_ccv_secure_group_id,v_ccv_secure_variable_id,V_LAST_MODIFIED_DATE,
               sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               )
           );
--DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
end;
/

 The above procedure giving an error on secure value if i enter more that 2000 characters for v_value column.  Do you know how to enter more than 2000 characters for v_value

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
ORA-06512: at "saveSecure", line 6
ORA-06512: at line 1

0
Comment
Question by:vadicherla
  • 5
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34903930
you'll have to split your string into pieces, encrypt each and concatenate the results
0
 

Author Comment

by:vadicherla
ID: 34904211
Okey. Can you please provide more information how to do for above procedure
0
 

Author Comment

by:vadicherla
ID: 34907651
i modified but still i am getting error

CREATE OR REPLACE procedure saveSecure(v_NCC number,v_ccv_secure_group_id number,
v_ccv_secure_variable_id number,
V_LAST_MODIFIED_DATE timestamp,v_value1 in varchar2,v_value2 in varchar2,encrypt_decrypt_key in varchar2) as v_temp integer;
begin
for r in(select distinct NCC,vcc_group_id,ccv_variable_id from CCV_SECURE where
NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id )loop
--row exists
update CCV_SECURE set NCC = v_NCC, vcc_group_id=v_ccv_secure_group_id,ccv_variable_id=v_ccv_secure_variable_id,
last_modified_date=v_last_modified_date, SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value1||v_value2),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               ) WHERE  NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id;
--DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||SQL%ROWCOUNT);
     return;
  end loop;
  -- row doesn't exist
INSERT INTO CCV_SECURE(CCV_SECURE_ID,NCC,vcc_group_id,ccv_variable_id,LAST_MODIFIED_DATE,SECURE_VALUE)
    VALUES (CCV_SECURE_SEQ.nextval,v_NCC,v_ccv_secure_group_id,v_ccv_secure_variable_id,V_LAST_MODIFIED_DATE,
               sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value1 ||v_value2),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               )
           );
--DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
end;
/

ROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_RAW", line 224
ORA-06512: at "DX_TEST.SAVESECURESITESTRINGVARIABLE", line 16
ORA-06512: at line 1

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vadicherla
ID: 34909766
Can you please update
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34909854
Are you sure the problem is in the encryption?

What is line 16?  Is it the update

Is the problem that secure_value is being updated with a RAW value too big?

There is a quirk to working with SQL and PL/SQL together.

VARCHAR2 and RAW can have length up to 32767 in pl/sql,  but only 4000 in SQL.
If you have multi-byte characters, the limits are proportionally less
0
 

Author Comment

by:vadicherla
ID: 34912247
I gave data type as long raw for secure value column in table


its insert which is failing

INSERT INTO SSV_SECURE(SSV_SECURE_ID,CCN,SSV_GROUP_ID,SSV_VARIABLE_ID,LAST_MODIFIED_DATE,SECURE_VALUE)
 17      VALUES (SSV_SECURE_SEQ.nextval,v_ccn,v_ssv_secure_group_id,v_ssv_secure_variable_id,V_LAST_MODIFIED_DATE,
 18                 sys.DBMS_CRYPTO.
                encrypt(UTL_RAW.cast_to_raw(v_value1 ||v_value2),
 19   20                     8  + 256 + 4096,
 21                     UTL_RAW.cast_to_raw(encrypt_decrypt_key)));
 22  --DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34912534
It doesn't matter that your column is a long raw, the SQL statement itself is failing because you are generating a raw string that is too long

Here's a simple test to demonstrate


create table rawtest (lr  long raw);

insert into rawtest (lr) values(utl_raw.cast_to_raw(rpad('test',2000,'test')));   --- this will work because the generated RAW is 4000 bytes long

insert into rawtest (lr) values(utl_raw.cast_to_raw(rpad('test',2001,'test')));   --- this will fail because the generated RAW is 4002 bytes long
0
 

Author Comment

by:vadicherla
ID: 34913215
Do we have solution for this.. Can you please let me know
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question