dbms_obfuscation_toolkit
L. Lugo, May 21, 2001 - 5:02 pm UTC
Even though, this might have some use. I would have
prefer to have seen this example in a procedure since
most Encryption is done from external apps.
Encryption
Senthil, August 07, 2001 - 6:55 am UTC
Thanks for the example. Good one
Great.. how to protect the key?!
Doug, November 16, 2001 - 10:16 am UTC
Hey Tom - good info. As you said, the catch is to protect the key. If I want a very quick simple solution to encrypting passwords stored in the database I can use something similar to this, but how can I protect this key? To keep it from showing up in tkprof, I can use a bind variable, but if I use a bind variable in a procedure then it will be defined in the code and show up in the all_source view if a user has execute on it, no?
November 16, 2001 - 10:50 am UTC
Well, I would never use encryption for passwords -- I would use a digest or hash. In 817 we have a md5 routine in dbms_obfuscation_toolkit for secure hashes, before that see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:95412348059 <code>
How to protect the key -- the person who can answer that without a single chink in the armor wins a prize.
You can stuff it in an OS file on the server, you could have the client send it over, you could make the key a function of the username and other user attributes (security via obscurity).... There are many techniques you can take (i outline a couple in my book in the dbms_obfuscation_toolkit) each has their drawbacks though.
Protect the Key
A reader, November 16, 2001 - 12:14 pm UTC
Have a database function that will return the "Magic Key" and wrap the function.
Will it work ?...
November 16, 2001 - 1:51 pm UTC
Yes, but you'll want to break the key up even so. something like:
is
l_magic_key varchar2(8);
l_dummy varchar2(8);
begin
l_magic_key := chr(123);
l_dummy := 'aafdafda';
l_magic_key := l_magic_key || chr(23);
l_dummy := 'erq fa';
l_magic_key := l_magic_key || chr(122);
...
return l_magic_key;
end;
to further obscure it (else the variable value would just be sitting there in the wrapped text for easy reading)
And even then, you have to protect the function -- all i need to do is call the function (if I can see the source, I can probably execute the function!)
Closing in on a solution
Doug, November 16, 2001 - 10:34 pm UTC
Tom, can you elaborate on why you would *never* use encryption for passwords? Why are the other two options, hash and digest so much more preferable? The situation I have is that there are custom app login passwords in a table. Not good.
November 17, 2001 - 10:02 am UTC
Because I would never put myself into a situation where data could be comprimised and it didn't need to be.
It is so much less effort for me to store a hash/digest (effectively the same thing). It is a one way function that takes a big string (the bigger the better) and maps it into a finitite set of numbers. Hence it is not one to one -- and it cannot be undone. You cannot go from the digest to the original string. You use this when someone gives you information and you want to authenticate its validity. If you hash/digest the data they give you and you get the same hash you were expecting -- the odds are one in many billions that they "guessed" another lucky string that happened to match. It is the way digital signatures are generated as well. I can take my document (say an email) and "sign it" by hashing it. I'll incorporate my certificate (x509) with the message -- hash it and put the number i get into the message. When you recieve it -- you can take the original message + my certificate and hash it. If you do not get the same number -- someone either changed the message or forged my signature.
With hashes, I do not have to worry about someone discovering my key, getting into my database, dumping my table of passwords and decoding them all. With a hash, they would have to guess at each users password -- run it through the hashing routine and compare the hashes. Very time consuming -- and if I through a bit of "salt" in there and combine it in some fashion with the username -- they might never figure it out unless they have my algorithm as well. For exmaple, I might hash:
'Hello' ||
substr(username,1,4) || substr(password,1,4) || substr(username,5) ||
substr( password,5 ) || 'World'
unless they knew that as well -- they could never guess the password. Its just more secure without me having to protect a key. Remember encrypted data is ONLY as secure as the key. Since this has no key -- its pretty secure.
About key
Balasubramanian Rengasamy, November 17, 2001 - 5:41 am UTC
Hi Tom,
Instead of protecting and preserving the key, can i use the password itself as key like :
if length of password > 8
key := substr(password, 1, 8) ;
else
key := rpad(password, 8, chr(0) ) ;
Is there any problem will happen using this .
Thanks
Bala
November 17, 2001 - 10:09 am UTC
Yes you can -- but then it is working just like a hash (but it is isomorphic, one to one -- you can get the password back if they give you the password).
Since you never really need the password -- and you cannot get the password back for a user who "lost theirs" (as that is the key) i see NO benefit in encrypting the password (you only encrypt when you need to decrypt, here you NEVER need to decrypt, just to verify the password is correct).
So, yes you can but to what end?
Help to both of us
AanyGary, November 17, 2001 - 6:31 am UTC
This is very help to me and Anu
what am i doing wrong???
Senthil, May 08, 2002 - 10:30 am UTC
Tom-
I am using 8.1.7 for Solaris. I am trying to run the same sql given in the example. Its not decrypting properly. Am i missing something???
SQL> var a varchar2(255)
SQL> var b varchar2(255)
SQL> var c varchar2(255)
SQL> exec :a := 'How Now Brown Cow';
PL/SQL procedure successfully completed.
SQL> print a
A
--------------------------------------------------------------------------------
How Now Brown Cow
SQL> exec dbms_obfuscation_toolkit.DESEncrypt(input_string=> rpad(:a, (trunc(length(:a)/8)+1)*8, chr(0)), key_string => 'MagicKey', encrypted_string => :b);
PL/SQL procedure successfully completed.
SQL> print b
B
--------------------------------------------------------------------------------
Or?=?Yw]??SW?:Y?oE?T?
SQL> exec dbms_obfuscation_toolkit.DESDecrypt(input_string=> :b, key_string => 'MagicKey', decrypted_string => :c);
PL/SQL procedure successfully completed.
SQL> print c
C
--------------------------------------------------------------------------------
ji5N?u++1aea?a?A$T???io
May 08, 2002 - 12:10 pm UTC
My example used character strings -- your NLS_LANG in the client (sqlplus) must be different then the NLS_LANG in the server.
Hence, the "string" in B is being "character set translated" -- bits are getting stripped off.
Consider:
$ csh -vx test.csh
echo $NLS_LANG
echo AMERICAN_AMERICA.WE8ISO8859P1
AMERICAN_AMERICA.WE8ISO8859P1
sqlplus / @test.sql
sqlplus / @test.sql
SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 8 12:07:05 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
ops$tkyte@ORA817DEV.US.ORACLE.COM> variable x varchar2(25)
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec :x := 'How Now Brown Cow';
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_data varchar2(255);
3 begin
4 l_data := rpad( :x, (trunc(length(:x)/8)+1)*8, chr(0) );
5 dbms_obfuscation_toolkit.DESEncrypt( input_string=>l_data, key_string=>'MagicKey', encrypted_string=>:x );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> print x
X
--------------------------------
Òr?=ðÝw]ð?SWñ:ÝÐôÊ?T¬
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_data varchar2(255);
3 begin
4 dbms_obfuscation_toolkit.DESDecrypt( input_string=>:x, key_string=>'MagicKey', decrypted_string=>l_data );
5 :x := rtrim( l_data, chr(0) );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> print x
X
--------------------------------
How Now Brown Cow
ops$tkyte@ORA817DEV.US.ORACLE.COM> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
<b>unsetenv NLS_LANG</b>
unsetenv NLS_LANG
sqlplus / @test.sql
sqlplus / @test.sql
SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 8 12:07:05 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> variable x varchar2(25)
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec :x := 'How Now Brown Cow';
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_data varchar2(255);
3 begin
4 l_data := rpad( :x, (trunc(length(:x)/8)+1)*8, chr(0) );
5 dbms_obfuscation_toolkit.DESEncrypt( input_string=>l_data, key_string=>'MagicKey', encrypted_string=>:x );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> print x
X
--------------------------------
Or?=?Yw]??SW?:Y?oE?T?
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_data varchar2(255);
3 begin
4 dbms_obfuscation_toolkit.DESDecrypt( input_string=>:x, key_string=>'MagicKey', decrypted_string=>l_data );
5 :x := rtrim( l_data, chr(0) );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> print x
X
--------------------------------
ji5N?u++1aea?a?A$T???io
ops$tkyte@ORA817DEV.US.ORACLE.COM> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
<b>in this case, my db character set is WE8ISO and when the NLS_LANG matches -- no worries. When it doesn't, (when I let it default to US7ASCII as it does on Unix), the next run through does exactly what yours does! We've gone through character set conversion.
I recommend use of RAW types for all encrypted data (using utl_raw.cast_to_raw to magically turn a varchar2 into a raw without touching it). No character set conversions with RAW types.
data encryption package
Jamil Shaibani, October 15, 2002 - 9:39 am UTC
Hi Tom
I have used dbms_obfuscation_toolkit , to Decrypt this data
29 6B60638A8C932A11 by using this example:
variable x varchar2(55);
exec :x := '29 6B60638A8C932A11';
declare
l_data varchar2(255);
begin
dbms_obfuscation_toolkit.DESDecrypt
( input_string => :x,
key_string => 'MagicKey',
decrypted_string=> l_data );
:x := rtrim( l_data, chr(0) );
end;
but I am getting this errors message
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 153
ORA-06512: at line 4
Thanks
Jamil
October 15, 2002 - 9:54 am UTC
:x has a blank in it. that is not proper.
Also, that string doesn't look "encrypted" to me. It would have squiggles and funky characters.
That looks like a hex string to me, thats NOT encrypted. I've no idea what it is but I know what it isn't -- it isn't an encrypted string.
About your data encryption package
Jeff Li, March 11, 2003 - 10:43 am UTC
Hi Tom,
Thanks for this great site. I got a lot of help from it.
I've created the encryption and decryption functions based on your method. I used Oracle 8.1.7.0 server. The functions worked very well on this server. But after I installed the patch 8.1.7.4 recently, all my *old* encrypted data (stored in the db before the patch) couldn't be decrypted any more. I didn't change anything for the functions. And they still worked well for the *new* data.
It will be a huge trouble for our company if we can't get the old data back. Please give me a help.
Thanks,
Jeff
March 11, 2003 - 11:10 am UTC
they "fixed" the key byte order problem.
You have an 8 byte key right now -- say the key was:
12345678
the key to unlock your data is:
43218765
swap the bytes -- in your decrypt just use the key:
substr(key,4,1)||substr(key,3,1)||substr(key,2,1)||substr(key,1,1)||
substr(key,8,1)||substr(key,7,1)||substr(key,6,1)||substr(key,5,1)
let me know if that works -- it only affects certain hardware architectures. Used to be that before this was fixed -- data encrypted on windoze couldn't be decrypted on sparc and vice versa due to key addressing (they address it as 2 4 byte integers internally and high endian/low endian messed it up)
about data encryption package
Jeff Li, March 11, 2003 - 10:45 am UTC
Sorry, forgot to tell you that we use Oracle server on Windows 2000.
Thanks,
Jeff
data encryption package
Jeff Li, March 11, 2003 - 11:32 am UTC
Tom:
Thank you very much for the help. I got my data back and everything works very well now.
Great appreciate!!!
Jeff
Excellent
Mike, March 11, 2003 - 12:29 pm UTC
Hi Tom,
Hopefully my problem is all kind of related to this. As part of our software we want to supply a very powerful toolkit which has extra functionality in it for specific problems that we may encounter on customer sites. We have tried before this sort of thing, but the problem is that once something is "given out" it is not possible to "get it back". we don't want people being able to have access to this all the time, only when we give them specific cause to use it.
As such we were thinking of supplying a totally wrapped package (Header and body). as part of this the package would contain some initialsation code which would
* read a user supplied *password* from dbms_application_info
* use a concationation of the system date truncated to a whole day in various formats to make it a multiple of 8 as the key
* use some hard-coded concatenated text as the text to encrypt
* check what they supplied is what we worked out, if it's not raise an error.
we would give a routine to our support team such that they could generate the keys for users on an as needed day by day basis (or even create them for future / previous days). after encyting the string we would then send it out as a list of numbers each number being the ascii number for that charecter e.g 192-15-133....
My questions I suppose are 3 fold.
1) Is this just way over the top, I started off by quite liking the use of sysdate as a self changing part of the encryption. but After reading the above I start to get the feeling I'm writing it because I think it would be "cool"... perhaps just a hash function would suffice?
2) We can make no assumptions about how the database will be set up. It's NLS_LANG settings could be anything. Only the fact it will be atleast Oracle 8.1.7. Would we be able to work around this by being explicit in our date formats
3) further to two we can't even say what charecter set the database will be in, it quite possible it will be some Asian 2 bytes per charecter charecter set. I assume we would need someway of converting the text into a "known" charecter set before we cast it to a raw? Is this possible?
Thanks as always,
Mike.
March 11, 2003 - 12:41 pm UTC
not sure -- i don't get the entire algorithm here.
why not this....
You have your "salt" -- the date YYYYMMDD plus some magical string
You have your "key" you give them, say 'Hello World'
You have your "hash" you give them.
So, you provide them 2 things -- key and hash.
They in turn must supply this key and hash to your package.
Your package uses dbms_obfuscation_toolkit.md5 to generate a md5 checksum of the KEY+SALT. compares that to the hash -- if equal - that code will run that day. If not, that code will not run that day.
Tomorrow, you need to give them a new key + hash.
It's easier if I just post the few lines of code...
A reader, March 17, 2003 - 12:35 pm UTC
Hi Tom,
This is how I would generate the key... the input string would be a lot longer than just the DD/MM/YY, but as that is 8 charecters, it was handy for testing purposes...
PC_KEY is a private constant that is build up through layers of code as you have previously said so you cannot "see it" in the wrapped code...
they would then type in the return, and the wrapped code would just generate the key and compare it to what they typed in...
function gen_key return varchar2 is
l_encrypt varchar2(100);
l_return varchar2(100);
begin
l_encrypt := dbms_obfuscation_toolkit.desencrypt
( input_string => to_char(sysdate,'DD/MM/YY'),
key_string => PC_KEY
);
for i in 1..length(l_encrypt) loop
l_return := l_return||ascii(substr(l_encrypt,i,1))||'-';
end loop;
l_return := substr(l_return,1,length(l_return) -1);
return l_return;
end gen_key;
March 17, 2003 - 1:09 pm UTC
my opinion -- hashing is easier
you give them key+hash, done.
why is this ?
A reader, April 22, 2003 - 1:12 pm UTC
SQL> exec :x := 'How Now Brown Cow';
PL/SQL procedure successfully completed.
SQL> declare
2 l_data varchar2(255);
3 begin
4 l_data := rpad( :x, (trunc(length(:x)/8)+1)*8, chr(0) );
5 dbms_obfuscation_toolkit.DESEncrypt
6 ( input_string => l_data,
7 key_string => 'MagicKey',
8 encrypted_string=> :x );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> print :x
X
------------------------------------------
Rr=p]w]pSWq:]PtJ?T,
SQL> declare
2 l_data varchar2(255);
3 begin
4 dbms_obfuscation_toolkit.DESDecrypt
5 ( input_string => :x,
6 key_string => 'MagicKey',
7 decrypted_string=> l_data );
8 :x := rtrim( l_data, chr(0) );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> print :x
X
---------------------------------------------
~ QiPei8L .mZ2Z hz0E
SQL>
** Why is this ?
April 22, 2003 - 9:35 pm UTC
look up in the page here.... been there, done that.
DBMS_OBFUSCATION_TOOLKIT
Sachin, July 31, 2003 - 2:28 pm UTC
I have a table with user id , encrypted password and the key string. I have successfully executed the decryption in SQL*Plus.I need to do the same in Visual Basic. How can I return a record set so that VB can get it as a varriable.
Please help
Thank you
Sachin
July 31, 2003 - 7:04 pm UTC
8
A reader, July 31, 2003 - 11:53 pm UTC
Tom,
Not able to ubderstand why this.
l_data := rpad( :x, (trunc(length(:x)/8)+1)*8, chr(0) );
If I am not using the avobe I am getting error
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 33
ORA-06512: at line 6
Could please clarify?
Thank you very much.
August 01, 2003 - 7:45 am UTC
because the answer started with:
...
Sure, the trick is to make sure everything is a multiple of 8...
.....
in order to encrypt, you need a block of data that is a multiple of 8 bytes.
What does the kernel use to generate user passwords?
Justin, November 26, 2003 - 12:54 pm UTC
What does the Oracle kernel (? is that was generates them)
use to generate user passwords in the database? How does it created the value in the password column of _USERS views?
Has anyone figured out how to crack oracle passwords that you know of?
For example:
justin@DEV> create user loser identified by dorko;
User created.
justin@DEV> select password from dba_users where username ='LOSER';
PASSWORD
==============================
EF7D03F009BD9BDB
justin@DEV> create user homer identified by dorko;
User created.
justin@DEV> select password from dba_users where username ='HOMER';
PASSWORD
==============================
7C927170489C87F3
justin@DEV> drop user loser;
User dropped.
justin@DEV> create user loser identified by dorko;
User created.
justin@DEV> select password from dba_users where username ='LOSER';
PASSWORD
==============================
EF7D03F009BD9BDB
and furthermore:
justin@DEV> create user lose identified by rdorko;
User created.
justin@DEV> select password from dba_users where username ='LOSE';
PASSWORD
==============================
EF7D03F009BD9BDB
So the password appears to be based on a combination of the username and the password without a time element.
It seems that this password mechanism would be easily cracked?
Yet, I must be wrong...
Can you shed some light on my findings and add some discussion/thought to this topic?
Thanks much!
November 26, 2003 - 2:42 pm UTC
it is a hashed version of your username and password.
go ahead, crack it. It is just a one way hash, it is not reversible. You have to brute force the system and guess passwords, you cannot "decrypt" them.
How come you can
A reader, December 23, 2003 - 5:26 pm UTC
alter user loser identified by values then?
How does it take the hash and recreate the password?
Funny Output
Shaji Nair, January 07, 2004 - 12:44 am UTC
Hi Tom,
I have an encryption function which encrypted a string and stored in a table. The encrypted string is
SQL> select search_condition from ice_lookups
2 where rownum = 1
3 /
SEARCH_CONDITION
-------------------------------------------------------------------------------------------------------------------
=F9:D=<W>D9?@24¿5@24¿@31¿Q@31¿@24¿9F<@24¿LJMF;$32¿FND$32¿=F<W<9L=W9;LAN=$KQK<9L=!!@24¿56@24¿LJMF;$32¿KQK<9L=!
Now when i decrypt this i get like this
1 select ice_encrypt_decrypt.decrypt(search_condition) from ice_lookups
2* where rownum = 1
SQL> /
ICE_ENCRYPT_DECRYPT.DECRYPT(SEARCH_CONDITION)
---------------------------------------------------------------------------------------------------------
ENABLED_FLAGH:<=H:<H;9YH;9H:<ANDH:<TRUNC,;:NVL,;:END_DATE_ACTIVE,SYSDATE))H:<=>H:<TRUNC,;:SYSDATE)
Amazingly when i decrypt the string i get the following correct output
SQL> select ice_encrypt_decrypt.decrypt('=F9:D=<W>D9?@24¿5@24¿@31¿Q@31¿@24¿9F<@24¿LJMF;$32¿FND$32¿=F<W<9L=W9;LAN=$KQK<9L=!!@24¿56@24¿LJMF;$32¿KQK<9L=!') from dual;
ICE_ENCRYPT_DECRYPT.DECRYPT('=F9:D=<W>D9?@24¿5@24¿@31¿Q@31¿@24¿9F<@24¿LJMF;$32¿FND$32¿=F<W<9L=W9;LAN=$KQK<9L=!!@24¿56@24¿LJMF;$32¿KQK<9L=!')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ENABLED_FLAG = 'Y' AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) => TRUNC(SYSDATE)
This has created a headache, What's the reason for behaving in this improper manner.
January 07, 2004 - 7:45 am UTC
it doesn't look improper. Looks like you are using a STRING to encrypt into (which I've learned is a truly, utterly BAD idea) and your character set on your client differs from the db's!
when you encrypt a string, you get "binary data". binary data stored in a string. it is subject to character set translations.
Look upwards in this page for "what am i doing wrong" and see that review/followup. it describes this in detail
I suggest you
a) change the encrypted column to be RAW -- or if you want to leave it as varchar2 -- double its width (we'll be storing HEX in there instead)
b) use utl_raw.cast_to_raw to convert the varchar2 string to be encrypted into a RAW.
c) then encrypt the RAW, not a varchar2 -- this results in a raw
d) either store the raw in a raw or in a varchar2 that is 2x the size of the raw (it'll be rawtohex'ed for you)
e) upon retrieval -- you would fetch a raw or a varchar2 hex string. you would use the raw decrypt (raw will just go, the varchar2 hex would be hextoraw'ed for you)
f) use utl_raw.cast_to_varchar2 in order to convert the decrypted data back into a safe varchar2 string.
Thank You Tom
Shaji Nair, January 08, 2004 - 12:42 am UTC
Thanks for your inputs Tom. I will test this and will let you know.
Using different charsets
Titi Ala'ilima, June 22, 2004 - 3:07 pm UTC
We migrated from using a US7ASCII db to a AL32UTF8 db. Under the ASCII db, we could pad with CHR(0), but I discovered that we needed to pad with spaces for it to work under the Unicode db. (kept getting ORA-06512) So in migration, we decrypted and reencrypted the data on the ASCII db to use the different padding scheme. Except for one set of data, that is. Now the old db is long gone and I have a Unicode db that can't read the data that was encrypted in the ASCII db.
One other wrinkle: most of the data in the ASCII db was actually Unicode, so in order to defeat imp's attempts to translate what it thought was ASCII into Unicode, we used a hex editor to change several sequences of x0001 to x0369 (bases on suggestions by Branimir Dolicki at </code>
http://rhea.redhat.com/bboard-archive/webdb/0006jS.html <code> so that imp figured the file really did contain Unicode after all.
The original db was 9.0.1, and I have at my disposal 9.2 and 10.1.0. All the data is stored in varchar2 columns.
How can I get this data into a usable state, preferably padded with blanks and encrypted?
June 22, 2004 - 9:28 pm UTC
if you have ascii data hidden in a utf8 database -- your best and only bet that I would rely on would be to decrypt the data in a ascii database, load that into the utf8 database and decrypt it.
Correction to previous comment
Titi Ala'ilima, June 22, 2004 - 4:18 pm UTC
The main error I was encountering, and still am, is not ORA-06512 but ORA-28232: invalid input length for obfuscation toolkit
I've tried all manner of charset conversions and casts but haven't hit upon the right permutation yet. If I understand you right, the data in the ASCII db was converted from binary into ASCII and stored in the varchar. How can I recover the pre-conversion binary data, unencrypt it, correct the padding, and encrypt and store it like the other data. (I'm not ready yet to switch over to using raws, but I certainly plan to do so when we have the time.)
June 22, 2004 - 9:44 pm UTC
did you use varchar or raw types in the database?
if you used varchar and exp/imported it -- what you have is a totally unmigated and unfixable *mess* as the "encrypted data" was converted using ascii-> utf8 rules (but they were not really characters)
Lucky me
Titi Ala'ilima, June 22, 2004 - 10:22 pm UTC
After reading a few more related articles, I figured out a reasonable way to go:
1) create a database that uses US7ASCII encoding.
2) import the data (luckily I still had the original data file that I used to import into the new db) into the ascii db.
3) recrypt the desired data into a table with raw columns
4) export the recrypted table
5) import the new table into the new db
6) recrypt the raw data into Unicode varchars.
I know ultimately I'll want to move over to raws entirely, and I'm confident now that I can do it successfully given this experience.
Comparing 2 encrypted values
Vikram Romeo, July 11, 2006 - 12:31 pm UTC
Hi Tom,
Is it valid to compare two encrypted values(stored in VARCHAR2 fields)?
Will the comparison work out fine?
Basically, we have 2 systems which use the same algorithm to encrypt an account number. Now when I am trying to join tables in these 2 systems, I need to use the encrypted account number. We are not given access to the decrypt function.
Now, the question is, will the direct VARCHAR2 comparison work fine?
Appreciate your response ...
Regards,
Vikram Romeo
Comparing 2 encrypted values
Vikram Romeo, July 12, 2006 - 5:53 pm UTC
That was useful Tom. However , after going through the link, seems like it is going to be a problem only when you are running interactive SQL in sqlplus (since it is related to the character set mismatch between client from which we are running the SQL and the database). My question looks stupid to myself ... ;-) But then looks like I dont understand this completely.
Is this going to be a problem even when I compare encrypted VARCHAR2 values in two tables without having to use bind variables like :x = some_encrypted_value? or is it that the character set conversion happens even when we compare values in the tables just like that say using a join or something?
My requirement is this - I just want to use the encrypted_columns (which is stored as VARCHAR) in a join condition in a SQL within a stored proc. So, since a stored proc should run entirely in the server space, will the character set conversion happen here?
I am totally confused Tom .. Please guide me out ...
Regards,
Vikram Romeo
July 12, 2006 - 5:59 pm UTC
no, it is a problem whenever any clients characterset doesn't match the servers characterset (eg: this is a huge problem lying in wait, this will burn you, it is simply a matter of time)
there is your client character set,
there is your servers character set,
there is the remote servers character set...
It will "likely work" if all of the stars align, but move a single star (characterset) and all bets are off.
This needs to be fixed. Not just for you.
Encrypt clobdata
maher, August 12, 2006 - 6:20 am UTC
declare
how can i Encrypt and dncrypt clob data
i made this:
variable x clob
exec :x := 'sdf sdfj haskfh gaskjgfkajsgf kajsfg kjasgfk jasgf kajsgfkasjgf kasjgf kjasgfkajs';
decalre
l_data clob;
begin
l_data := rpad( :x, (trunc(length(:x)/8)+1)*8, chr(0) );
dbms_obfuscation_toolkit.DESEncrypt
( input_string => l_data,
key_string => 'maherjaber',
encrypted_string=> :x );
end;
and i have this error:
ERROR at line 1:
ORA-22275: invalid LOB locator specified
August 12, 2006 - 7:53 pm UTC
what version of the product are you using here.
Clob Data
maher, August 13, 2006 - 5:32 am UTC
The product version is 8.1.7EE
August 13, 2006 - 9:28 am UTC
so no dbms_crypto - Ok, do you have access to Expert one on one Oracle - my first book? You'll find encrypting a lob to be not so straightforward.
and in 8i, lobs were very different, you had to allocate them, use dbms_lob.writeXXXX to write to them, they could not be used as if there were just strings.
Way way back then, that was true, has not been true for the last 4 releases of the product, but 5 major releases ago, it was true.
how to index encryption data
maher, September 15, 2006 - 12:16 pm UTC
hi tom,
i have a clob column i want to encrypt it ,i made that but the problem how i can make context index on it and make client search in specified world on it.
September 15, 2006 - 12:23 pm UTC
hmm, well.... if you encrypt it, you don't want to see it?
Sorry - but I'm not sure what to tell you - what is the goal behind encrypting this data in the first place? If we text index it - every word will be plaintext visible in the context index - we can do it, but hey - if you are encrypting it...
reply how to index encryption data
maher, September 15, 2006 - 12:59 pm UTC
you say "hey - if you are encrypting it..."
that is what i mean .
sorry tom i want to ask how to encrypt world file (i made encryption/dencrytion for text file only) which contain text,pictures,tables and dencrytion it with the same format ?
September 15, 2006 - 1:53 pm UTC
why are you encrypting it - what is the goal behind the encryption.
re how to index encryption data
maher, September 15, 2006 - 2:28 pm UTC
hi again tom
why encryption??? because my company want me to do that.
what i want to say can i dencryption indexed data and then search through it.(i know it is not logical question.)
September 16, 2006 - 2:17 pm UTC
and if you create a text index on that, it'll be stored in the clear.
So, again - are you ok with that (and if so, well, umm, do you need to encrypt? since you can find the documents that contain things like "this, that and the other thing" AND you'll need a function to decrypt it to give it to text - so basically, it seems a waste to encrypt here)
WHAT is the GOAL of encryption here - besides "company wants to", WHY does company want to, what are they trying to achieve.
Karthick Pattabiraman, January 22, 2008 - 8:22 am UTC
I have a table. I want to secure its information. Only the information should be accessed by my program. Of any thing else it must be invisible or encrypted.
Problem in using Encryption:
1. Say I have a flag field char (1) and I encrypt it and get a value greater than 1 I can't store it in there.
2. Same way I can get data type miss match when trying to update.
So have you done any thing like this in the past?
Any help in this regards is much appreciated.
January 22, 2008 - 6:23 pm UTC
problem is encryption is not useful for discretionary access control, never. It is useful to protect data from theft, never for access control.
so, please - just forget about encryption.
do read about virtual private database, also known as fine grained access control, via the dbms_rls package. IT IS about access control. You can use that to make the table appear empty unless your application is accessing the table.
http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvpoli.htm#sthref2179
A reader, January 23, 2008 - 3:55 am UTC
Karthick Pattabiraman, January 23, 2008 - 6:39 am UTC
Hi tom,
I have implemented VPD and its working fine. Thank you.
My question is
1. if the user drops the policy for a given table then he will be having access to the table information. Can i make a policy un changable by any one (only my application can change it).
2. Check out this site
http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php it says
"Although this type of access can be controlled by the application, access via other methods (SQL*Plus) would leave the data open to abuse. "
I dont get it. I tried and its not allowing from any where to acces the table records. My table is entirely secured. What do they mean by that.
January 23, 2008 - 7:57 am UTC
1) your application isn't "anyone". Your application is just code. There is a schema, the schema owner can make changes. LOCK THE SCHEMA - prevent people from logging in as that schema.
2) that is extremely old stuff. Pre-dates the ability to secure by application (9i feature, 5 releases ago now...)
Is the POLICY visible to others
Karthick Pattabiraman, January 24, 2008 - 2:00 am UTC
Document on DBMS_RLS
http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_rls.htm#998100 Says..
"The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement."
Does this mean that the POLICY are not visible to any one (through data dictionary). And only the creater of the policy know that such a policy exist on a given table.
January 24, 2008 - 7:51 am UTC
correct.
Karthick Pattabiraman, February 15, 2008 - 7:51 am UTC
does VPD give access control over TRUNCATE statement.
February 17, 2008 - 6:55 am UTC
a person that can truncate, can truncate - and truncate just releases all allocated space, it doesn't look at the data.
Now, who can truncate?
the owner of the object - but they can do anything, including "drop" it
someone with 'DROP ANY TABLE' - but they can drop it too - so, not really relevant. Meaning, if you give someone drop any table, you have given them "quite a bit"
Problem in Decrypting
Jaspreet Nagra, August 06, 2009 - 9:22 pm UTC
Hi Tom,
I am facing problem in decrypting a value. I am getting "ORA-28232: invalid input length for obfuscation toolkit". Though the other ids of same lenght are coming up correctly.
ENCRYPTING Code:
FUNCTION DO_ENCRYPT(P_STRING IN VARCHAR2) RETURN VARCHAR2
IS
l_data VARCHAR2(4000);
vStr VARCHAR2(4000);
BEGIN
IF P_STRING IS NOT NULL THEN
vStr := P_STRING;
If mod(length(p_string),8) <> 0 Then
l_data := rpad( vStr, (trunc(length(vStr)/8)+1)*8, chr(0) );
Else
l_data := vStr ;
End If;
dbms_obfuscation_toolkit.desencrypt( input_string => l_data,key_string => 'TIN_Or_SSN',encrypted_string=> vStr);
END IF;
RETURN vStr;
END DO_ENCRYPT;
DECRYPTING CODE:
FUNCTION DO_DECRYPT(P_STRING IN VARCHAR2 ) RETURN VARCHAR2
IS
l_data VARCHAR2(4000);
vStr VARCHAR2(4000);
BEGIN
IF P_STRING IS NOT NULL THEN
dbms_obfuscation_toolkit.DESDecrypt( input_string => vInput, key_string => 'TIN_Or_SSN',decrypted_string=> l_data);
vStr := RTRIM(l_data, chr(0)) ;
END IF;
RETURN vStr;
END DO_DECRYPT;
August 07, 2009 - 9:33 am UTC
you cannot use strings - learned that the painfully hard way over the years.
encrypted data is always RAW data in 16 byte increments. We have varchar2 data here - varchar2 data is subject to CHARACTER SET CONVERSION (eg: it can change as you send it back and forth between client and server). You cannot allow encrypted data to be changed - hence you MUST use raw.
consider (I don't care if your character sets are different, the situation exists for ANY and ALL character sets):
database character set: US7ASCII
client character set: WE8ISO8859P1
client retrieves data from database - nothing happens to it (the conversion from us7ascii to WE8ISO8859P1 is lossless - we just leave the data 'as is'). But, you've tricked us, you return data that is NOT a varchar2 in the us7ascii character set - it is 8bit raw encrypted data hiding in a string.
client sends data back to database. Well, the conversion from WE8ISO8859P1 to US7ASCII is lossy - we strip the high bit and make everything fit in 7 bits
You have just broken the encrypted data - you can never decrypt it again, ever.
ctl-f on this page for:
My example used character strings
Problem in Decyrpting
Jaspreet Nagra, August 06, 2009 - 9:48 pm UTC
In continuation to my above post .. The strange part is that the same numbers (data) works fine for other tables mean when I used the number as value of other tables then it's working.
RE: Facing Problem in Decryppting
Jaspreet Nagra, August 07, 2009 - 4:34 pm UTC
Hi Tom,
Thanks for quick and helpful response. I tried testing RAW instead of varchar2. When I am make a PL/SQL and run, it works fine. But when I making function and trying to return the value I am facing following problem in decrypting:
ERROR at line 3:
ORA-01465: invalid hex number
Table Strucure
id number(5)
tid raw(512)
Functions:
CREATE OR REPLACE FUNCTION DO_ENCRYPT_TEST(P_STRING IN VARCHAR2) RETURN RAW
IS
input_string VARCHAR2(256);
raw_input RAW(256);
key_string VARCHAR2(8) := 'TINOrSSN';
raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(256);
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) := '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
BEGIN
IF P_STRING IS NOT NULL THEN
input_string := P_STRING;
If mod(length(input_string),8) <> 0 Then
input_string := rpad(input_string,(trunc(length(input_string)/8)+1)*8,chr(0));
End If;
raw_input := UTL_RAW.CAST_TO_RAW(input_string);
dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,key => raw_key, encrypted_data => encrypted_raw);
END IF;
RETURN rawtohex(encrypted_raw);
END DO_ENCRYPT_TEST;
CREATE OR REPLACE FUNCTION DO_DECRYPT_TEST(P_STRING IN RAW) RETURN VARCHAR2
IS
key_string VARCHAR2(8) := 'TINOrSSN';
raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(256);
decrypted_raw RAW(256);
decrypted_string VARCHAR2(256);
BEGIN
IF P_STRING IS NOT NULL THEN
encrypted_raw := P_STRING;
dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,key => raw_key, decrypted_data => decrypted_raw);
decrypted_string := UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw);
END IF;
RETURN decrypted_string;
END DO_DECRYPT_TEST;
Function Calling SQL:
a) Insert Statement :
INSERT INTO test (id,tid) VALUES(1272,DO_ENCRYPT_TEST('2552-49541'));
b) Show SQL: -- This shows me error which I mentioned above
select id, DO_DECRYPT_TEST(tid) from test;
Please help.
August 07, 2009 - 5:13 pm UTC
well, you do not need to rawtohex in the encrypt routine (since you return raw, the rawtohex will have an implicit hextoraw applied to it).
I cannot reproduce your issue.
ops$tkyte%ORA10GR2> create table test ( id number, tid raw(512) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE FUNCTION DO_ENCRYPT_TEST(P_STRING IN VARCHAR2) RETURN RAW
2 IS
3 input_string VARCHAR2(256);
4 raw_input RAW(256);
5 key_string VARCHAR2(8) := 'TINOrSSN';
6 raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
7 encrypted_raw RAW(256);
8 double_encrypt_not_permitted EXCEPTION;
9 PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
10 DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) := '*** CANNOT DOUBLE ENCRYPT DATA -
11 IGNORING EXCEPTION ***';
12 BEGIN
13 IF P_STRING IS NOT NULL THEN
14 input_string := P_STRING;
15 If mod(length(input_string),8) <> 0 Then
16 input_string := rpad(input_string,(trunc(length(input_string)/8)+1)*8,chr(0));
17 End If;
18 raw_input := UTL_RAW.CAST_TO_RAW(input_string);
19 dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,key => raw_key, encrypted_data => encrypted_raw);
20 END IF;
21 RETURN rawtohex(encrypted_raw);
22 END DO_ENCRYPT_TEST;
23 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE FUNCTION DO_DECRYPT_TEST(P_STRING IN RAW) RETURN VARCHAR2
2 IS
3 key_string VARCHAR2(8) := 'TINOrSSN';
4 raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
5 encrypted_raw RAW(256);
6 decrypted_raw RAW(256);
7 decrypted_string VARCHAR2(256);
8 BEGIN
9 IF P_STRING IS NOT NULL THEN
10 encrypted_raw := P_STRING;
11 dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,key => raw_key, decrypted_data => decrypted_raw);
12 decrypted_string := UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw);
13 END IF;
14 RETURN decrypted_string;
15 END DO_DECRYPT_TEST;
16 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO test (id,tid) VALUES(1272,DO_ENCRYPT_TEST('2552-49541'));
1 row created.
ops$tkyte%ORA10GR2> select id, DO_DECRYPT_TEST(tid) from test;
ID
----------
DO_DECRYPT_TEST(TID)
-------------------------------------------------------------------------------
1272
2552-49541
what do I need to do to see your issue - cutting and pasting like I always do from sqlplus is preferred.
RE:RE: Facing Problem in Decryppting
Jaspreet Nagra, August 07, 2009 - 5:11 pm UTC
Hi Tom,
It works now so please don't look at it.
Please tell me if there is any problem in using varchar2(512) instead of raw(512) in Table definition.
Thanks,
Jas
August 07, 2009 - 5:19 pm UTC
...
Please tell me if there is any problem in using varchar2(512) instead of
raw(512) in Table definition.
.....
the raw data will be implicitly (or explicitly) converted into a varchar2 HEX string that will be twice as long.
eg: you encrypt 20 characters
we pad out to 32
you then double it to 64 (hex)
so you end up storing 64 characters instead of 32. that will be the "problem", if that isn't a problem for you - it isn't a problem.
encrpytion of passwords while logging in
tk, April 24, 2011 - 3:07 am UTC
Does Oracle provide a way to encrypt passwords while logging in so that the passwords re not compromised while it travels through the wire? From user end to Application server, SSL will take care. From application server to database level, can I setup SSL?
April 25, 2011 - 11:45 am UTC
Passwords used to login are always encrypted, even without the advanced security option or any sort of setup, they are already encrypted.
Redo
A learner, February 19, 2018 - 9:26 am UTC
Would you please recode Toms first example using dbms crypto instead of dbms obfuscation toolkit?
February 20, 2018 - 1:09 am UTC
SQL> set serverout on
SQL> declare
2 l_key raw(128) := utl_raw.cast_to_raw('mysecretkey');
3 l_source_data varchar2(100) := 'The data I want to encyrpt';
4 l_data raw(256) := utl_raw.cast_to_raw(l_source_data);
5 l_encrypted raw(256);
6 l_decrypted raw(256);
7
8 begin
9 l_encrypted := dbms_crypto.encrypt -- algorithm
10 ( src => l_data,
11 typ => dbms_crypto.des_cbc_pkcs5,
12 key => l_key );
13
14 dbms_output.put_line(l_encrypted);
15
16 l_decrypted := dbms_crypto.decrypt
17 ( src => l_encrypted,
18 typ => dbms_crypto.des_cbc_pkcs5,
19 key => l_key );
20
21 dbms_output.put_line(utl_raw.cast_to_varchar2(l_decrypted));
22 end;
23 /
D8E36D7ECB61C8EFF223636DF7ACE65F6F4393A5E0CDD7383BE2FDDC09ACF08A
The data I want to encyrpt
PL/SQL procedure successfully completed.
More pls
Learner, February 20, 2018 - 5:18 am UTC
So you don't have anymore to "make sure everything is a multiple of 8... "?
Also is it fitted to use this package in production environment since the cols types have to be changed? Say I have a number (2) colunm and char(10) another one. The output must be a big raw even converted to varchar2 remain within another type of data and another length! So what is your view on that? So if I have to encrypt data I have to alter the table and this defeat the conceptual model.