Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Encryption

Posted on 2011-02-15
8
Medium Priority
?
1,631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Cyber Threats to Small Businesses (Part 2)

The evolving cybersecurity landscape presents SMBs with a host of new threats to their clients, their data, and their bottom line. In part 2 of this blog series, learn three quick processes Webroot’s CISO, Gary Hayslip, recommends to help small businesses beat modern threats.

 

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

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!

Question has a verified solution.

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

Ransomware is a growing menace to anyone using a computer or mobile device. Here are answers to some common questions about this vicious new form of malware.
Will you be ready when the clock on GDPR compliance runs out? Is GDPR even something you need to worry about? Find out more about the upcoming regulation changes and download our comprehensive GDPR checklist today !
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

670 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