Home » RDBMS Server » Security » Use of dbms_crypto packageto encrypt table columns (Oracle 10g 10.2.0.3 on Linux 64 bit)
Use of dbms_crypto packageto encrypt table columns [message #424282] Thu, 01 October 2009 04:33 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
I am tryiing to use dbms_crypto package for the first time to encypt my tables column
Following are my table columns

NAME1 VARCHAR2(2000),
ID1 NUMBER,
SCORE number

This table is already populated

i want to encrypt Name1 and Score column. Following are the functions i have created for Encryption and decryption.

--For Encryption
create or replace function get_enc_val
(
p_in in varchar2,
p_key in raw
)
return raw is
l_enc_val raw (2000);
l_mod number := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
l_enc_val := dbms_crypto.encrypt
(
UTL_I18N.STRING_TO_RAW
(p_in, 'AL32UTF8'),
l_mod,
p_key
);
return l_enc_val;
end;

--For Decryption
create or replace function get_dec_val
(
p_in in raw,
p_key in raw
)
return varchar2
is
l_ret varchar2 (2000);
l_dec_val raw (2000);
l_mod number := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin
l_dec_val := dbms_crypto.decrypt
(
p_in,
l_mod,
p_key
);
l_ret:= UTL_I18N.RAW_TO_CHAR
(l_dec_val, 'AL32UTF8');
return l_ret;
end;

Key: I have stored a key in other schema and calling it by using function get_key().

Following is my insert

INSERT INTO Score_table VALUES
(get_enc_val('John',get_key()),25,get_enc_val(79,get_key()))

it is giving me following error

ORA-00932:Inconsistent Datatypes:Expected number got binary.

I checked, it is an error due to Score field, which is of number type. So do i need to change type of Score field to varchar or is there any other way to encrypt number and date field.

If i need to change the type then what will happen to the data already in Table and how do i encrypt data already in table.

[Updated on: Thu, 01 October 2009 05:05] by Moderator

Report message to a moderator

Re: Use of dbms_crypto packageto encrypt table columns [message #424288 is a reply to message #424282] Thu, 01 October 2009 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your code.

Regards
Michel
Re: Use of dbms_crypto packageto encrypt table columns [message #424664 is a reply to message #424282] Mon, 05 October 2009 04:02 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Do i need to change the number data type to varchar2 as encrypted value will not be number
Re: Use of dbms_crypto packageto encrypt table columns [message #424665 is a reply to message #424664] Mon, 05 October 2009 04:04 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An encrypted value is a binary one so correct datatype is RAW.

Regards
Michel
Previous Topic: create user
Next Topic: Remote Login question
Goto Forum:
  


Current Time: Fri Mar 29 00:58:30 CDT 2024