Home » SQL & PL/SQL » SQL & PL/SQL » Data Encryption
Data Encryption [message #208692] Mon, 11 December 2006 22:24 Go to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
How can I encrypt input data in Oracle? Using which function? I need to use it in select query.
Re: Data Encryption [message #208693 is a reply to message #208692] Mon, 11 December 2006 22:37 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
HI,
there is one more function used to replace the word by position.
u can change alphabet with another.u can use replace function for that.
Re: Data Encryption [message #208696 is a reply to message #208693] Mon, 11 December 2006 23:00 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
In Replace function we need to used the key string also, means in which characters we want to convert. Is there no any direct encrpt or decrypt functions available in oracle?
Re: Data Encryption [message #208697 is a reply to message #208696] Mon, 11 December 2006 23:07 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
plz send me ur query.
Re: Data Encryption [message #208698 is a reply to message #208697] Mon, 11 December 2006 23:08 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
I need to use function in any select query like...

Select encrypt('ABCDEFG') FROM DUAL;

say for example encrpt is a function.
later on i need to used column name(password) from one of my table as a parameter.
Re: Data Encryption [message #208704 is a reply to message #208698] Mon, 11 December 2006 23:19 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
hi sushma,
as according to my knowleadge there is no function like this in oracle 9i.
but u can use replace function smartly & have the result whatever u want.
Re: Data Encryption [message #208706 is a reply to message #208704] Mon, 11 December 2006 23:20 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
ok thanks
Re: Data Encryption [message #208715 is a reply to message #208706] Mon, 11 December 2006 23:46 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
hi sushma,
i want to know as i m new to this forum ,how do i post my message to this forum publically so that all user can view my mail ?
Re: Data Encryption [message #208721 is a reply to message #208715] Tue, 12 December 2006 00:00 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
you can create it with new topic.
Re: Data Encryption [message #208735 is a reply to message #208715] Tue, 12 December 2006 00:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi akash,

Welcome On Orafaq Forms.

Go through below link
http://www.orafaq.com/forum/h/93410/


hope this helps
Taj
Re: Data Encryption [message #208739 is a reply to message #208735] Tue, 12 December 2006 00:43 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
hi,
thanks alot.
Re: Data Encryption [message #208768 is a reply to message #208706] Tue, 12 December 2006 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid @Akash's knowledge is not encyclopediac.

The package DBMS_OBFUSCATION_TOOLKIT provides tripple DES encryption, and MD5 hashing.

The solution of replacing each letter with another should not be taken seriously, as it is a level of encryption barely reliable for stopping schoolchildren.

Re: Data Encryption [message #208793 is a reply to message #208768] Tue, 12 December 2006 04:47 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
can you give any example.

I have tried creating function using this package. was not successful.

I want function which can be utilized in select query.
Re: Data Encryption [message #208815 is a reply to message #208793] Tue, 12 December 2006 05:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My script:
create function my_encryption(piv_string in varchar2, piv_key in varchar2)
return varchar2
is 
   v_string VARCHAR2(200) := piv_string;
begin
  if mod(length(v_string),8) != 0
  then
    v_string := v_string||lpad(chr(0),8-mod(length(v_string),8),chr(0));
  end if;
  
  return dbms_obfuscation_toolkit.desencrypt(input_string=>v_string,key_string=>piv_key);
end;
/

  
sho err
create function my_decryption(piv_string in varchar2, piv_key in varchar2)
return varchar2
is 
   v_string VARCHAR2(200);
begin 
  v_string := dbms_obfuscation_toolkit.desdecrypt(input_string=>piv_string,key_string=>piv_key);
  return rtrim(v_string,chr(0));
end;
/
sho err

col x format a30
col y format a30
select my_encryption('Hello world', '8bytekey') x
     , my_decryption(my_encryption('Hello world', '8bytekey'),'8bytekey') y
from dual
/

drop function my_decryption
/

drop function my_encryption
/

My output:
SQL> @orafaq

Function created.

No errors.

Function created.

No errors.

X                              Y
------------------------------ ------------------------------
Z┴4♠`l▄(2☺>D½░⌐               Hello world


Function dropped.


Function dropped.


MHE
Re: Data Encryption [message #208831 is a reply to message #208815] Tue, 12 December 2006 05:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Maaher and JRowBottom saved the day (as usual).
Just to add, there is something called SQLNET encryption.
With a few changes in sqlnet.ora you can acutally ecnrypt all the traffic from and to the server over the network. It does not though ecnrypt the data within database.
Re: Data Encryption [message #208844 is a reply to message #208815] Tue, 12 December 2006 06:14 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
select my_encryption('abcdef','%^ppp') from dual

ORA-28234: key length too short
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 126
ORA-06512: at "ABN_DOORSTEP.MY_ENCRYPTION", line 11


It is giving me this error?
Re: Data Encryption [message #208861 is a reply to message #208844] Tue, 12 December 2006 06:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
naiksushma wrote on Tue, 12 December 2006 13:14
select my_encryption('abcdef','%^ppp') from dual
you need an 8 byte key: 8 bytes, 16 bytes, 24 bytes, ... but not 5 like yours ('%^ppp').

MHE
Re: Data Encryption [message #208996 is a reply to message #208861] Tue, 12 December 2006 21:53 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
oh thanks. its working. but key has to be 8 bytes minimum,right? and moreover have to remember key.
Re: Data Encryption [message #209020 is a reply to message #208996] Tue, 12 December 2006 23:38 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
BUT ONE MORE THING
IF I TRIED OUT LIKE THIS

SELECT dbms_obfuscation_toolkit.desencrypt('ABNABN','PPP111PP') FROM DUAL;

WITH 8 BYTES KEY.

ITS GIVING ME ERROR :

TOO MANY DECLARATION OF 'DESENCRYPT' MATCH THIS CALL.

WHY?
Re: Data Encryption [message #209027 is a reply to message #209020] Wed, 13 December 2006 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have to use named parameters instead of positional parameters.
Next time, please Google for your error-code (pls-00307) before asking here...
And hit that CAPS-LOCK again, please.
Re: Data Encryption [message #209032 is a reply to message #209027] Wed, 13 December 2006 00:41 Go to previous messageGo to next message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
i have already searched in google but could not able to relate it with my problem.

and will take care of caps lock.

thanks
Re: Data Encryption [message #209042 is a reply to message #209032] Wed, 13 December 2006 01:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The error is clear: there are too many declarations of the same function that match that call. dbms_obfuscation_toolkit.desencrypt('ABNABN','PPP111PP') could be the varchar2 variant or the raw variant. They both take two input parameters and, given your input, Oracle cannot decide which variant you want to call. Rather than guess which one you want, it will throw an error. This mechanism (same procedure, same number of input and output parameters but different datatypes/names) is called overloading. The drawback is that you have to make clear to Oracle that you want a specific variant. One way of doing that is, like Frank said, using named parameters. Instead of doing:
dbms_obfuscation_toolkit.desencrypt('ABNABN','PPP111PP')

You 'll have to use
dbms_obfuscation_toolkit.desencrypt(input_string=>'ABNABN',key_string=>'PPP111PP')
It will tell Oracle exactly what variant of the function you want to use. But this named assignment cannot be used directly in a SQL query. So I wrapped it in my own PL/SQL functions.

MHE

[Updated on: Wed, 13 December 2006 01:03]

Report message to a moderator

Re: Data Encryption [message #236558 is a reply to message #209042] Thu, 10 May 2007 04:42 Go to previous messageGo to next message
nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam
Member
Cool
1. Hehe, It need change 8 => 7
  if mod(length(v_string),8) != 0 then
    v_string := v_string||lpad(chr(0),8-mod(length(v_string),8),chr(0));
  end if;

=>
v_string := v_string||lpad(chr(0),7-mod(length(v_string),8),chr(0));

OK?
2. I test
Code 1, code 2, code 3
code 1: end string: 6789, code 2: end string: 1234
code 2: key: 8bytekey8bytekey, code 3: 8bytekey
select my_encryption('1234567890123456789012345678901234567890123456789012345678906789', '8bytekey8bytekey') x
     , my_decryption(my_encryption('1234567890123456789012345678901234567890123456789012345678906789', '8bytekey8bytekey'),'8bytekey8bytekey') y
from dual
/


select my_encryption('1234567890123456789012345678901234567890123456789012345678901234', '8bytekey8bytekey') x
     , my_decryption(my_encryption('1234567890123456789012345678901234567890123456789012345678901234', '8bytekey8bytekey'),'8bytekey8bytekey') y
from dual


select my_encryption('1234567890123456789012345678901234567890123456789012345678901234', '8bytekey') x
     , my_decryption(my_encryption('1234567890123456789012345678901234567890123456789012345678901234', '8bytekey'),'8bytekey') y
from dual

Result always: X = "・t・Cラobエヒ釆,`uェ"
Please tell my why?

3. If data is varchar2(64), can I know maxlength after encrypt data?

[Updated on: Thu, 10 May 2007 04:44]

Report message to a moderator

Re: Data Encryption [message #236600 is a reply to message #236558] Thu, 10 May 2007 06:09 Go to previous messageGo to next message
nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam
Member
Haha, i have a problem very funny.
create table t1(c1 number, c2 varchar2(20));

I want insert c2 = "1234" to t1 table. After insert, I must encrypt value c2.

create or replace trigger trg1
BEFORE insert on t1
for each row
begin
   :new.c2 := MY_ENCRYPTION (?????, '12345678');
end;

???? is value of new.c2. Very Happy

Thanks

NghiaNT.
Re: Data Encryption [message #238388 is a reply to message #209032] Thu, 17 May 2007 14:59 Go to previous message
pranab_garg
Messages: 1
Registered: May 2007
Junior Member
hi
this proc will encrypt once for the whole table and also takes care of your problem of 8,16,24 bit key length and also a plus point you dont have to remember the key coz m deriving the key from the col itself. Also if you want that no one shld view it then after compiling the procedure then use wrap utility to encrypt the procedure itself and remove the original proc file and compile the encrypted file. note once encrypted there is no utility till date designed to unwrap it.

create or replace procedure enc_pwd
is
cursor cr is
select T2_SMS_TR_EMP_PWD ,T2_SMS_TR_EMP_ID from syn_emp for update of T2_SMS_TR_EMP_ID;
input_string varchar2(16);
encrypted_string varchar2(2048);
key_string varchar2(8);
err_in_input_buffer exception;
pragma exception_init(err_in_input_buffer,-28000);
err_msg varchar2(60) := 'Input not in multiples of 8';
begin
for emprec in cr
loop
input_string:=emprec.T2_SMS_TR_EMP_PWD;
if length(emprec.T2_SMS_TR_EMP_PWD)!=16 then
input_string:=rpad(input_string,16,'*');
end if;
key_string:=to_char(emprec.T2_SMS_TR_EMP_ID)||'8';
dbms_obfuscation_toolkit.DESEncrypt(input_string=>input_string,key_string=>key_string,
encrypted_string=>encrypted_string);
update syn_emp
set T2_SMS_TR_EMP_PWD=encrypted_string where current of cr;
end loop;
exception
when err_in_input_buffer then
dbms_output.put_line(err_msg);
end;
Previous Topic: Performance issue during delete
Next Topic: difference between B-Tree and Bitmap indexes
Goto Forum:
  


Current Time: Sat Dec 03 20:12:23 CST 2016

Total time taken to generate the page: 0.04431 seconds