Solved

Oracle Encryption

Posted on 2011-02-15
8
1,561 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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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 73

Accepted Solution

by:
sdstuber earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Cybersecurity has become the buzzword of recent years and years to come. The inventions of cloud infrastructure and the Internet of Things has made us question our online safety. Let us explore how cloud- enabled cybersecurity can help us with our b…
When the confidentiality and security of your data is a must, trust the highly encrypted cloud fax portfolio used by 12 million businesses worldwide, including nearly half of the Fortune 500.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

770 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