Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jonathan.

Asked: February 26, 2002 - 2:56 pm UTC

Last updated: May 15, 2012 - 1:58 pm UTC

Version: 6.0.8

Viewed 10K+ times! This question is

You Asked

Hi, Tom.

I made a form to encrypt and decrypt text string (more than 4000 Chr long).

Basically, I have to read a encrypted text file and decrypt from that file, and also have read simple text file and encrypt the file before sending it to client.

I can encrypt the simple text and save it fine, but If I read the saved encrypt file to decrypt, it contained some broken decrypt string.

I suspect when my from write to emcrypted ascii text file, it write ASC(15) to ASC(29) to same ascii text(black square box).

I don't know if it is true or not.

Please help me.

Jonathan

Here is my encrypt and decrypt procedure.

PROCEDURE DO_DECRYPT IS
V_ENCRYPT LONG default NULL ;
V_DECRYPT LONG default NULL ;
BEGIN
V_ENCRYPT := :DECRYPT_FILE.TXT_ENC;

dbms_obfuscation_toolkit.DESDecrypt
( input_string => V_ENCRYPT,
key_string => :DECRYPT_FILE.TXT_KEY,
decrypted_string=> V_DECRYPT );

:DECRYPT_FILE.TXT_DEC := V_DECRYPT;
END;

PROCEDURE DO_ENCRYPT IS
V_ENCRYPT LONG default NULL ;
V_DECRYPT LONG default NULL ;
V_INPUT LONG default NULL ;
BEGIN
V_INPUT := :DECRYPT_FILE.TXT_DEC;
V_INPUT := rpad( V_INPUT, (trunc(length(V_INPUT)/8)+1)*8, chr(0));

dbms_obfuscation_toolkit.DESEncrypt
( input_string => V_INPUT,
key_string => :DECRYPT_FILE.TXT_KEY,
encrypted_string=> V_ENCRYPT );

:DECRYPT_FILE.TXT_ENC := V_ENCRYPT;
END;

And, procedure to save encrypt ascii file

Procedure Save_enc_file

V_Filename VARCHAR2(255);
V_ONE_CHR VARCHAR2(1);
V_FILETYPE Text_io.file_type;
i NUMBER;
BEGIN
V_Filename := :DECRYPT_FILE.TXT_DIRECTORY || :DECRYPT_FILE.TXT_FILENAME;
V_FILETYPE := TEXT_IO.FOPEN(V_Filename, 'w' );

FOR i IN 1..LENGTH(:DECRYPT_FILE.TXT_ENC)
LOOP
V_ONE_CHR := SUBSTR(:DECRYPT_FILE.TXT_ENC, i, 1);
TEXT_IO.PUT (V_FILETYPE, V_ONE_CHR);
END LOOP;

TEXT_IO.FCLOSE(V_FILETYPE);
END;

and Tom said...

encryption will create binary data.

Text_IO will write ascii data only.

You'll want to write only ascii data. I suggest your do_encrypt is coded like this:

PROCEDURE DO_ENCRYPT IS
V_ENCRYPT LONG default NULL ;
V_DECRYPT LONG default NULL ;
V_INPUT LONG default NULL ;
BEGIN
V_INPUT := :DECRYPT_FILE.TXT_DEC;
V_INPUT := rpad( V_INPUT, (trunc(length(V_INPUT)/8)+1)*8, chr(0));

dbms_obfuscation_toolkit.DESEncrypt
( input_string => V_INPUT,
key_string => :DECRYPT_FILE.TXT_KEY,
encrypted_string=> V_ENCRYPT );

:DECRYPT_FILE.TXT_ENC := utl_raw.cast_to_raw(V_ENCRYPT);
END;

That utl raw call would turn the varchar2 into a RAW. when you assign the RAW to the varchar2 -- it would do it in HEX. (your VARCHAR2 out parameter would need to be 2 times as long as the input string was!!!)

Then, you can save the hex string safely. Later, to decrypt, you would:



PROCEDURE DO_DECRYPT IS
V_ENCRYPT LONG default NULL ;
V_DECRYPT LONG default NULL ;
BEGIN
V_ENCRYPT := utl_raw.cast_to_varchar2( hextoraw(:DECRYPT_FILE.TXT_ENC) );

dbms_obfuscation_toolkit.DESDecrypt
( input_string => V_ENCRYPT,
key_string => :DECRYPT_FILE.TXT_KEY,
decrypted_string=> V_DECRYPT );

:DECRYPT_FILE.TXT_DEC := V_DECRYPT;
END;



Just beware of the doubling in size -- it is unavoidable if you want to use TEXT_IO however.

Rating

  (112 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Most interesting and useful, as usual !

Andre Whittick Nasser, February 26, 2002 - 4:08 pm UTC


It works!!!!!!!!!!!!!!

Jonathan, February 27, 2002 - 1:51 pm UTC


dbms_obfuscation_toolkit.DESDecrypt

mins, April 01, 2002 - 2:04 pm UTC

Is there a way to avoid the doubling in size.

I am encrypting the data and the encrypted data can't fit in the table because of the doubling of size.

(what I do is convert VARCHAR2(30) and the encrypted output
is more than 50 characters)
Is there any other encryption other than dbms_obfuscation_toolkit provided by Oracle.

Thanks


Tom Kyte
April 01, 2002 - 8:21 pm UTC

YOU don't need to double it.

THEY did. They wanted to write it to an ascii file using TEXT_IO, they were limited therefore by the API (text_io) they wanted to use. They needed ascii strings, hex code, not binary bytes.

You can deal with binary bytes. The data is not in general "doubled". Only when you treat a binary as a hex string would it be doubled.


My advice:

o always always always stored encrypted data in a raw
o plan on multiples of 8 bytes.

So, for your varchar2(30), I would suggest you set up a field that is raw(32). Use UTL_RAW.CAST_TO_RAW to turn the varchar2(30) into a RAW -- encrypt the raw (32 bytes long) and put that in the database table. When you fetch it out -- decrypt it and use UTL_RAW.CAST_TO_VARCHAR2 to put it back "right" again.

Encrypting varchar2 and storing..

Mins, April 02, 2002 - 2:07 pm UTC

Tom,
Thanks for the reply , but I still have a problem.
I am encrypting VARCHAR2(30) data from the tables and storing the encrypted values in the same table.

The output raw and the size is the double or more of the orignal input.

How could I encrypt the varchar2 and store it in the same field of the same size.

Thanks
the e

Tom Kyte
April 02, 2002 - 3:08 pm UTC

Well the problem is two fold:

o encryption works 8 bytes at a time, that 30 character field will be 32 bytes after you are done. No avoiding that. That field will grow.

o the output size is NOT double, it is 2 bytes longer at most (the contents of that field will be 0,8,16, 24 or 32 bytes)

o there is a chance of screwing the data up if you store binary data in the varchar2. If you ALWAYS insert clear text and have a trigger encrypt the data (binary data stays in the database) and you never fetch the RAW data out -- rather you apply a decrypt function in the database and retrieve clear text -- you'll be OK.

This is how I view this whole discussion....

You are adding new functionality to an existing application.

Most times, in any case, this cannot be done 100% transparently.

Adding encryption -- ANYONES encryption -- is one of those times.

You will have to change.

Am I doing something wrong

Srimal, May 08, 2002 - 1:49 pm UTC

clts@test.WORLD> drop table t
2 /

Table dropped.

clts@test.WORLD> create table t (data varchar2(9), data_enc raw(16));

Table created.

I use the crypt function that you published here

function crypt( p_str in varchar2 ) return varchar2
as
l_data varchar2(255);
begin
l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
dbms_obfuscation_toolkit.DESEncrypt
( input_string => l_data,
key_string => 'MagicKey',
encrypted_string=> l_data );
return l_data;
end;


clts@test.WORLD> insert into t (data, data_enc) values ('012345678',crypt('012345678'));
insert into t (data, data_enc) values ('012345678',crypt('012345678'))
*
ERROR at line 1:
ORA-28234: key length too short
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 33
ORA-06512: at "CLTS.CRYPT", line 6
ORA-06512: at line 1


What am I doing wrong?

Tom Kyte
May 08, 2002 - 1:58 pm UTC

I don't think the code you show is exactly the code you are using?

select text from user_source where name = 'CRYPT' order by line;

see that it is the right code.  You will have to modify your routine slightly:

ops$tkyte@ORA8I.WORLD> create or replace function crypt( p_str in varchar2 ) return varchar2
  2  as
  3      l_data  varchar2(255);
  4  begin
  5      l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
  6      dbms_obfuscation_toolkit.DESEncrypt
  7          ( input_string => l_data,
  8           key_string   => 'MagicKey',
  9           encrypted_string=> l_data );
 10      return <b>UTL_RAW.CAST_TO_RAW( l_data );</b>
 11  end;
 12  /

Function created.

<b>so that it returns in fact a RAW type..</b>

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select crypt( '012345678' ) from dual;

CRYPT('012345678')
-----------------------------------------------------------------------------------------------------------------------------------
07CF8213EDC46C277C720164D915B70C

<b>that is the raw in "hex"</b>

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> drop table t;

Table dropped.

ops$tkyte@ORA8I.WORLD> create table t ( data varchar2(9), data_enc raw(16) );

Table created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> insert into t values ( '012345678',crypt('012345678'));

1 row created.


<b>this just shows that it works...</b> 

Excellent! You are too good

Srimal, May 08, 2002 - 2:23 pm UTC

Hello Mr Tom,

I need to get a smack on my head!!!

You are right.... I was trying some options for the keystring and when changing back left the "c" in "MagicKey"..

I learnt a new lessan today, to see the user source before crying fowl.

Thanks a lot and sorry for wasting your time!

Is there any equivalent package exists before 8i?

Nirmal kumar, July 10, 2002 - 2:32 am UTC

Tom,

I need to apply encryption on data of a column in a table. The DB box version is 7.3.1.

Any direct oracle tools are there to implement this like DBMS_OBFUSCATION_toolkit package?...

Thanks.

Tom Kyte
July 10, 2002 - 7:35 am UTC

Nope, you need to get to 8i before you have that functionality.

Prior to that, the functionality just plain and simply "did not exist" in the database.

why it not work

ashraf, July 10, 2002 - 8:39 am UTC


QL> PROCEDURE DO_ENCRYPT IS
2 V_ENCRYPT LONG default NULL ;
3 V_DECRYPT LONG default NULL ;
4 V_INPUT LONG default NULL ;
5 BEGIN
6 V_INPUT := :DECRYPT_FILE.TXT_DEC;
7 V_INPUT := rpad( V_INPUT, (trunc(length(V_INPUT)/8)+1)*8, chr(0));
8
9 dbms_obfuscation_toolkit.DESEncrypt
10 ( input_string => V_INPUT,
11 key_string => :DECRYPT_FILE.TXT_KEY,
12 encrypted_string=> V_ENCRYPT );
13
14 :DECRYPT_FILE.TXT_ENC := utl_raw.cast_to_raw(V_ENCRYPT);
15 END;
16 /
P2-0552: Bind variable "DECRYPT_FILE" not declared.
QL>

Tom Kyte
July 10, 2002 - 12:19 pm UTC

line 6, you are trying to use a bind variable in DDL, that is not possible.

I don't know what you are attempting to do with :DECRYPT_FILE.TXT_DEC but it isn't even remote "valid syntax"

password encryption

mo, September 04, 2002 - 6:51 pm UTC

Tom:

I am using the crypt and decrypt functions for encrypting application users that are stored in a table.

However, do you say that this is not really safe method if someone knew what the key "word" was.

2. If this is the case, then what do you use these for?

3. Do you recommend using Hash methods for password encryption.

4. You say to use 8 bytes. is that for the key?

Thank you,

Tom Kyte
September 04, 2002 - 7:28 pm UTC

Your encrypted data is only as safe as the keys -- obviously.


2) don't understand what you mean?

3) yes, yes and double yes.
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:95412348059 <code>

4) key and data -- the toolkit needs data in 8 byte multiples.


encryption

mo, September 04, 2002 - 9:48 pm UTC

AS a followup:

1. You say that the data is as safe as the key?

Since they key can be any combination of letters and numbers how in the world can someone guess it? ALso are you assuming that someone goes in and know how to use the decrypt package to try to figure out what passwords are?

2. You say use hashing for appliction or oracle passwords (I assume). Then I am wondering what is the encryption packages used for then?

3. How can hashing be safer than encryption?

4. to implement hashing in your given code below:

a. When admin creates a userid/password you use "digest" to read a username and a password and store the hashed result as a password in the database?

b. Then when someone wants to log in you take whatever he keyed in, hash it and compare that to the hashed value in the database?

c. how is that safer than encrypted value.


ops$tkyte@8i> declare
2 function digest( p_username in varchar2, p_password in varchar2 )
return varchar2
3 is
4 begin
5 return ltrim( to_char( dbms_utility.get_hash_value(
upper(p_username)||'/'||upper(p_password),
6 1000000000,
power(2,30) ),
7 rpad( 'X',29,'X')||'X' ) );
8 end digest;
9 begin
10 for x in ( select username from all_users where rownum < 20 )
11 loop
12 dbms_output.put_line( 'User: ' || rpad( x.username , 30 ) ||
13 ' digest: ' || digest( x.username, 'TIGER' )
);
14 end loop;
15 end;
16 /



Tom Kyte
September 05, 2002 - 7:27 am UTC

1) many people use psuedo random number generators to generate keys. In doing so, they generate keys in a predicable fashion (all psuedo random number generators have a period -- show me a couple of keys in sequence and I can predict the keys that are coming)

Also, many people use algorithms to generate keys. For example really early Netscape SSL implementations used an algorithm to generate the session key used to encrypt the conversation. Unfortunately, once anyone knew the algorithm used to generate the key -- they too could generate the key and listen in on the conversation. Here encryption was useless as it was trivial to reverse!

Your password to your systems is as you say "any combination of letters and numbers" so how is it that many HACKS start with password guessing???

2) when you need to see the value back. For passwords, all you need to do is verify that the username/password pair a user presents to you is correct. So, you hash the password they gave you and compare to the hash in the database. You never need to REVERSE the hashed password in the database (that is one of the beauties of using the HASH, even if your user table with usernames/hashed passwords is stolen-- it provides no useful information to anyone in any case!)

But suppose that was a credit card number in there -- username -> credit card number. Well, if you are an online store, you obviously need to get the credit card number out of there so you can get paid right? So, you cannot hash it, you encrypt it. That way, if your user table with credit card numbers is stolen, it is "safe" unless the robber has your key(s) (which goes to point #1 again, the data is only as safe as the keys)

3) See #2, it not easier in that you don't have the HASSLE of generating a secure key, maintaining the key, presenting the key. You can also sleep easier at night cause you don't have to worry about if your key has been comprimised.

4) read #3 in your previous comment and follow the link.

For mo...

Connor, September 05, 2002 - 4:07 am UTC

hashing is "more" secure that encryption because decryption is not possible. Thus a brute force attack is basically the only way to go (assuming you have a good hashing function).

The issue with the key is not possible guessing it, but them finding some way to see it. For example, when you call dbms_obfuscation_..., you need to pass the key. If you pass it as a literal, then it might be caught by snooping v$sql. If its a bind variable, then it might be caught in a 10046 level 4 trace. If its in a table, then you've got to be very sure that this is well protected. If its in the PLSQL source code, then you have got to make sure that its wrapped and that even the wrapped code does not give any clues. If its being run across a network, then the key cannot be passed in clear text....

The moment you embrace database encryption, you're up a LOT of work

hth
connor

Tom Kyte
September 05, 2002 - 7:39 am UTC

Or if you don't embrace the "work" concept -- all you've done is built a false sense of security.

It is like the people that say "Oh, I've got it so my application is the only thing that can access the data. We did that by password protecting the role, pretty neat eh?" Even when I describe how it would take less then a couple of seconds to break their scheme in most every case -- they say "oh, our users are not that smart, not to worry". Total and utter false sense of security by obscurity...

...our users aren't that smart...

Frank, September 05, 2002 - 12:52 pm UTC

Indeed. Which always makes me wonder why the h#ll they're going through all the work to encrypt it in the first place :-))

Usefulness of hashed passwords

Gary Myers, September 05, 2002 - 7:09 pm UTC

"even if your user table with usernames/hashed
passwords is stolen-- it provides no useful information to anyone in any case!)"

I'd have to disagree with that (though I could be persuaded to agree if you changed 'it provides' to 'it should provide').

If I get to see the hashed passwords in DBA_USERS, I can run a bit of PL/SQL on my own database on my home PC that does an 'ALTER USER SYSTEM IDENTIFIED BY' for a few thousand possible passwords until it gets one with a hashed password that matches the one for your system account. If the system password is a normal word that can be found in a hacker's dictionary, it's vulnerable. [Yes, system passwords shouldn't be that simple, but that's why I think the phrase would be better with 'should'.]

Trying to run that same quantity of passwords against the real database is more likely to get noticed.

Accounts with access to hashed passwords should be treated as a security risk. If it's your own hashing mechanism, then the table should not be accessible to the users except via a function, with username and password parameters, returning true or false and locking out after a certain number of failures. The passwords should also be a minimum length, and matched against an exclusion list of 'easily guessable' entries.

I've worked on an application that had an authorisation feature using a hashed 4 digit pin. It's amazing how quickly PL/SQL can go through the 10,000 combinations to find a success (and how many were either 9999 or 1111).

Tom Kyte
September 05, 2002 - 11:18 pm UTC

Ok, beyond the typical "brute force, I'll try every possible password and see what happens -- given that I have access to your hash function and magic "salt" if you use one", you are right.

Add a little salt, hide the salt, and there you go.

encryption and hashing

mo, September 07, 2002 - 2:43 pm UTC

Tom:

1. Can you verify for me what is the difference between binay, raw and hex data.

2. When you encrypt date it created a binary data (0's and 1's) while hashing creates hex code (0..9 A..F).
Correct?

3. You say encryption will work on 8 bytes at a time . Does this mean if I have a credit card or password field that is 10 characters in length I have to make it varchar2(16) before applying the encrypt function.

4. When you say encryption is as safe as the key do you mean that all it takes is to figure out the word "Magic Key"? Can this also be replaced by any word of any length?

5. Since encryption creates a binary and you store this in a varchar2 column would it work or you have to change the column type of encrypted data before storing it?

6. If I encrypt aplication passwords (which you do not recommend), then If a user logs in why do I have to decrypt the password from the database, why not encrypt the entered password again and compare it to the encrypted password in the database? Would not this be more like hashing?

7. I this correct:
If you want to read the data from the database like credit card numbers use encryption (actually only way of security). If you do not need read data back like user passwords use hashing instead?

8. To hash data you use dbms_utility.get_hash_value and you define a string there which works like a key in the encryption scheme. Correct?

9. Can't a user break into the system by hashing his random password exactly like trying to guess a key in a an encryption data?



Tom Kyte
September 07, 2002 - 6:58 pm UTC

1) RAW is a datatype that can hold upto 2000 bytes of BINARY data in a database table (or 32k of it in a PLSQL routine).

HEX is text data (not binary, just normal ascii, printable, 'safe' characters) that is a textual representation of binary data.

2) wrong. Encrypting the data results in binary data. Hashing the data results in a number (which I used a fixed length HEX representation of

3) yes.

4) the key length is dictated by the algorithm. single des = 8byte key, double des = 16byte and des3 (triple des) wants a 24byte key.

Yes, the key can be ANYTHING in the world you want.

5) you would want to use a RAW datatype to store it, yes.

6) it is what I call "6 one way, 1/2 dozen the other". No, it is not like hashing AT ALL since you can decrypt the password and the reason you hash is so YOU CANNOT. That is the basic point -- hash = no decrypt = less chance of compromise

7) yes. If you do not need to decrypt the data, don't encrypt it.

8) no, not even close. re-read that thread again (and read up on hashing, it is a basic computer science "thing"). The string is what we are hashing. It is not the key, it is the value

9) HUH? doesn't compute.

Reader

A reader, September 09, 2002 - 9:13 pm UTC

Why Oracle does not HASH the password for Database Links
created with
create database linke <link> connect to <user>
identified by <password> using <connect string>

Tom Kyte
September 10, 2002 - 7:13 am UTC

because there we need to present the password from DB1 to DB2. In order to log in, you need to present a username+password that is hashed to see if it is you. You cannot just present the username and hash.

Reader

A reader, September 11, 2002 - 12:34 pm UTC

Tom,

There are two parameters
DBLINK_ENCRYPT_LOGIN and
ORA_ENCRYPT_LOGIN

If we use DBLINK_ENCRYPT_LOGIN = true, does Oracle
send the *key* and the encrypted password via network
from DB1 to DB2 and enable DB2 decrypt the password using
the key, and authenticate the password.

Thanks

Tom Kyte
September 11, 2002 - 3:26 pm UTC

Just the encrypted password is sent.

the password is encrypted with false as well -- only sent in the clear if the first attempt fails.

Reader

A reader, September 16, 2002 - 2:18 pm UTC

Tom,

Given that, a database link is set up
successfully between local DB1 and
remote DB2, and that the application
has been tested successfully using the db_link
before going into production, what is
the chances of DB_LINK to fail in the
first attempt in a PRODUCTION environment.

If the password is changed at the remote
DB2, the link will fail. In this case it
is not a concern.

Is there any other scenarios for the first
attempt to fail and subsequent attempt
to succeed

Thanks



Encrytion and Decrytion

Dave Fowler, November 08, 2002 - 9:59 am UTC

Good example of use of dbms_obfucation toolkit. and encryption/decryption technology.

How do you do this?

Eric Givler, December 04, 2002 - 11:20 am UTC

You said:

"It is like the people that say "Oh, I've got it so my application is the only thing that can access the data. We did that by password protecting the role, pretty neat eh?" Even when I describe how it would take less then a couple of seconds to break their scheme in most every case -- they say "oh, our users are not that smart, not to worry". Total and utter false sense of security by obscurity... "


HOW CAN YOU DO THIS IN A FEW SECONDS? THE ROLE PASSWORD IS HASHED IF ONE APPLIES. CAN YOU ELABORATE?

Two cases I thought this would be an issue are:
a) if someone with DBA rights (or GRANT ANY ROLE) creates a newuser and then grants the "magic role" to the newly created account. That role now becomes a DEFAULT ROLE, and the user can then begin snooping.

b) The user who logged in has ALTER ROLE, and removes the password from the protected role.

How can a non-privileged, non-DBA users break this scheme in a couple of seconds?

Tom Kyte
December 04, 2002 - 1:15 pm UTC

The application will be sending the password over the wire to the database. I'll turn in sqlnet tracing and I'll see your password.

You would have to be using ASO (advanced security) and network encryption in order to stop me.

I would just run your application (as I'm supposed to), then I'll have your password. You cannot stop me from turning on sqlnet tracing on my machine.

How does that apply?

Eric Givler, December 07, 2002 - 8:28 pm UTC

I realize this is probably not the way to continue asking questions (based on instructions to the side), but how would a user turn on SQL-tracing?

The application is not sitting on their desktop. The application is on the application server, or possibly on a citrix server.

Now they still have to find that machine and then login with a valid oracle account, then view the trace files after modifying sqlnet.ora. How did they get privileges to get to all of these files and folders?

Am I missing something obvious?

Tom Kyte
December 08, 2002 - 9:44 am UTC

It is not sql_trace.

It applies to client server (which is what the vast preponderance of people using this approach have done -- client server).

It is net tracing, set in the sqlnet.ora
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/params.htm#437951 <code>
it can dump everything that goes back and forth.



Use of char(0)

Pooja, December 24, 2002 - 1:10 pm UTC

I am noticing that you some times use chr(0) in your code (including this one). After some research I found the
following.
1) This is equivalent to ascii U000.
2) select 'abc' || chr(0) || 'de' from dual
seems to return just abc.
String after chr(0) does n't seem to be returned.
3) substr('abc' || chr(0) || 'de', 4) returns nothing.
But, substr('abc' || chr(0) || 'de', 5) returns de.


Can you please tell me

a) What is this character called as?
b) When do we usually use this?

Appreciate your help.

Thanks





Tom Kyte
December 24, 2002 - 1:38 pm UTC

It is generally call NUL in ascii speak.

In C -- it is the end of string marker.. that is why select 'abc' || chr(0) || 'de' from dual; does that -- sqlplus is using

printf( "%s", field );

and the chr(0) which is '\0' in C denotes end of string.

In this example, I'm just using it as a "safe" character to embedd in my string.



Differnce between 8.1.7 and 9.0.1.1.1

Jonathan Lee, January 29, 2003 - 4:59 pm UTC

Yes, it work on same version.
But, if I encrypt with 8.1.7 and try to decrypt in 9.0.1 , it doesn't decrypt correctly.

Any thought?

Tom Kyte
January 30, 2003 - 8:15 am UTC

there is no difference -- DES is DES is DES.  it is the same.

More then likely -- you are storing the encrypted data which is binary in a varchar2 field instead of raw?  and you are getting bit by a character set conversion?

check that out -- make sure the character sets are the SAME 
that the raw DATA is the same (use the DUMP() builtin to verify that)

Here is an example that shows this works just dandy:

ops$tkyte@ORA817DEV> create or replace function crypt( p_str in varchar2 ) return raw
  2  as
  3      l_data  varchar2(255);
  4  begin
  5      l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
  6          return dbms_obfuscation_toolkit.DESEncrypt
  7             ( input => utl_raw.cast_to_raw(l_data),
  8                           key => utl_raw.cast_to_raw('MagicKey') );
  9  end;
 10  /

Function created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function decrypt( p_str in raw ) return varchar2
  2  as
  3  begin
  4          return utl_raw.cast_to_varchar2(
  5                dbms_obfuscation_toolkit.DESdecrypt
  6                ( input => p_str,
  7                  key   => utl_raw.cast_to_raw('MagicKey') ) );
  8  end;
  9  /

Function created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> column data format a10
ops$tkyte@ORA817DEV> drop table t;

Table dropped.

ops$tkyte@ORA817DEV> create table t ( data varchar2(9), data_enc raw(16) );

Table created.

ops$tkyte@ORA817DEV> insert into t values ( '012345678',crypt('012345678'));

1 row created.

ops$tkyte@ORA817DEV> commit;

Commit complete.

ops$tkyte@ORA817DEV> select data,
  2             data_enc,
  3             decrypt(data_enc) data
  4    from t;

DATA       DATA_ENC                         DATA
---------- -------------------------------- ----------
012345678  07CF8213EDC46C277C720164D915B70C 012345678



$ exp userid=/ tables=t

Export: Release 8.1.7.4.0 - Production on Thu Jan 30 08:13:57 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          1 rows exported
Export terminated successfully without warnings.




$ imp userid=/ full=y

Import: Release 9.2.0.1.0 - Production on Thu Jan 30 08:14:21 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"          1 rows imported
Import terminated successfully without warnings.


ops$tkyte@ORA920> select data,
       data_enc,
       decrypt(data_enc) data
  from t;  2    3    4

DATA      DATA_ENC
--------- --------------------------------
DATA
-----------------------------------------------------------------------------------------------------------------------------------
012345678 07CF8213EDC46C277C720164D915B70C
012345678




<b>Now, if i stored the data in varchar2 and did a character set switcheroo during the exp/imp step -- this would *not* work since the data would *not* be the same</b>

make sure you have the same data!
 

Is there a way?

TH, April 22, 2003 - 10:03 am UTC

Dear Tom,

Is there a way to hide all the schema objects when we are supplying a software?


For example the public should not know how we defined our tables, procedures, functions etc. I heared we can encrypt procs. But wht about tables?

Tom Kyte
April 22, 2003 - 6:42 pm UTC

nope, tables are tables are tables -- it would defeat the purpose of being a database otherwise.

It is THEIR data remember. NOT yours.

Encrypt And Decrypt

Deval Shah, May 18, 2003 - 8:12 am UTC

Here i am sending you one code which i had copide from your answer.

I am using version 8.1.7

Here i have written two trigger on the table where i am storing my pass word
one is on Before insert and another on Before Update

This code is written in version 8.1.7

The table like this
Table := USER_DETAIL
USER_ID NUMBER(10),
PASSPHRSE VARHCAR2(50),
PASSPHRSE2 RAW(100)

and here two triggers and both the trigger is same

This is Before Update trigger
create or replace trigger TRIG_PASS_BEF_UPD
DECLARE
vTabPass VARCHAR2(100);
vEncrDncrPass raw(100);
BEGIN
vTabPass := rpad( :NEW.PASSPHRSE,(trunc(length(:NEW.PASSPHRSE)/8)+1)*8, chr(0));
:NEW.PASSPHRSE2 := dbms_obfuscation_toolkit.DESEncrypt
( input => utl_raw.cast_to_raw(vTabPass),
key => utl_raw.cast_to_raw('MagicKey') );
END;

after i apply update query to change the password

UPDATE USER_DETAIL
SET PASSPHRSE = 'deval';

which will update all the records in table.

i have also written function to get that password back from the table.

create or replace function get_password
(vEncyPassword raw) return VARCHAR2
as
begin
return utl_raw.cast_to_varchar2(
dbms_obfuscation_toolkit.DESdecrypt
( input => vEncyPassword ,
key => utl_raw.cast_to_raw('MagicKey') ) );
end;

On version 8.1.7 it is working very nice.

but after that i had taken export of this user and i had imported it on the another server which is having version 9.2.0

here i am checking my password by using get_password function but it is not giving me junk password so could you tell me where i am making mistake?

currently my live system is working on the client in USA and all the coding is done perfectly so i don't want to change the frontend code so i had taken two field to encrypt the password. if it is working well then i will do something to remove one field.

please help me.


Tom Kyte
May 18, 2003 - 10:32 am UTC

well, beyond being the LEAST SECURE implementation of security I've seen in a while.....

so, what is the datatype of the password column.

I'll guess. You left it as varchar2. It is a varchar2.

When you exported, the database had characterset X, the client had character set Y (nls_lang setting).

When you imported the client had characterset A and the database had character set B.


One of X, Y, A, B was different from the others. characterset conversion kicked in (as it is supposed to) and changed the varchar2 data.

All you'll need to do is

select dump( password, 16 ) from thistable;

in both databases and compare a couple, you'll see the bytes have changed.

only solution:

make sure X, Y, A, B all agree 100%

long term solution:

don't store passwords! that is the key to "insecurity" -- store passwords and I'll get them. especially when you make it so easy to do so.

or if you persist. store the passwords in a RAW field so the conversion does not take place.

Response to Deval Shah from India

Jonathan Lee, May 20, 2003 - 1:35 pm UTC

I found hard way why to same encrypt and decrypt function is not working on Oracle 9i (but works fine on 8i)

Change the KEY_STRING.

If you use Key_string as 12345678 on 8i, change to 43218765 on 9i.

It will work.


Encrypt & Decrypt

Bonnie Tang, September 16, 2003 - 3:31 am UTC

Hi! Tom,

does your encrypt and decrypt procedure work on any characterset ? UTF8 ? or ZHT16MSWIN950 (which is for traditional chinese)?. How about the length of raw we should use ?

Please help.

Bonnie

Tom Kyte
September 16, 2003 - 8:28 am UTC

for multi-byte data, you probably need to use lengthb (length in bytes, not characters) and use a character you know is a single byte in your character set to pad with.

Encryption & Decryption for multi byte data

Bonnie Tang, September 17, 2003 - 11:22 pm UTC

Tom,

Can utl_raw.cast_to_varchar2 give me back the original text in multi-bytes ? Why do I need to pad a single byte character in encrypting ? Suppose your chr(0) is single byte, then encrypt/descrypt procedures should look like the following. Right ?


create or replace function crypt( p_str in varchar2 )
return raw
as
l_data varchar2(255);
begin
l_data := rpad( p_str, (trunc(lengthb(p_str)/8)+1)*8, chr(0) );
return dbms_obfuscation_toolkit.DESEncrypt
( input => utl_raw.cast_to_raw(l_data),
key => utl_raw.cast_to_raw('MagicKey') );
end;
/



create or replace function decrypt( p_str in raw ) return
varchar2
as
begin
return utl_raw.cast_to_varchar2(
dbms_obfuscation_toolkit.DESdecrypt
( input => p_str,
key => utl_raw.cast_to_raw('MagicKey') ) );
end;
/


Many thanks.

Bonnie

infinitive loop

Hui Li, September 26, 2003 - 1:56 pm UTC

We found that in SOME Oracle database enviroments, the following line gives you infinitive loop since the "length" function does not count the "char(0)". Do you know why?

rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) )

Tom Kyte
September 26, 2003 - 2:56 pm UTC

an infinite loop? how so?

and why would p_str have chr(0) in it ? i choose chr(0) cause it is a character one would not see in a string in a single byte characterset.

does not return

Hui Li, October 08, 2003 - 1:22 pm UTC

Tom,
We found that in some 9i Oracle DBs, the following line of code does not finish

rpad(p_input_string, (trunc(length(p_input_string)/8)+1)*8, chr(0) )

However, it works if we changed it to the following

l_length := (trunc(length(p_input_string)/8)+1)*8;
l_input_string := p_input_string;
WHILE length(l_input_string) < l_length
LOOP
l_input_string := l_input_string || chr(0);
END LOOP;
I am thinking the "length" function does not work with the "chr(0)" for some 9i Oracle DBs

Tom Kyte
October 09, 2003 - 3:44 pm UTC

"does not finish".

if you have a database where that "does not finish" -- (do you mean hang) -- i would STRONGLY encourage you to file a bug with support, you have a really simple testcase -- they would collect the configuration information and get it fixed.

sql net tracing

A reader, October 18, 2003 - 3:25 pm UTC

You mention sql net tracing above - Have you ever used this
tool to troubleshoot or debug an issue - could you kindly give an example?

Thank you!

Tom Kyte
October 19, 2003 - 6:15 pm UTC

No many times. I use it to see whats going on in a connection problem (packet dumps, see whats going back and forth, in the early days of NAT (network address transalation) i remember seeing the actual IP address (not hostname) go back -- leading me to discover why we couldn't connect).


Or to satisfy curiosity -- or demonstate that the data is cleartext over the net unless you enable encryption.

Eliminate Special Characters

Mohan, October 21, 2003 - 5:45 am UTC

Is there a way to eliminate (#, ?) in the encrypted string? We need to pass this encrypted string via URL and is giving a problem.

Thanks


Tom Kyte
October 21, 2003 - 7:51 am UTC

well, encrypted data should be in RAW fields first of all

I would use utl_raw.cast_to_raw to convert the string into a raw.

then encrypt the raw data.

then assign the raw data back to a varchar2 that is 2x the size of the raw.

You'll have a nice hex string consisting of 0..9A..F only

to decrypt, assign varchar2 back to raw (converts)

decrypt raw

utl_raw.cast_to_varchar2 the decrypted data

DECRYPT

Junior, November 13, 2003 - 10:50 am UTC

Tom:

I tried your example, but have problem for the decrypt data size. You have explained it, but I still not understand, so... How can I get rid of those extra invisible characters.... Thanks.

create table t ( data varchar2(9), data_enc raw(16) );

 1  create or replace function crypt( p_str in varchar2 )
 2  return raw
 3    as
 4        l_data  varchar2(255);
 5    begin
 6        l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
 7            return dbms_obfuscation_toolkit.DESEncrypt
 8               ( input => utl_raw.cast_to_raw(l_data),
 9                            key => utl_raw.cast_to_raw('MagicKey') );
10* end;

 1  create or replace function decrypt( p_str in raw ) return
 2  varchar2
 3  as
 4  begin
 5        return utl_raw.cast_to_varchar2(
 6              dbms_obfuscation_toolkit.DESdecrypt
 7              ( input => p_str,
 8                key   => utl_raw.cast_to_raw('MagicKey') ) );
 9* end;


  1  select data, LENGTH(DATA),
  2         data_enc, LENGTH(DATA_ENC),
  3         decrypt(data_enc), LENGTH(decrypt(data_enc))
  4* from t
SQL> /

DATA      LENGTH(DATA) DATA_ENC                         LENGTH(DATA_ENC)
--------- ------------ -------------------------------- ----------------
DECRYPT(DATA_ENC)
-------------------------------------------------------------------------
LENGTH(DECRYPT(DATA_ENC))
-------------------------
012345678            9 07CF8213EDC46C277C720164D915B70C               32
012345678
                       16 ( why this is 16? Are there extra invisible characters?)


Thank you again. 

Points raised by Connor McDonald

SA, November 20, 2003 - 4:05 pm UTC

Hi Tom:

We are considering use of Encryption in our project and this thread is so helpful. Could you please give your openion on how to tackle some of these issues raised by Connor in the same thread:

<QUOTE>
For example, when you call dbms_obfuscation_..., you need to pass the key. If you pass it as a literal, then it might be caught by snooping v$sql. If its a bind variable, then it might be caught in a 10046 level 4 trace. If
its in a table, then you've got to be very sure that this is well protected. If its in the PLSQL source code, then you have got to make sure that its wrapped and that even the wrapped code does not give any clues. If its being run across a network, then the key cannot be passed in clear text....
<END QUOTE>

For example, what is the solution to DBA seeing the key from a 10046 trace? How do we avoid it? Also, how do we avoid the key to appear in an sql_trace?

Is there any examples of a real-life implementation of Encryption within the database anywhere in OTN? I could not find any. Will be greatful if you can help.

Thanks a lot.
SA

Tom Kyte
November 21, 2003 - 4:35 pm UTC

to tackle these issues - simple

BE AWARE OF THEM.

there is no avoiding them. they are things you must be aware of and take whatever precautions YOU feel is sufficient to prevent them from falling into the wrong hands.

for example, to do a 10046 trace -- you need to have "alter session". don't grant that.

if it is in a table, secure the table.

if it is in code, wrap it and obscure it in the code.


the DBA must be trusted, regardless. Any semi intelligent DBA would be able to figure anything you do out in the end. Vett your DBA's (clear them).

Input on keys is very helpful

Eric Givler, November 21, 2003 - 8:26 pm UTC

In regards to some of the facts above, you noted originally about storing encrypted data in varchar2 is ok, under certain conditions, then later talked about chr(0)'s, and it did not appear to cause a problem. I have to allow "special" administrative users to query our data by a number that is encrypted. What I have is the encoded varchar2 column and an index on it. I pass their query prm to a function and do return what this result should be encoded. When I attempt the lookup, I get a "no data found". The raw trace appears to stop when it sees a null byte in the bind.

However, I've created dummy table, stored a string with an embedded chr(0), like '0000001'||chr(0)||'A', and then later queried by this literal without a problem.

I tried the same "technique" against this problem by using an open refcur for 'select '... embed literal with chr(0) in the string. The trace shows an open statement with a bind that is truncated at the chr(0), no rows returned. It also shows a "good statement" with the original encoded data, which is BYTE-by-BYTE equivalent to the encoded data, but appears to not return a row.

What gives?

Should I simply work it into my plans to undo this column as varchar2, and make it a raw, and slightly change my existing interaction with it in my encrypt/decrypt functions? Or possibly even just widening the column and replacing each existing value with a utl_raw.cast_to_raw, then recast back to varchar2 to save a "hex string", which I simply "like to look at". I can understand reading hex digits, but hate writing funky little routines to spit out the bytes, their hex digits, and the character just to see why this does not work.

Any thoughts would be helpful, and I'm hoping this might be useful to others.

Tom Kyte
November 21, 2003 - 10:45 pm UTC

what gives? need an example, depends on the language and method of interface. In C and other languages -- chr(0) means "END OF STRING" -- "STOP". So, i would guess your language you program in is one of them that says "STOP" and when you bind -- it stops.


i would use RAW for encrypted data (based on experiences). it is the safest all around.

more input about chr(0) issue

eric givler, November 22, 2003 - 7:16 pm UTC

The language is SQL, and or Pl/sql.

For example, I query the database like this:

variable x varchar2(32)

begin
-- this is the "known" problematic record
select cc_encoded into :x from receipts
where receipt_num = 12041231;
end;
/

print x

(I see a string of chars here.)

I write:

select receipt_num
from receipts
where cc_encoded = :x
/

no rows returned

I examine the tracefile for my select and it shows 11 bytes where sent.

So, I type:

variable rc refcursor

begin
open :rc for 'select *
from receipts where cc_encoded=''' ||
substr(:x,1,11)||chr(0)||substr(:x,13)||
'''';
end;
/

print rc

no rows returned

The trace now shows "two statements". (I'm using 8i R3 on Windows 2000). The first statement shows the pl/sql block and open for "cursor". The second shows a statement that looks like this:

select * from receipts
where cc_encoded = '<bytes here>'

I open the trace file using "hex mode" (hex bytes on left, and normal text on right). I scroll to the statement and it shows that the '<bytes here>' contains the exact string of bytes that I've printed in sqlplus, as I listed each byte in the string using a substr() loop and displayed it in hex.

I still get no rows when I print rc though.

NOTE: I can't get to my work machine right now to give you the raw trace files. I'm tempted to open a TAR.

Tom Kyte
November 22, 2003 - 10:05 pm UTC

give me the entire test case (insert et. al.)


plus is probably binding a regular "ascii z string" (null terminated)


having chr(0) in a "string" is a truly (110%) bad idea

it's not the null byte

eric, November 23, 2003 - 12:47 am UTC

I forgot to ever dump this out, but here's the difference, and I can't explain it though:

PARKS_DBA@test [NRBITORAS03] > select dump(:x) from dual;

DUMP(:X)
-----------------------------------------------------------------------------------------------------------
Typ=1 Len=24: 179,162,44,255,228,81,251,191,205,191,94,0,12,188,14,230,60,86,127,183,177,94,191,33

1 row selected.

PARKS_DBA@test [NRBITORAS03] > select dump(cc_encoded)
2 from transaction_groups@prrsdb where eid=1288792
3 /


DUMP(CC_ENCODED)
-----------------------------------------------------------------------------------------------------------
Typ=1 Len=24: 179,162,44,255,228,81,251,142,205,151,94,0,12,188,14,230,60,86,127,183,177,94,137,33

1 row selected.


The difference being the next to the last byte. Why does selecting it into the Varchar2 "change it"? or more simply:

PARKS_DBA@test [NRBITORAS03] > ed
Wrote file afiedt.buf

1 begin
2 :y := chr(179) ||
3 chr(162) ||
4 chr(44) ||
5 chr(255) ||
6 chr(228) ||
7 chr(81)||chr(251)||chr(142)||chr(205)||chr(151)||
8 chr(94)||chr(0)||chr(12)||chr(188)||chr(14)||chr(230)||
9 chr(60)||chr(86)||chr(127)||chr(183)||chr(177)||
10 chr(94)||chr(137)||chr(33);
11* end;

PARKS_DBA@test [NRBITORAS03] > select dump(:y) from dual;


DUMP(:Y)
------------------------------------------------------------------------------------------------------
Typ=1 Len=24: 179,162,44,255,228,81,251,191,205,191,94,0,12,188,14,230,60,86,127,183,177,94,191,33

1 row selected.

why does this change?

Tom Kyte
November 23, 2003 - 8:29 am UTC

character set translations no doubt.

You are storing binary information in a varchar2, you want to be using a RAW for that. You are using characters that "do not exist" in your characterset and we are converting them.

YES YOU ARE CORRECT AGAIN.

Eric Givler, November 24, 2003 - 10:39 am UTC

I did not think it was character set translation at first due to the TRACE showing the "shorter" bind. However, that trace was from FORMS. A subsequent trace was from Plus (9iR2), and when I had looked in the registry for the NLS_LANG, I looked in the wrong spot. The DB's NLS was: AMERICAN_AMERICA.WE8ISO8859P1 and the 9i registry NLS was: AMERICAN_AMERICA.WE8MSWIN1252

Changing the NLS so they match, does allow me to query in PLUS, but forms still fails due to the chr(0). Anyway, I'm moving all the code for the querying off the form to base it on a stored procedure or ref cursor. I'll handle the logic in there temporarily, then correct the column issue thereafter (more work involved).

Thanks for all the help!

infinit loop

Djordje, December 10, 2003 - 12:31 pm UTC

We have experienced the same thing: code hanging with chr(0) appended to the end.

Environment: Solaris 2.8, Oracle 9.2.0, Enterprise

Specifically this statement was hanging (all variables are VARCHARs):

l_data := rpad(l_in_data, (trunc(length(in_data)/8)+1)*8, chr(0) );

We changed the algorith to pad with the space. Later I saw on Metalink (Note 123091) a suggestion to pad the string using the PKCS5 method ('1', or '22', or '333', etc.).

Could you comment on that?

Thanks.

Tom Kyte
December 10, 2003 - 3:57 pm UTC



sounds like a "bug" -- but i don't use multi-byte character sets and I'm not sure what impact chr(0) might have in them.

Encrypted data in varchar2 datatype (trying to change)

Sami, January 19, 2004 - 5:57 pm UTC

Dear Tom,

We are storing users encrypted "MEMORABLE ANSWERS" in VARCHAR2 column (I know you hate this. Me too.. The design happened long time back.)

So for we did not face any issues because of VARCHAR2 datatype. But very soon we are going to upgrade from 8i to 9i also changing OS from Solaris to Linux.

I did setup a small test case(as you always do) what we will be facing when we do exp/imp.

Kindly explain how do we resolve this issue? 

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ENC_METHOD                                         VARCHAR2(50)
 DATA                                               VARCHAR2(50)
 VARCHAR2_DATA_ENC                                  VARCHAR2(100)
 RAW_DATA_ENC                                       RAW(100)

SQL>
The end result is...

[1]
a)export and import into the same database 
b) select data,vc_decrypt(varchar2_data_enc),decrypt(raw_data_enc) from t;

WORKING FINE.

[2]
a)export from DB1 (server1) and import into DB2(server2)
b) select data,vc_decrypt(varchar2_data_enc),decrypt(raw_data_enc) from t;

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 75
ORA-06512: at "fake_userid.VC_DECRYPT", line 8
ORA-06512: at line 1


Note:-
DB character set is UTF8 for both DB1 and DB2
NLS_LANG is set to AMERICAN_AMERICA.UTF8 on both server1 and server2 (while doing exp and imp)
OS: Sun Solaris




DEMO table to store encryped data
===================================

create table t (enc_method varchar2(50),data varchar2(50), varchar2_data_enc VARCHAR2(100),raw_data_enc RAW(100));

Function to support RAW datatype
=================================
CREATE OR REPLACE FUNCTION CRYPT( P_STR IN VARCHAR2 ) 
return raw
as
     l_data  varchar2(255);
begin
    l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
         return dbms_obfuscation_toolkit.DESEncrypt
            ( input => utl_raw.cast_to_raw(l_data),
                          key => utl_raw.cast_to_raw('ABCDEFGHIJKLMNOPQRST987123456ABC') );
END;
/

CREATE OR REPLACE FUNCTION DECRYPT( P_STR IN RAW ) RETURN 
varchar2
as
begin
        return utl_raw.cast_to_varchar2(
              dbms_obfuscation_toolkit.DESdecrypt
              ( input => p_str,
                key   => utl_raw.cast_to_raw('ABCDEFGHIJKLMNOPQRST987123456ABC') ) );
END;
/

Function to support VARCHAR2 datatype
=====================================

CREATE OR REPLACE FUNCTION PADSTR (P_STR IN VARCHAR2)
return varchar2
as
l_len number default length(p_str);
begin
return to_char(l_len,'fm00000009') ||rpad(p_str,(trunc(l_len/8)+sign(mod(l_len,8)))*8,chr(0));
END;
/


CREATE OR REPLACE FUNCTION VC_ENCRYPT (P_STR VARCHAR2)
return varchar2
is
  key_str varchar2(36) := 'ABCDEFGHIJKLMNOPQRST987123456ABC' ;
  return_val varchar2(256) := NULL;
begin
  dbms_obfuscation_toolkit.DESEncrypt(input_string =>p_str, key_string => key_str, encrypted_string => return_val );
  return (return_val) ;
END;
/

CREATE OR REPLACE FUNCTION  VC_DECRYPT (P_STR VARCHAR2)
return varchar2
is
  dec_data varchar2(256);
  key_str varchar2(36) := 'ABCDEFGHIJKLMNOPQRST987123456ABC' ;
  return_val varchar2(256) := NULL;
begin
  dbms_obfuscation_toolkit.DESDecrypt(input_string =>p_str, key_string => key_str, decrypted_string => dec_data );
  return_val :=substr(dec_data, 9,length(dec_data) -8);
  return trim(return_val);
END;
/


insert into t values('DESEncrypt','Secret Answer1',vc_encrypt(padstr('Secret Answer1')),crypt('Secret Answer1'));
insert into t values('DESEncrypt','Secret Answer2',vc_encrypt(padstr('Secret Answer2')),crypt('Secret Answer2'));
insert into t values('DESEncrypt','Secret Answer3',vc_encrypt(padstr('Secret Answer3')),crypt('Secret Answer3'));
insert into t values('DESEncrypt','Secret Answer4',vc_encrypt(padstr('Secret Answer4')),crypt('Secret Answer4'));
insert into t values('DESEncrypt','Secret Answer5',vc_encrypt(padstr('Secret Answer5')),crypt('Secret Answer5'));
insert into t values('DESEncrypt','Secret Answer6',vc_encrypt(padstr('Secret Answer6')),crypt('Secret Answer6'));

select data,vc_decrypt(varchar2_data_enc),decrypt(raw_data_enc) from t;



SQL> select data,vc_decrypt(varchar2_data_enc) from t;
select data,vc_decrypt(varchar2_data_enc) from t
            *
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 "fake_userid.VC_DECRYPT", line 8

IMPORT window
==============
$ echo $NLS_LANG
AMERICAN_AMERICA.UTF8

$ imp fake_userid file=enc3.dmp full=y
 
Import: Release 8.1.7.3.0 - Production on Mon Jan 19 17:33:22 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.3.0 - Production
 
Export file created by EXPORT:V08.01.07 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing fake_userid's objects into fake_userid
. . importing table                            "T"          6 rows imported
Import terminated successfully without warnings.
 
fake_server fake_userid idb21 /opt/home/fake_userid
$ sqlplus fake_userid
 
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Jan 19 17:33:41 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
Enter password: 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.3.0 - Production
 
SQL> select data,vc_decrypt(varchar2_data_enc),decrypt(raw_data_enc) from t;
select data,vc_decrypt(varchar2_data_enc),decrypt(raw_data_enc) from t
            *
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 75
ORA-06512: at "fake_userid.VC_DECRYPT", line 8
ORA-06512: at line 1
 

Tom Kyte
January 20, 2004 - 7:02 am UTC

dump the data using the DUMP() function and verify you have the same bits and bytes in both databases.

A FIELD INCLUDES BINARY DATA

reader, January 23, 2004 - 6:46 pm UTC

Hi Tom
Is there a easy way to check all of the fields in a table
wheather includ bniary data (probably part of some records)

Any ideas

Thanks!


Tom Kyte
January 24, 2004 - 8:35 am UTC

depends -- how do YOU define binary data?

is that a newline? does it include a tab? a linefeed?

what you would do is setup a string that includes characters you feel are to be discovered:

declare
l_string varchar2(1000) := chr(9)||chr(10)||chr(13); -- tab, CR/LF
l_string2 varchar2(1000) := rpad('a',length(l_string),'a');
begin
for x in ( select *
from t
where translate( c1, l_string, l_string2 ) <> c1
or translate( c2, l_string, l_string2 ) <> c2
.......... )
loop
......


there I would find all records that included the tab, carriage return, linefeed characters.

Since the "definition" of what is "binary data" differs by the character set you use, you'll have to set up the set of binary characters you want to look for.

Alternatively, you could use the set of "safe characters" and

where replace( translate(c1,l_safe,l_string2), 'a', '' ) is not null
or ......


(turn all safe characters into 'a', replace 'a' with nothing. if anything is left over -- then it contains bad characters.


Comparing Encrypted Values

Sami, March 02, 2004 - 11:11 am UTC

Dear Tom,

Is it possible to compare encrypted value(without DECRYPT) which is stored in the database.
For example,
select count(*)
from encrypted_tab T1
where not exist (
select 1
from another_encrypt_tab T2
where T1.encrypted_customer_id=T2.encrypted_customer_id)

Tom Kyte
March 02, 2004 - 6:43 pm UTC

sure, it is just data. as long as the SAME data was encrypted with the SAME key, they will encrypt to the SAME value.

password

reader, March 13, 2004 - 11:34 am UTC

If I connected to the database like,

C:>sqlplus scott/tiger@proddb,

is the password encrypted by oracle while transmission? Does this encryption occur automatically? Thanks.

Tom Kyte
March 13, 2004 - 12:05 pm UTC

yes and yes.

How can I do the same in my app?

Leonardo Zacché, April 12, 2004 - 5:03 pm UTC

If I connected to the database like,

C:>sqlplus scott/tiger@proddb,

is the password encrypted by oracle while transmission? Does this encryption
occur automatically? Thanks.


Followup:
yes and yes.
----------------------
How can I do the same in my app, supposing I'll have a MD5 hashed password stored in BD? How to MD5-hash the password BEFORE sending throw the network? is it a client dependant function?


Tom Kyte
April 12, 2004 - 7:11 pm UTC


You can use the advanced security option to encrypt ALL client/server network traffic.

Putting the encrypted data into varchar2 ?

A reader, July 29, 2004 - 5:47 am UTC

</code> https://cwisdb.cc.kuleuven.ac.be/ora10doc/appdev.101/b10802/d_crypto.htm <code>

This document (10g) states:

"If you want to store encrypted data of the RAW datatype in a VARCHAR2 database column, then use RAWTOHEX or UTL_ENCODE.BASE64_ENCODE to make it suitable for VARCHAR2 storage. These functions expand data size by 2 and 4/3, respectively."

Does the use these functions overcome the limitations described above when placing the RAW into a varchar2 ??

Thanks!


Tom Kyte
July 29, 2004 - 11:49 am UTC

yes, because it turns the raw data into hex code (0-9A-F)

encryption of stored procedure

Sean, August 11, 2004 - 10:40 am UTC

Hi Tom,

How do I compile the stored procedure so no one is able to see the code in the db?

Thanks so much for your help.



Tom Kyte
August 11, 2004 - 1:22 pm UTC

see the wrap executable, documented in the plsql guide

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96624/c_wrap.htm#1859 <code>

Wrap stored procedure

Sean, August 11, 2004 - 3:50 pm UTC

Thanks so much!

Actually, I looked at the PL/SQL user’s guide and searched the Oracle web site and Goole by using 'encryption of stored procedure', but didn't find any usefule things. Well, it is in Appendix and use different name.




Storing encrypted data in raw dataype

Valar G, August 23, 2004 - 2:56 pm UTC

Hi Tom,

In your replies to storing the encrypted data you have suggested that raw datatype is the recommended one. We are planning to encrypt ssn data in a demographic table and wanted to make sure that we chose the right data type.

There are two school of thoughts on this subject.
i.e. i)store the data as varchar field itself but alter the ssn field for the additional storage. This way the change would be transparent to the end user.

ii) Store it as raw datatype and change existing applications to support raw datatype

Are there any real performance/storage benefits by storing the ssn in a varchar field ? Could you please discuss / refer to articles which would discuss the benefits and downsides of storing them as either varchar / raw datatype ?

The queries hitting the table could be something like

select demdataid, dlid, crypt.DecryptString(ssn)
from demdata
where ssn = crypt.EncryptString(p_ssn);

Thanks,
Valar

Tom Kyte
August 23, 2004 - 3:19 pm UTC

i) no, the encrypted data would be "binary junk", not normal varchar2 data.

if your client had a NLS_LANG different from the database (eg: database = us7ascii on unix, client = we8iso... on windows for example) -- client would get GARBABLED GARBAGE upon retreiving this data.

it is unsafe to stuff into a character field things that are not valid characters in that character set. since encrypting a varchar2 returns a string that just contains bytes of any value 0x00..0xFF -- they may well return characters that are not characters (and WOE TO YOU that are using multi-byte charactersets, gets even worse)

ii) that is the only viable method I see.


or method

iii) create a new table -- ID_2_ENCRYPTED_SSN storing a sequence (use a varchar2 if you want) and a raw field.

then MOVE the ssn from the table it is in now -- put it into this table and replace the SSN with a sequence value....


if all the apps have to do is "crypt.decryptstring(ssn)" -- why bother encrypting? whose got the key here?

Thanks for the suggestions

Valar G, August 24, 2004 - 1:35 pm UTC

Hi Tom,

The key to the encrypted data would be set in the crypt package code which would be wrapped. We tested this setup with a set of 100000 records, encrypting the data using a 24 byte key and storing the encrypted value in a raw datatype. The datatype used for this was raw(128). Since the encrypted data occuppies at the most only 16 bytes, should we alter the table to be raw(16), as we know that ssn data is always going to be a fixed length and the key is set at 24 bytes. To facilitate search requests on the encrypted data, we have this encrypted field indexed too. Is it advisable to index on a raw datatype ? Any downsides in storage & performance ? 

The encrypted data when retrieved from the test setup was as follows,
SQL> r
  1  select demaltkey, utl_raw.length(demaltkey) len
  2* from demdata where rownum < 5

DEMALTKEY
--------------------------------------------------------------------------------
       LEN
----------
09014E4DB31DFC54746A734767C3BD28
        16

914BA0A481ECD8DDE1D3FE671EFF4647
        16

48756CCC879D437582C31C6CE826E477
        16

17E765F7A3DB10BB119769CB4B775B77
        16

The package description is as follows,
SQL>  desc crypt;
FUNCTION DECRYPTSTRING RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_DATA                         RAW                     IN
 P_KEY                          VARCHAR2                IN     DEFAULT
FUNCTION ENCRYPTSTRING RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_DATA                         VARCHAR2                IN
 P_KEY                          VARCHAR2                IN     DEFAULT
PROCEDURE SETKEY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_KEY                          VARCHAR2                IN

SQL> 

Good point though on the question of crypt.Decryptstring(ssn) !! Not all applications would have the crypt.Decryptstring(ssn) field in the select clause. The select list would be dynamically framed based on the privilege of the application user. I gave an example of how probably a select might be framed. In some cases, depending on the client requirement, we might only allow searches on the ssn data, but woudln't display the ssn field.

Thanks !!
Valar 

Tom Kyte
August 24, 2004 - 3:26 pm UTC

but if the crypt package has the key...

why why why bother encrypting? i mean the means to decrypt is right there, wrapped or not, it is right there -- even easier than if I had the key?

you can index raw just fine. it is just bytes.

Key Storage ??

Valar G, August 24, 2004 - 4:23 pm UTC

Hi Tom,

It is now become a requirement from the client that we encrypt the sensitive data. Hence the research into encrypting ssn data.

Now that you pointed out, I see a serious drawback in setting the key in a package like crypt. How would you suggest the keys be stored/handled ? The general preference here is to store the key in the database. May be I should think along the lines of what you discussed in method 3 / use a packaged function which would return the key only for the appropriate user, as suggested in your book.

Thanks,
Valar

Tom Kyte
August 24, 2004 - 7:34 pm UTC

key mgmt is *hard*.

you have to first figure out "who the heck am I protecting the data from?"

then, you have to make sure the key is not accessible to them.

for example, if the answer is "the DBA", the key cannot possibly be in the database! for the dba has access to it all.

if the answer is "from the end users, only authorized people should have access to it", there is per-chance a chance that the wrapped package is strong enough -- IF end users log in using their own credentials (you have the ability to GRANT or not)

so, you need to step back and answer "from whom are we trying to protect this data...." that'll go a long way to help answering where the key's may be (or not)

Update and Encrypt A Column

FS, January 15, 2005 - 10:50 pm UTC

Tom, what is the best (or recommended) way to encrypt a column of a table that has 4 million rows of data already? Can it be as simple as UPDATE TABLE ... SET ... within a procedure? Code examples would help.

Thanks

FS

Tom Kyte
January 16, 2005 - 11:19 am UTC

well, this is a majorly HUGE change that will involve changing the datatype.

For, I do not think your column is RAW already and the only safe way to store encrypted (hence BINARY) data is in a raw.

So, you will be changing the datatype:

alter table t add column NEW_COLUMN raw(N);

where N is larger than the existing string (see routine below, string will be padded out to length that is multiple of 8 bytes and 8 more bytes are added.  so 

(trunc(length(S)/8)+2)*8

if the length(s) is not a mulitple of 8 already -- just +1 if it is.  Warning: if you are using multi-byte charactesets, you need to think about this more, as it is BYTES, not characters that count here)

This package is loosely based on my work in Expert one on one Oracle, you can use des, des3, 8,16,24 byte keys -- whatever (you tweak it), this one uses a simple 8 byte key:


ops$tkyte@ORA9IR2> create or replace package crypt_pkg
  2  as
  3      function encryptString( p_data in varchar2,
  4                              p_key  in varchar2 default NULL )
  5      return raw;
  6
  7      function decryptString( p_data in raw,
  8                              p_key  in varchar2 default NULL )
  9      return varchar2;
 10  end;
 11  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body crypt_pkg
  2  as
  3
  4  g_charkey         varchar2(48);
  5
  6  function padstr( p_str in varchar2 ) return varchar2
  7  as
  8      l_len number default length(p_str);
  9  begin
 10      return to_char(l_len,'fm00000009') ||
 11               rpad(p_str, ( trunc(l_len/8)+sign(mod(l_len,8)) )*8, chr(0));
 12  end;
 13
 14  function unpadstr( p_str in varchar2 ) return varchar2
 15  is
 16  begin
 17      return substr( p_str, 9, to_number(substr(p_str,1,8)) );
 18  end;
 19
 20  function encryptString( p_data in varchar2,
 21                     p_key  in varchar2 default NULL ) return raw
 22  as
 23      l_temp      raw(4000);
 24      l_encrypted raw(4000);
 25  begin
 26      l_temp := utl_raw.cast_to_raw( padstr(p_data) );
 27
 28      dbms_obfuscation_toolkit.desencrypt
 29      ( input => l_temp,
 30        key => utl_raw.cast_to_raw(p_key),
 31        encrypted_data => l_encrypted );
 32
 33      return l_encrypted;
 34  end;
 35
 36  function decryptString( p_data in raw,
 37                          p_key  in varchar2 default NULL ) return varchar2
 38  as
 39      l_temp long;
 40  begin
 41      dbms_obfuscation_toolkit.desdecrypt
 42      ( input => p_data,
 43        key => utl_raw.cast_to_raw( p_key ),
 44        decrypted_data => l_temp );
 45
 46      return unpadstr( utl_raw.cast_to_varchar2(l_temp) );
 47  end;
 48
 49
 50  end;
 51  /
 
Package body created.


Then you can:

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select *
  4    from all_users
  5   where rownum <= 5 ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select username from t;
 
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
SCOTT
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add username_encrypted raw(40);
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t set username_encrypted = crypt_pkg.encryptstring(username, 'My8bytes' );
 
5 rows updated.
 
ops$tkyte@ORA9IR2> alter table t drop column username;
 
Table altered.
 
ops$tkyte@ORA9IR2> select * from t;
 
   USER_ID CREATED   USERNAME_ENCRYPTED
---------- --------- ----------------------------------------
         0 18-SEP-04 9C0677EC9468547CF8CE39AE5EBAAFAE
         5 18-SEP-04 AE7E628293C6FD31624C164AB0F7EEA6
        11 18-SEP-04 B57145CC912BB270D6D21CD3B63AF8FC
        19 18-SEP-04 AE7E628293C6FD31248141FA82D35D2D
        49 24-SEP-04 B57145CC912BB270B4E3DFB400878617
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select crypt_pkg.decryptstring(username_encrypted,'My8bytes' ) from t;
 
CRYPT_PKG.DECRYPTSTRING(USERNAME_ENCRYPTED,'MY8BYTES')
-------------------------------------------------------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
SCOTT



You will most likely want to employ "do it yourself parallelism" as you'll find encryption and calling plsql from sql 4,000,000 times to be "measurable" in the amount of time it takes:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10498431232211

Florin, April 29, 2005 - 10:41 am UTC

Dear Tom,

I have a table and one of its fields (CONN_PARAMS varchar2(2000)) contains data as following:

SQL> select CONN_PARAMS from gn1_connect_params where rownum<2;

CONN_PARAMS
-------------------------------------------------------------------------------------------------------------
USER=AIXAPP1;PASSWORD=AIXAPPO;INSTANCE=AIXX9I;PORT=1521;HOST=ep65001;PM_CONNECT_TYPE=ORA;UH_FTP_HOST=ep65001;

I need to create a package for encrypt and decrypt only the substr of CONN_PARAMS , I mean only the part of the PASSWORD (in my case only "AIXAPPO" should be encrypted). 


Would you please help me with this?

Many many thanks in advance! 

Tom Kyte
April 29, 2005 - 10:47 am UTC

you need a new field (of type RAW)

you need to substr out what you want to substr out (that is easy)

you need to use dbms_obfuscation_toolkit (9i and before) or the new dbms_crypto package in 10g to encrypt it.

lots of examples on this site, search for dbms_obfuscation_toolkit

DIDN't SEE ANSWER FOR THIS

A reader, May 18, 2005 - 7:34 am UTC

I tried your example, but have problem for the decrypt data size. You have 
explained it, but I still not understand, so... How can I get rid of those extra 
invisible characters.... Thanks.

create table t ( data varchar2(9), data_enc raw(16) );

 1  create or replace function crypt( p_str in varchar2 )
 2  return raw
 3    as
 4        l_data  varchar2(255);
 5    begin
 6        l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
 7            return dbms_obfuscation_toolkit.DESEncrypt
 8               ( input => utl_raw.cast_to_raw(l_data),
 9                            key => utl_raw.cast_to_raw('MagicKey') );
10* end;

 1  create or replace function decrypt( p_str in raw ) return
 2  varchar2
 3  as
 4  begin
 5        return utl_raw.cast_to_varchar2(
 6              dbms_obfuscation_toolkit.DESdecrypt
 7              ( input => p_str,
 8                key   => utl_raw.cast_to_raw('MagicKey') ) );
 9* end;


  1  select data, LENGTH(DATA),
  2         data_enc, LENGTH(DATA_ENC),
  3         decrypt(data_enc), LENGTH(decrypt(data_enc))
  4* from t
SQL> /

DATA      LENGTH(DATA) DATA_ENC                         LENGTH(DATA_ENC)
--------- ------------ -------------------------------- ----------------
DECRYPT(DATA_ENC)
-------------------------------------------------------------------------
LENGTH(DECRYPT(DATA_ENC))
-------------------------
012345678            9 07CF8213EDC46C277C720164D915B70C               32
012345678
                       16 ( why this is 16? Are there extra invisible 
characters?)


Thank you again. 

Tom Kyte
May 18, 2005 - 9:11 am UTC

trim it off.

Tried..but in vain !!!

A reader, May 18, 2005 - 9:18 am UTC


Tom Kyte
May 18, 2005 - 9:24 am UTC

dump it (dump() the function -- see what bytes are there.

A reader, May 18, 2005 - 10:01 am UTC

Typ=1 Len=16: 112,97,115,115,119,111,114,100,49,50,51,52,32,32,32,32

I used your crypt/decryps functions. And then

select dump(decrypt(crypt('password1234')))
from dual

Tom Kyte
May 18, 2005 - 11:19 am UTC

they are blanks - spaces.

Wrong Key Values display same result ?

KP, July 12, 2005 - 2:38 am UTC

I created the package crypt_pkg as per your followup on Jan 15, 2005.  Checked it with this example,

CREATE TABLE CHK1 AS SELECT Crypt_Pkg.encryptString(owner, 'S1234567') EN_OWNER, OWNER, OBJECT_NAME FROM ALL_OBJECTS
WHERE ROWNUM <=2 ;

Table created.

SQL> SELECT * FROM CHK1 ;

EN_OWNER
--------------------------------------------------------------------------------

OWNER                          OBJECT_NAME
------------------------------ 
7E4D9E4BF4AE71165581AE53C6077141
SYS                            /1005bd30_LnkdConstant

7E4D9E4BF4AE71165581AE53C6077141
SYS                            /10076b23_OraCustomDatumClosur

--------------------------------------------------------
The value was getting extracted correctly when I give the
correct key

SELECT Crypt_Pkg.decryptstring(EN_OWNER, 'S1234567') endname, OBJECT_NAME FROM CHK1 ;

ENDNAME
--------------------------------------------------------------------------------

OBJECT_NAME
------------------------------
SYS
/1005bd30_LnkdConstant

SYS
/10076b23_OraCustomDatumClosur
---------------------------------------------------------

Now, I changed the key from 'S1234567' to 'S1234566' the last character was changed for testing.  However, it still returned me the correct values !.

SELECT Crypt_Pkg.decryptstring(EN_OWNER, 'S1234566') endname, OBJECT_NAME FROM CHK1 ;

ENDNAME
--------------------------------------------------------------------------------

OBJECT_NAME
------------------------------
SYS
/1005bd30_LnkdConstant

SYS
/10076b23_OraCustomDatumClosur
-----------------------------------------------------------

I tried with other combinations for the key, but couldn't get the same results.  Is it an odd value for this key alone or a known issue ? 

Tom Kyte
July 12, 2005 - 4:52 pm UTC

'S1234577'


  1  SELECT Crypt_Pkg.decryptstring(EN_OWNER, 'R1234576') endname, OBJECT_NAME FROM
  2* CHK1
ops$tkyte-ORA9IR2> /

ENDNAME
-------------------------------------------------------------------------------
OBJECT_NAME
------------------------------
SYS
/1005bd30_LnkdConstant

SYS
/10076b23_OraCustomDatumClosur


it (the encryption algorithm) uses 7 bits, S and R, 6 and 7 have the same 7 bits....

 

How safe is the Key ?

KP, July 21, 2005 - 3:05 am UTC

I read some reviews / comments about keeping the KEY safe from database administrators and other developers etc. Some options were having part of the key concatenated to other part in a PL/SQL and 'wrapping' the package. Other was to store it outside the database, another option was to obfuscate the key itself etc. Even with all these methods, we should be having a package / procedure which converts this to actual data to be used by the application ? So, the question is whether this procedure which decrypts the data be in the application level or the database level. If it is in the DB side, then, the security is compromised as anybody who has access to this procedure will be able to view the data as well.

Tom Kyte
July 21, 2005 - 8:01 am UTC

you have to trust someone, if the key is in the middle tier, then someone at the middle tier will have the key too.

Oracle's DES implementation does not match with others.

Suresh, September 20, 2005 - 9:05 pm UTC

We are using DBMS_OBFUSCATION_TOOLKIT in Oracle 9i to encryt data using DES algorithm. Similarly, we have external Java program doing DES encryption. The key used in both the places are same. But encrypted strings do not match. Below illustration shows differences in encrypted out put from Oracle and from "openssl" program:
SQL> l 
  1  declare
  2             -- have a 16 character input string
  3          v_data_string                      VARCHAR2(200) := 'oracle12oracle12';
  4          v_key                                      VARCHAR2(24)  := 'key12345';
  5          v_encrypted_data           VARCHAR2(64);
  6  BEGIN
  7
  8             DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(INPUT_STRING => v_data_string,
  9                                                                                      KEY_STRING => v_key,
 10                                                                                      ENCRYPTED_STRING =>v_encrypted_data
 11                                                                                      );
 12                                                                                                             dbms_output.put_line(utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(v_encrypted_data))));
 13
 14* END;
SQL> /
tT+AwhmxJNW1d5GTmwJzLg==

PL/SQL procedure successfully completed.

----------------------
Using openssl:

suresh:~ $ openssl enc -des-cbc -in tempp -k key12345 -a  -base64 -nosalt;
bXOUmORXUvGy310+j0EpOVV/wO7LFZAw
suresh:~ $

So, the question is Oracle's implementation of DES is different from others? If yes what's the work around to handle encrypted code amongs different programs.
 

Tom Kyte
September 21, 2005 - 6:46 pm UTC

see note Note 225214.1, it should be interoperable, there might be an initialization vector (IV) used by that program - although, I'm not sure what the program "openssl" does.

How to find if the column has encrypted data

A reader, January 11, 2006 - 6:10 pm UTC

Hi Tom,

We have few tables tables with encrypted columns but I don't have the table lists which are encrypted .

Is there a way to find the list of tables and its columns that are encrypted in a given schema ? any v$ or dba_ views available ?
Or can you provide any link related to this.

Thanks in advance.


Tom Kyte
January 12, 2006 - 10:53 am UTC

ops$tkyte@ORA10GR2> desc user_encrypted_columns
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 COLUMN_NAME                              NOT NULL VARCHAR2(30)
 ENCRYPTION_ALG                                    VARCHAR2(29)
 SALT                                              VARCHAR2(3)

 

How to find if the column has encrypted data - is it availabe in 9i r2

dhar, January 12, 2006 - 11:40 am UTC

Hi Tom,

Thanks for the details.I saw your DB is 10gr2. 

We are using 9ir2 and I'm not able to see this view . Is it available only from 10g ? or am i not seeing it properly ?

======================================================
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
=========================================================

SQL> desc user_encrypted_columns
ERROR:
ORA-04043: object user_encrypted_columns does not exist

If its only from 10g is there a way or workaround to find in 9ir2 ?

Thanks for your time Tom.

Thanks,
dhar

 

Tom Kyte
January 12, 2006 - 11:53 am UTC

because column level encryption is a new 10gr2 feature

it does not EXIST as a concept in 9i - hence there is no view.


there is no such thing as an encrypted column in that release. IF you happen to be encrypting data and storing it in the table encrypted, then it would be up to you to store metadata somewhere reminding you of that fact - we are not involved.

Thanks Tom

Dhar, January 12, 2006 - 12:49 pm UTC

OK , I got you.

I should check with my previous DBA (has moved out) who had stored it in some spreadsheet and that is not available now..


Thanks,
Dhar.



Insert using MD5 logic

Magesh, January 30, 2006 - 12:08 pm UTC

Tom,
I am trying to practice the MD5 algorithm, please help me to find out what am i doing wrong.

CREATE TABLE CRYPTO
   (    TEXT_DATA VARCHAR2(30), 
    CRYPT_DATA RAW(64)
   );

SQL> set serveroutput on
SQL> r
  1  declare
  2  a raw(40) := utl_raw.cast_to_raw('Test');
  3  begin
  4  dbms_output.put_line(dbms_obfuscation_toolkit.md5(input=>a));
  5* end;
0CBC6611F5540BD0809A388DC95A615B

PL/SQL procedure successfully completed.

SQL> declare
  2  a raw(40) := utl_raw.cast_to_raw('Test');
  3  pwd raw(40);
  4  begin
  5  select dbms_obfuscation_toolkit.md5(input => a) into pwd from dual;
  6  insert into crypto (text_data,crypt_data)
  7  values('M5 logic - pwd - Test',pwd);
  8  end;
  9  /
select dbms_obfuscation_toolkit.md5(input => a) into pwd from dual;
                                       *
ERROR at line 5:
ORA-06550: line 5, column 43:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored

SQL> r
  1  declare
  2  a raw(40) := utl_raw.cast_to_raw('Test');
  3  --pwd raw(40);
  4  begin
  5  --select dbms_obfuscation_toolkit.md5(input => a) into pwd from dual;
  6  insert into crypto (text_data,crypt_data)
  7  values('M5 logic - pwd - Test',dbms_obfuscation_toolkit.md5(input => a));
  8* end;
insert into crypto (text_data,crypt_data)
                                       *
ERROR at line 6:
ORA-06550: line 7, column 67:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

When I try to use dbms_output, it prints out normally. But when I try to insert the same, I am getting error. What am I doing wrong?

Thanks for you help. 

Tom Kyte
January 30, 2006 - 3:29 pm UTC

You cannot use named notation in a plsql call in SQL...

And the way they overloaded the md5 function, they basically made it impossible to call it from sql since you NEED to use named notation in this case.

So, you would wrap the md5 call in your OWN plsql function, to hide the input=>a bit.

dbms_obfuscation_toolkit.md5

Magesh, January 30, 2006 - 3:48 pm UTC

Thanks a lot for that input. Another quick question, starting 10g, am I correct if I say that TDE is considered to be an alternative to using dbms_obfuscation_toolkit? Infact, TDE is more secure and involves no coding/key maintenance by DBA/App. owner compared to dbms_obfuscation_toolkit? Also, if time permits, can you also give a sample coding of how to use dbms_obfuscation_toolkit.md5 in pl/sql using WRAP?

Thanks again for all your inputs.

Tom Kyte
January 30, 2006 - 3:50 pm UTC

In 10gR2 if your goal is to store a column encrypted on disk - transparent data encryption can by used.


You are using a hash however, not encrypting data?


I don't get "how to use
dbms_obfuscation_toolkit.md5 in pl/sql using WRAP?"

you just write plsql and if you want to obscure your source code, you WRAP it using the command line tool wrap? md5 doesn't change anything...

Seguridad

eri sanchez, February 02, 2006 - 11:03 am UTC

como controlo el acceso a la base de datos
quiero asegurarme que las actualizaciones de las tablas
son solo a través de las aplicaciones y no usando ningun otro software

Tom Kyte
February 02, 2006 - 12:20 pm UTC

You would be interested in secure application roles and n-tier proxy authentication.


</code> http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/toc.htm <code>

you can grant the privs to do things to your tables to a role. This role can only be enabled by running a procedure. The procedure can verify that it is being called by the application.


If your application uses a "constant user/pass", that is easy - the procedure checks the username.

If not, you can use ntier proxy authentication - then your procedure makes sure the proxy user is the application user and the user running the procedure is allowed to enable the role for that application - and then enable it.

no role, no privileges.

A reader, October 27, 2006 - 7:22 pm UTC

I'm trying to create a package to wrap the encryption, but I'm having problems with the pragma -- I need to call it within a sql statement.  Here's a sample of the package:
SQL> create or replace package foo
  2  as
  3     function encrypt (val in varchar2) return varchar2;
  4     pragma restrict_references (encrypt, wnds, wnps);
  5     function decrypt (val in varchar2) return varchar2;
  6     pragma restrict_references (decrypt, wnds, wnps);
  7  end;
  8  /

create or replace package body foo
as
   function encrypt (val in varchar2) return varchar2
   is
      v_data varchar2(300);
   begin
       dbms_obfuscation_toolkit.desencrypt (
          input_string => val
        , key_string => 'MagicKey'
        , encrypted_string => v_data);
       RETURN utl_raw.cast_to_raw(v_data);
   end;


Package created.

SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14     function decrypt (val in varchar2) return varchar2
 15     is
 16        v_data varchar2(300);
 17     begin
 18        dbms_obfuscation_toolkit.desdecrypt (
 19           input_string => utl_raw.cast_to_varchar2(hextoraw(val))
 20         , key_string => 'MagicKey'
 21         , decrypted_string => v_data);
 22        RETURN v_data;
 23     end;
 24  end;
 25  /
show error

Warning: Package Body created with compilation errors.

SQL> Errors for PACKAGE BODY FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PLS-00452: Subprogram 'ENCRYPT' violates its associated pragma
14/4     PLS-00452: Subprogram 'DECRYPT' violates its associated pragma

I tried encrypt using your example above, and it worked great, but couldn't figure out the package. 

Tom Kyte
October 27, 2006 - 8:27 pm UTC

dbms_obfuscation_toolkit does not assert any purity level in 9i (it does in 10g, but dbms_crypto should be used there)

but it is OK, you don't need to assert the purity.



ops$tkyte%ORA9IR2> create or replace package foo
  2  as
  3     function encrypt (val in varchar2) return varchar2;
  4     function decrypt (val in varchar2) return varchar2;
  5  end;
  6  /

Package created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package body foo
  2  as
  3     function encrypt (val in varchar2) return varchar2
  4     is
  5        v_data varchar2(300);
  6     begin
  7         dbms_obfuscation_toolkit.desencrypt (
  8            input_string => val
  9          , key_string => 'MagicKey'
 10          , encrypted_string => v_data);
 11         RETURN utl_raw.cast_to_raw(v_data);
 12     end;
 13
 14  function decrypt (val in varchar2) return varchar2
 15     is
 16        v_data varchar2(300);
 17     begin
 18        dbms_obfuscation_toolkit.desdecrypt (
 19           input_string => utl_raw.cast_to_varchar2(hextoraw(val))
 20         , key_string => 'MagicKey'
 21         , decrypted_string => v_data);
 22        RETURN v_data;
 23     end;
 24  end;
 25  /

Package body created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select foo.encrypt( '12345678' ) from dual;

FOO.ENCRYPT('12345678')
-------------------------------------------------------------------------------
F1856C0E6E6CB4AC
 

A reader, October 28, 2006 - 4:11 pm UTC

Thanks! This will work.

Indexing an encrypted column

Mohit Agarwal, November 03, 2006 - 12:05 pm UTC

We need to allow users to search through a multi-million record table using an encrypted column. The key itself is stored in a separate secure table. You call the decryption function, pass in the PK of the record you're attempting to decrypt, it finds the decryption key and returns the decrypted data.

They also need us to do LIKE searches on this column. I've already argued that this is a bad idea and we should require an exact match but its not my decision to make.

Given that, if I created a function based index on this table using UPPER(DECRYPT(PK)), would it be a security loop hole. Can someone read the data in the function based index itself? Can this be done through SQL or would it take someone who had a DBA role to do this (though they could just as easily decrypt the data since they can access the secure table). If someone got a hold of the server harddrive, could they read the FBI data?

Would a query like the one utilize this index?

SELECT *
FROM mytable
WHERE UPPER(DECRYPT(PK)) LIKE UPPER('John%');


Thanks,
Mohit Agarwal

Tom Kyte
November 03, 2006 - 12:20 pm UTC

it is not only a bad idea, it is just "sort of silly"

what is the point of encrypting this data? Since you store the key in the same database, it is not about protecting it in the event of theft (it cannot be)

And since encryption is a silly way to implement any sort of access control - (silly being a pleasant term, could be stronger), what is the POINT of encrypting this data in the first place?????????


You have a security hole already, you mistakenly believe you have done something positive security wise by encrypting data when you have in fact done absolutely nothing.


encryption is good to secure data IN THE EVENT OF THEFT OF YOUR DATABASE.

that is about it.


Your DBAs can decrypt anything here (by definition)
Anyone that stole your backup can decrypt anything here (by definition)

All you've done is made is so that people who are legitimately allowed to access these specific rows and columns are made to pay an extremely high price to do so.


so, my recommendation would be to drop the encryption, it isn't doing anything positive for you and is only degrading overall system performance.

Encryption / Decryption

bc, November 03, 2006 - 2:54 pm UTC

Our department is implementing a similar encryption / decryption mechanism as described by Mohit Agarwal from Milwaukee, WI.

The difference is that they split the encryption key store half of it in a wrapped function and the other half in a table . Access to this keystore table is controlled by Row Level Security and this table resides on a different table space.

What do you think about this approach ?


Tom Kyte
November 04, 2006 - 12:11 pm UTC

it is POINTLESS do you understand?


Please tell me your goal with encryption, what are you trying to protect against and I'll tell you then why what you are doing is futile (99.99% chance I'll be able to do that)

followup above

A reader, November 05, 2006 - 6:49 am UTC

so what's your suggestion to that case?

Tom Kyte
November 05, 2006 - 9:15 am UTC

to WHAT case???? no clue what you might be referring to.

I just see me asking a bunch of questions these last couple of go arounds.

someone must tell me what the GOAL is here, then we can start discussing.

Indexing an encrypted column

Mohit Agarwal, November 21, 2006 - 4:32 pm UTC

The goal is to protect the data in the event the someone stole our database. It is not being used to enforce access control. There are some federal/state statutes that apply to us where we need to encrypt certain customer information (SSN, Name, Address, Credit Card numbers etc.). All this information must be physically stored encrypted on our disks. That's what we've done.

Access to the decryption function is limited to a single application account - only the DBAs know the password to it. Similarly a single application account has select privileges on the table containing the encrypted columns. Without knowing the userid/pwd can someone read this information if they managed to steal our backup drives for?

Is TDE much different? I realize Oracle handles the key management but anyone with select privilege on the table gets to see the data unencrypted. But if someone stole the hard drive they wouldn't be able to do that.

We should be upgrading to 10gR2 soon (hopefully) and we're hoping to use TDE to accomplish this since it allows indexing of encrypted columns. We just needed an interim solution until then.

Tom Kyte
November 22, 2006 - 4:08 pm UTC

Mohit,

you have stored the key in the database, so - again - I very very patiently ask:

TO WHAT PURPOSE.

If I steal your database, I sort of - well - apparently - I'll have your KEYS.

Now what????????

when I steal or break into your database, I am SYSDBA, I bypass ALL access control - passwords be darned - they do not count anymore.




With TDE, the keys are

a) not stored in the database
b) released to the database with a password (so even if you stole them, you cannot use them unless you have the password too, but - they wouldn't be stored with the backups)



Indexing an encrypted column

Mohit Agarwal, November 29, 2006 - 4:29 pm UTC

Thanks for your response Tom. Before TDE how would someone encrypt sensitive data on their DB in a secure fashion so that simply having access to the DB hard drive does not allow someone to read this information?

If you have SYSDBA privileges or determine the userid/password for the generic application user account which is allowed to decrypt this data, I guess there's nothing we can do about it. DBA's should not be giving out their passwords or using an easily breakable password. Same thing for the application account userid/password. Short of that however, we want something reasonably secure.

We need an encryption solution that will prevent someone from decrypting sensitive data just because they've managed to steal our backup drive or somehow broken into our network and can access our production DB server. As long as they don't know the SYSDBA or application user login information, we need our data to be safe.

With TDE, it seems we'll be in good shape. We need an interim solution till we get there. Do you have any broad guidelines for an approach to accomplish this or does what we have meet this basic criteria.

Thanks again for taking the time to respond. Its very much appreciated.

Sincerely,
Mohit Agarwal

Tom Kyte
November 30, 2006 - 9:37 am UTC

you would have to implement your own secure key management - that is the big feature introduced here.

encryption - trivial
key management - really hard


I'll ask you again: to what end is encrypting the data? If it is to protect it from hacking and theft of backups - you have DONE NEITHER, NOT AT ALL.

And given your implementation - I cannot see any reason for doing it at all.


if I steal your backup drive, I AM SYSDBA AFTER THAT. There are NO PASSWORDS, I'll just fire up Oracle, mount your datafiles, and I'll be connected as "/ as sysdba"

Indexing an encrypted column

Mohit Agarwal, November 30, 2006 - 3:52 pm UTC

Thanks Tom. I get it now.


Where Condition on Decrypt

Arindam Mukherjee, January 16, 2007 - 1:04 am UTC

Sir,

On reading this thread, I have learnt so much on encryption and decryption. After using your function, I have got the following output.

SQL> select data, data_enc, decrypt(data_enc) data
2 from t1
3 where data = '777777.123';

DATA DATA_ENC
---------- --------------------------------
DATA
--------------------------------------------------------------
777777.123 F77E0EC82E8DF50E09104CC4B86AE9A1
777777.123


SQL> select data, data_enc, decrypt(data_enc) data
2 from t1
3 where decrypt(data_enc) = '777777.123';

no rows selected

My Question - Why I am not getting the row in the second query where I am getting from the first? I know "WHERE" clause is evaluated first before "SELECT". Please help me get it.

Encryption

Arindam Mukherjee, February 04, 2007 - 11:02 pm UTC

Sir,

Please respond to my question mentioned above.

Regards,
Tom Kyte
February 05, 2007 - 7:40 am UTC

no complete test case
no look

Can I add md5 value from Oracle 9.2

parag j patankar, February 07, 2007 - 3:30 am UTC

Hi,

Pl find below an example of generating XML output for a client. I want to add md5 checksum at header as cleint wants it. Is it possible to do it from 9.2 database ?

1 select
2 xmltype('<?xml version=''1.0'' encoding=''UTF-8''?>
3 '||
4 xmlelement("bank",
5 xmlattributes(' http://www.w3.org/2001/XMLSchema' AS
6 "xmlns:xsi",
7 'XXXXXXXXXXXXXXX.xsd' AS
8 "xsi:nonamespaceSchemaLocation" ),
9 xmlelement( "NoOfRecords", nvl(max(numrecs), '0')),
10 xmlelement( "TotalAmount", nvl(max(totamt), '00.00')),
11 xmlagg(
12 xmlelement("Transaction",
13 xmlforest(
14 intraday
15 as "MessageCode",
16 actnum
17 as "ToAccountNo"
18 )))).extract('/*').getclobval())
19 testing
20 from
21 (
22 select
23 '0100' intraday,
24 empno actnum
25 from emp
26 where empno > 7600
27 ),
28 (
29 select count(*) numrecs,
30 to_char(sum(sal), '00000000000000000000.00') totamt
31 from emp
32 where empno > 7600
33* )
13:54:17 SQL> /

TESTING
----------------------------------------------------------------------------------------------------------------
<bank xmlns:xsi=" http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="XXXXXXXXXXXXXXX.xsd">
<NoOfRecords>8</NoOfRecords>
<TotalAmount>00000000000000016450.00</TotalAmount>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7654</ToAccountNo>
<CheckSum>???????????????????????<CheckSum/> ============> I want to add md5 checksum here
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7698</ToAccountNo>
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7782</ToAccountNo>
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7788</ToAccountNo>
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7844</ToAccountNo>
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7876</ToAccountNo>
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7777</ToAccountNo>
</Transaction>
<Transaction>
<MessageCode>0100</MessageCode>
<ToAccountNo>7934</ToAccountNo>
</Transaction>
</bank>

13:54:18 SQL>

Is it possible to add md5 checksum thru SQL ? Pl suggest.

best regards
PJP

TDE(Transparent data encryption)

Aditya Tiwari, July 02, 2007 - 7:19 am UTC

Hello ,

I am using wallet manager for Transparent Data Encryption (TDE). I created the wallet from wallet manager and saved it to default location. Then i updated my
sqlnet.ora with

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/C:/Documents and Settings/tiwariad/ORACLE/WALLETS)))


Then i opened my wallet with the command
alter system set encryption wallet open authenticated by "aditya123";
System altered.


Then i gave command for setting master key
alter system set encryption key authenticated by "aditya123";


It gives me following error

alter system set encryption key authenticated by "patni123"
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ztsmstore failed],
[18446744073709550614], [], [], [], [], [], []

Please help me in this matter. I am using 10.2.0.1.0.

Thanks
Aditya Tiwari

Tom Kyte
July 03, 2007 - 9:50 am UTC

ora-600
ora-7445
ora-3113

all imply "support", use metalink.oracle.com

encrypt/decrypt passwords

Suresh, July 05, 2007 - 6:26 am UTC

Hi Tom,

I have a chance to work on encrypting and decrypting psswords from Forms 6i.
The following code is working fine outside but not in forms.
declare
usname varchar2(25);
pwd1 varchar2(255);
input_string VARCHAR2(16):=:hrpassword.npwd;
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
key_string VARCHAR2(16) := 'magickey';
raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(2048);
encrypted_string VARCHAR2(2048);
decrypted_raw RAW(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
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 ***';
-- 1. Begin testing raw data encryption and decryption
--BEGIN
--dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
--dbms_output.put_line('> Raw input : ' ||
--UTL_RAW.CAST_TO_VARCHAR2(raw_input));
BEGIN
--message('1');
dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,
key => raw_key, encrypted_data => encrypted_raw );
message('> encrypted hex value : ' ||
rawtohex(encrypted_raw));
--message('2');
:hrpassword.npwd:=encrypted_raw;
update tested set pwd=:hrpassword.npwd where uname=:hrpassword.username;
message('Password is encrypted');
commit_form;

dbms_output.put_line('> encrypted hex value : ' ||
rawtohex(:hrpassword.npwd));
-------descryption of Password------------
message('1');
/*select uname,pwd into usname,pwd1 from tested
where uname ='suresh' and pwd = 'sureshus';*/
message('2');
dbms_obfuscation_toolkit.DESDecrypt(input =>encrypted_raw,--raw_input,
key => raw_key, decrypted_data => decrypted_raw);
dbms_output.put_line('> Decrypted raw output : ' ||
UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
message('3');
:hrpassword.dpw:=decrypted_raw;
--update tested set pwd=decrypted_raw where uname=:hrpassword.username;
--dbms_output.put_line('> Decrypted raw output : ' ||
--UTL_RAW.CAST_TO_VARCHAR2(:hrpassword.dpw));
--dbms_output.put_line('> ');
message('4');
if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
update tested set pwd=:hrpassword.dpw where uname=usname;
commit_form;
Message('Decryption is done...');
Message('> Raw DES Encyption and Decryption successful');
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
--END;
dbms_output.put_line('> ');
END;

can you please spare some time for me.

thanks,

Tom Kyte
July 05, 2007 - 1:09 pm UTC

"sorry"

laughing out loud - my car won't start - please spare some time for me.


there, I've given you precisely as much information as you have me regarding our problems.



encrypt/decrypt passwords in forms 6i

suresh, July 12, 2007 - 1:41 am UTC

Hi Tom,
here Iam sending a precised and cutshort code of what I have coded in forms 6i for encrypting and decxrpting the same in two different buttons.I can only encryption of the given password not the decryption. when the decrypt button is hit, the encrypted password is again getting encrypted.

Please kindly respond and help me out from this issue.

Thanks in advance,
Suresh

encrypt/decrypt passwords in forms 6i

suresh, July 12, 2007 - 1:43 am UTC

Hi Tom,
here Iam sending a precised and cutshort code of what I have coded in forms 6i for encrypting and decxrpting the same in two different buttons.I can only encryption of the given password not the decryption. when the decrypt button is hit, the encrypted password is again getting encrypted.

encryption
declare
input_string varchar2(255):=:npwd;
usname varchar2(20);
pwd1 varchar2(20);
bool number;
npwd varchar2(255);
l_data varchar2(255);
l_stage varchar2(20);
begin
l_stage:='At select';
select uname,pwd into usname, pwd1 from users
where uname=:hrpassword.username and pwd=:hrpassword.opwd;
l_stage:='After Select';
if :hrpassword.username is not null and :hrpassword.npwd is not null then
if :hrpassword.rt_new_password=:hrpassword.npwd
then
l_stage:='At Encrypt';
--:hrpassword.npwd :=rpad(:hrpassword.npwd,(trunc(length(:hrpassword.npwd)/8)+1)*8,chr(0));
dbms_obfuscation_toolkit.DESEncrypt
(input_string => :hrpassword.npwd,
key_string => 'MagicKey',
encrypted_string=>l_data);
:ENC_PWD:=l_data;
-- :npwd := rtrim( l_data, chr(0) );
update users set pwd= l_data where uname=:hrpassword.username;
l_stage:='After Encrypt';
Message('Password is encrypted and saved');
commit_form;
end if;
end if;

decryption

declare
usname varchar2(20);
pwd1 varchar2(20);
l_data varchar2(255);
l_uname varchar2(30):='suresh';
l_pwd varchar2(30):='sureshus';
begin
select uname,pwd into usname,pwd1 from users
where uname=:hrpassword.username;-- and pwd=:hrpassword.npwd;
--if pwd1=:hrpassword.npwd then
dbms_obfuscation_toolkit.DESDecrypt
(input_string=>pwd1,
key_string=>'magickey',
decrypted_string=>l_data);
--:hrpassword.npwd:=rtrim(l_data,chr(0));
update users set pwd=l_data where uname=:hrpassword.username;
message('Password is decrypted');
commit_form;
--end if;
end;
Please kindly respond and help me out from this issue.

Thanks in advance,
Suresh

Data Encryption an a star schema?

J, October 23, 2007 - 2:24 pm UTC

Hello Tom.

DW design question: One of the senior DA's wants us to store encrypted column data (such as "customer name") within the dimensions of a conformed star schema. But, says I, user access to the dimensions will be through views - we can either remove sensitive columns from the views or REPLACE values with '*' or some such thing. In large dimensions (populated w/100k+ rows), what technical constraints would impact the loading and retrieval of encrypted data?

We're writing up policies and procedures right now - we're not even in the design phase so I don't have any tests that I can perform. I am just concerned about implementing and adhering to policies that might not make sense when dealing with large volumes of data.

Am I off base with this concern? Is it common to have encrypted data stored within Dimensions? If so, would tricks like vertical partitioning be of any use?

Many thanks and best regards.
Tom Kyte
October 23, 2007 - 6:42 pm UTC

who/what is doing the encryption here and what is the purpose (goal) of encrypting anything in YOUR case (why are you doing this, what is the goal)

Dimension Encryption followup

J, October 23, 2007 - 7:19 pm UTC

Hello - thanks for responding!

We'd be getting unencrypted data in, perform transforms and scrubbing, and then load up the data - some of which is of a sensitive nature. At this time, policies are in discussion as to whether to encrypt those sensitive columns, to use views that restrict access to those columns, or to use a combination of encryption + views (the most favored case, imho).

We will be running v10.2, partitioned, stars and conformed dimensions. Some of the Dimensions will get rather large over time (500K+), and I want to ensure that the policies and practices we are developing protect the sensitive data while minimizing system impacts.

My question really boils down to - "Is there a best practice guide for managing encrypted data in a dimensional Oracle database?"

Best Regards.
Tom Kyte
October 24, 2007 - 8:53 am UTC

my question is "from whom are you trying to protect the data from"

with 10.2, TDE (transparent data encryption) is available - it protects the data from theft (someone stealing your backup for example). That is what encryption is good for. Access controls (virtual private database - row and column level security) is used to protect the data from end users. Data Vault - to protect slices of data from multiple DBA's.

I think you are maybe thinking of using encryption in place of access controls and that would be wrong.

Encryption & Decryption using DES

Omer, March 05, 2008 - 3:33 am UTC

Hi Tom,
We have database 10g release 2 with more than 10k concurrent users. In database, there is a table FX_USERS, where we save application users. For every application user, there is a database user. e.g. FX_USERS table having 10k users and same number of users is in database. Password in this table are encrypted (MD5) and cannot be decrypted.
Now we are going to change this structure. There will be single user (TCE) in database which will be shared by every application user.
In login screen, we will authenticate the password of application user and if it is correct then user will be connected with database using TCE oracle user. So every application user will login through TCE in database. We will set the client identifier to identify the sessions.
The password of oracle user (TCE) will be saved in database table and will be encrypted using DES and will be decrypted in login screen using dbms_crypto.decrypt. The key to decrypt password will be taken from flat file, saved in application server. Our consultant says, as key to decrypt password is in file in application server so it is safe. No body can access this.
My concern is, even the key to decrypt oracle user password is in file, stored in application server, I or any other developer can decrypt password of oracle user by looking the code in login screen (oracle form). This looks as a security risk at least to me.
Could you please give your valuable comments on this issue?
Tom Kyte
March 05, 2008 - 7:43 am UTC

... Password in this table are encrypted (MD5) and cannot be decrypted. ...

they are no encrypted, they are one way hashed. Things that are encrypted can be decrypted. One way hashes are not encryption.

...
Now we are going to change this structure. There will be single user (TCE) in database which will be shared by every application user.
....

why the heck would you do that? You want less accountability? Less security? Less of everything?

...
The password of oracle user (TCE) will be saved in database table and will be encrypted using DES and will be decrypted in login screen using dbms_crypto.decrypt.
......

ouch, that hurts.

....
The key to decrypt password will be taken from flat file, saved in application server. Our consultant says, as key to decrypt password is in file in application server so it is safe. No body can access this.
......

hahahahaha - do they work at the comedy club on the weekends? They must.


So, do you think I won't be able to see that in the SGA? Or on the network? Or if I, say, oh, log into your application server? The fact it is in a file is just the beginning - there are lots of other vunerabilities here.



Why wouldn't you use industry standard methods of identification? Like PKI or something???


You need to search for n-tier proxy authentication as well...



(but ask the consultant, so, why would we take the hassle of encrypting the password of TCE, and storing the key in the clear on the application server - why not just store the password of TCE on the application server - it would basically be the same)

Encryption/Decryption

omer, March 06, 2008 - 4:10 am UTC

Hi Tom,
Well said and thanks for your comments.
What are the benefits of new architecture?
<<
According to our consultant:
*data dictionary cleaning
*10k to 30k users can be deleted
*securing access by password encryption and securing key on application server
*flexibility by using central user management
*reducing maintenance cost by managing users in application itself
>>
And along this, they said, this authentication mechanism is inspired by oracle E-Business suite authentication mechanism.
Is it like that? I am sure, mechanism in E-suite would be not so cheap.
Just apart of this, what you think, if you have Mercedes and you go to BMW garage to fix the problem of your car ;)
Tom Kyte
March 06, 2008 - 8:23 am UTC

I did not know the dictionary got "dirty"

so what if the users can be dropped - SO WHAT?


The consultant is describing how to DE-secure something, not sure it.


Where is this centralized user managment????? I see know that you will have to WRITE YOUR OWN authorization module instead of use the existing one - is that a good thing????


Hah, reducing - that one is very very funny, "reducing cost" - by WRITING MORE CODE????


Oracle E-Business was written before the database had roles and other security mechanisms - way way long ago. And they spend thousands of hours working on that authorization stuff.

Tell me, how many hours do you spend now?

password encryption

ahd issa, March 02, 2009 - 5:49 am UTC

hi tom

could you please give me information about password encryptrion ( how ,where ,script....)

thanks alot
Tom Kyte
March 03, 2009 - 3:11 pm UTC

huh?

Oracle encrypts passwords and sends them over the network encrypted. You need do nothing.



Good learning on DBMS_OBFUSCATION_TOOLKIT Package

Jaspreet Nagra, July 27, 2009 - 4:41 pm UTC

Hi Tom,

I have one question based on DBMS_OBFUSCATION_TOOLKIT:
Can we find out whether the value stored in database field is in encrypted form or not?
Scenario: We encrypt data upon click of encrypt button from GUI. So we need to disable the button if the data is already in encrypted form.


Tom Kyte
July 27, 2009 - 8:40 pm UTC

read the other place you posted this same exact thing. I answered over there

Thanks

Jaspreet Nagra, July 28, 2009 - 12:39 pm UTC

Thanks ... I was not able to find my previous post so posted it again.

Well Thanks again for explanation.

ORA-28234

whizkid, December 08, 2009 - 9:57 am UTC

Hi Tom,

Have been breaking my head on this for hours together.. took this sample from the Oracle Documentation and modified key_bytes_raw := dbms_crypto.hash(src => utl_raw.cast_to_raw('passwordpasswordpasswordpassword'), typ => dbms_crypto.hash_sh1); but getting an error as above. Please help!

DECLARE
   input_string       VARCHAR2 (200) :=  'Secret Message';
   output_string      VARCHAR2 (200);
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   decrypted_raw      RAW (2000);             -- stores decrypted binary text
   num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   key_bytes_raw1     RAW (32);               -- stores 256-bit encryption key
   encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
   key_bytes_raw :=  dbms_crypto.hash(src => utl_raw.cast_to_raw('passwordpasswordpasswordpassword'), typ => dbms_crypto.hash_sh1);
   key_bytes_raw1 :=  DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
   DBMS_OUTPUT.PUT_LINE ( 'step2-raw: ' || key_bytes_raw);
   DBMS_OUTPUT.PUT_LINE ( 'step2-raw1: ' || key_bytes_raw1);
   encrypted_raw := DBMS_CRYPTO.ENCRYPT
      (
         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
         typ => encryption_type,
         key => key_bytes_raw
      );
    -- The encrypted value "encrypted_raw" can be used here
 DBMS_OUTPUT.PUT_LINE ( 'Encrypted string: ' || encrypted_raw);
   decrypted_raw := DBMS_CRYPTO.DECRYPT
      (
         src => encrypted_raw,
         typ => encryption_type,
         key => key_bytes_raw
      );
   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 
   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;

ORA-28234

whizkid, December 09, 2009 - 12:03 am UTC

Just realized that have posted something which does not have any info... Sorry for that.. here are the relevant details

DB Version: 10.2.0.3
Example is from the documentation http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_crypto.htm

Instead of using DBMS_CRYPTO.RANDOMBYTES as given in example as encryption key, want to generate my own key using a pass phrase. If I use key_bytes_raw1 while calling dbms_crypto, it works fine. Also noticed that my hash generates only 40 bytes of key whereas RANDOMBYTES generates 64bytes. How can I increase the keysize of has to 64 bit?

DWH_ADMIN@dwh > DECLARE
2 input_string VARCHAR2 (200) := 'Secret Message';
3 output_string VARCHAR2 (200);
4 encrypted_raw RAW (2000); -- stores encrypted binary text
5 decrypted_raw RAW (2000); -- stores decrypted binary text
6 num_key_bytes NUMBER := 256/8; -- key length 256 bits (32 bytes)
7 key_bytes_raw RAW (32); -- stores 256-bit encryption key
8 key_bytes_raw1 RAW (32); -- stores 256-bit encryption key
9 encryption_type PLS_INTEGER := -- total encryption type
10 DBMS_CRYPTO.ENCRYPT_AES256
11 + DBMS_CRYPTO.CHAIN_CBC
12 + DBMS_CRYPTO.PAD_PKCS5;
13 BEGIN
14 DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
15 key_bytes_raw := dbms_crypto.hash(src => utl_raw.cast_to_raw('passwordpasswordpasswordpassword'), typ => dbms_crypto.hash_sh1);--DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
16 key_bytes_raw1 := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
17 DBMS_OUTPUT.PUT_LINE ( 'step2-raw: ' || key_bytes_raw);
18 DBMS_OUTPUT.PUT_LINE ( 'step2-raw1: ' || key_bytes_raw1);
19 encrypted_raw := DBMS_CRYPTO.ENCRYPT
20 (
21 src => UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8'),
22 typ => encryption_type,
23 key => key_bytes_raw
24 );
25 -- The encrypted value "encrypted_raw" can be used here
26 DBMS_OUTPUT.PUT_LINE ( 'Encrypted string: ' || encrypted_raw);
27 decrypted_raw := DBMS_CRYPTO.DECRYPT
28 (
29 src => encrypted_raw,
30 typ => encryption_type,
31 key => key_bytes_raw
32 );
33 output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
34
35 DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
36 END;
37
38
39 /
DECLARE
*
ERROR at line 1:
ORA-28234: key length too short
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 10
ORA-06512: at line 19

Tom Kyte
December 10, 2009 - 1:15 pm UTC

as you have discovered - your key is simply "too short". It is 20 bytes in length (not 40, it is RAW remember - not a string, not hex, if you call length on it, it'll be converted to hex - you have to divide by two).

Your randombytes is not 64bytes, it is 32 (look at your definition of the variable!!!! how could it be 64bytes???!?!?).

then you slip in 64bit :(

anyway.

ops$tkyte%ORA10GR2> DECLARE
  2     input_string       VARCHAR2 (200) :=  'Secret Message';
  3     output_string      VARCHAR2 (200);
  4     encrypted_raw      RAW (2000);             -- stores encrypted binary text
  5     decrypted_raw      RAW (2000);             -- stores decrypted binary text
  6     num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
  7     key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
  8     encryption_type    PLS_INTEGER :=          -- total encryption type
  9                              DBMS_CRYPTO.ENCRYPT_AES256
 10                            + DBMS_CRYPTO.CHAIN_CBC
 11                            + DBMS_CRYPTO.PAD_PKCS5;
 12  BEGIN
 13     DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
 14     key_bytes_raw := UTL_I18N.STRING_TO_RAW ( 'passwordpasswordpasswordpassword' );
 15     encrypted_raw := DBMS_CRYPTO.ENCRYPT
 16        (
 17           src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
 18           typ => encryption_type,
 19           key => key_bytes_raw
 20        );
 21      -- The encrypted value "encrypted_raw" can be used here
 22   DBMS_OUTPUT.PUT_LINE ( 'Encrypted string: ' || encrypted_raw);
 23     decrypted_raw := DBMS_CRYPTO.DECRYPT
 24        (
 25           src => encrypted_raw,
 26           typ => encryption_type,
 27           key => key_bytes_raw
 28        );
 29     output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 30
 31     DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
 32  END;
 33  /
Original string: Secret Message
Encrypted string: E13D59102175696EFD2C1B0A7064FB47
Decrypted string: Secret Message

PL/SQL procedure successfully completed.

encryption

nicksa, March 16, 2010 - 12:59 pm UTC

Tom,
We are evaluating Protegrity Solution for encryption of our tables which contain creditcard nos as part of pci compliance project.
This solution requires
1. card number column NOT part of the primary key
2. if card number is indexed , then the index wont be used
and full table scan will occur. Because the column
will have been changed to varbinary.

To do above, Trouble is we will have to change the db structure (primary key) and then re write queries NOT to use card number in where clause. This means talking to our apps vendor for changes etc.

I was wondering if there was a built in solution from oracle?

I am thinking of the following in theory, you can help me if it is possible in oracle and how?{keeping the card number as primary key)

Suppose we have a table called "cards" which has "card_number" as the column we like to encrypt.

one time steps (during down time)
1. copy the "cards" table to cards_temp
2. truncate cards table
3. rename the cards table to cards_encrypted (so all
constraints and keys in tact for example card_number as
p-key or indexed column)
4. alter the table to change card_number from varchar2 to
varbinary(if required)
5. insert into cards_encrypted select c1,c2, oracle_func(card_number), c3 from cards_temp;

Question: Is there a built in function from oracle for oracle_func(card_number) above to encrypte the data???

Now that the table cards_encrypted is having encrypted data we can delete the cards_temp table.

6. create a view with same orignale table "cards" as
select c1,c2, oracle_decryptfunc(card_number), c3 from cards_encrypted
The above should settle for any select statements in the application without changing any code.
7. create insert, delete, update trigger to intercept the application dmls to handle the card_number encryption for all dmls.

Tom, can you please simulate the above with oracle example if this functuioinality exists in oracle database ??

lastly if there is availablem, what will happen if some one got hold of our db backup??

cheers

Tom Kyte
March 16, 2010 - 2:35 pm UTC

you have credit card numbers as primary keys?????? You have a serious design flaw I would think. I change my credit card numbers on websites at the drop of a hat.

Are you also evaluating transparent data encryption from Oracle - in that case, we can encrypt the tablespace containing the table with credit card numbers AND the index on credit card numbers - and you would not have to change a single thing. That is 11g.


If you use 10g, you can use transparent data encryption from Oracle to encrypt the column - but you won't be able to have a foreign key. The index will work IF you use "=" (range scans won't be useful since the index data is encrypted - and hence not sorted).


See transparent data encryption, column level was added in 10gR2, tablespace in 11g Release 1. tablespace encryption would let you do this without touching anything - column level encryption might meet your needs.

http://docs.oracle.com/docs/cd/E11882_01/network.112/e10746/asotrans.htm#ASOAG9566


If you use that and someone steals your data files, they would also need to steal your wallet with your encryption keys (stored externally from the database) and kidnap your DBA as they have the password to release the wallet to the database to enable the decryption to occur.


well...

nicksa, March 16, 2010 - 5:13 pm UTC

I used tde but the data of the columns of the table is encrypted on disk.
The PIC project requirement is that any user/application who is not "authorised" when does "select card_no from cards" should see the data as mask or null. So the table view within oracle database is encrypted unless the "authorized user" will run the query to view.

So the tde what you told me only encrypt the corresponding block on the data files.

Cheers

Tom Kyte
March 16, 2010 - 5:18 pm UTC

... The PIC project requirement is that any user/application who is not
"authorised" when does "select card_no from cards" should see the data as mask
or null. ...

that is grant ACCESS CONTROL - that is easily accomplished with dbms_rls and column masking.


encryption protects data at rest on disk, or data in motion on a network.

encryption is not and does not protect data from access, if you are allowed access to the data - you are allowed access to the data - you do not use encryption to enforce that, you use access controls.

And column level access control (part of VPD) does that just dandy - you have that feature as part of enterprise edition right now, already.

one more time

nicksa, March 17, 2010 - 1:09 am UTC

Thanks for all your valuable feedback.
I made a typo. The project is called PCI compliance
http://www.oracle.com/technology/deploy/security/database-security/oracle-pci.html

PCI has difference levels. For us the concerned audit dept requirement is "card number data should not be stored as ascii in the database tables so that it can be viewed by simple select"
For example even if the user(s), apps processes have access to the tables but the requirement is a)card no stored as encrypted (not ascii) and b) those who have select access on the table should still see the card no as masked.

You presented me with 2 concepts. can you refer links for these so i can understand and test them.

1.that is grant ACCESS CONTROL - that is easily accomplished with dbms_rls and column masking.

2.And column level access control (part of VPD) does that just dandy - you have that feature as part of enterprise edition right now, already.


In my view what they wanted from me is

a. encrypt the card no column in the table
b. users who need to see the card no column should use some decrypt function else they see masked
c. no app code change should be done

Tom Kyte
March 17, 2010 - 8:38 am UTC

the requirement is satisfied, as I wrote, by

a) column/tablespace encryption for data at rest
b) fine grained access control for masking.


done.


I see ONE concept in your numbered list. It is VPD (virtual private database), ACCESS control via VPD, dbms_rls (package used to implement VPD), column masking - also known as column level access control (part of VPD)....

Here I searched for "column masking" in the documentation to see what might turn up....

http://www.oracle.com/pls/db102/search?remark=quick_search&word=vpd&tab_id=&format=ranked



Your view is incorrect in my view. There is NO requirement to use some decrypt function explicitly. Tell me this, if you invoke a decrypt function - you need a key, please tell me WHERE does this key come from ??? And further more, if you have the key to decrypt row 1 - don't you already have the key to decrypt row 2, 3, .... and so on (eg: just because data is encrypted DOES NOT PROTECT IT, you need some real key management going on and that stuff - that stuff is hard).


What they want from you is:

a) encrypt data at rest (on disk) to protect from theft of backups, datafiles, etc (transparent data encryption)

b) encrypt data in motion (on the network) to protect from network sniffing, packet stealing (network encryption, sqlnet)

c) secure data such that only authorized users can see data values - row level access control via VPD can restrict access down to individual rows (I can see row 1, you cannot, you can see row 2, I cannot and so on). column level access control via VPD can mask columns - row by row if you want - so that I can only see columns on certain rows. That is, I can see row 3, you can see row 3 - but I can see the credit card number on row 3 - you are not allowed.

d) (a) ensures that if someone steals my database and turns OFF (b) and (c) - it is still perfectly OK, since they cannot decrypt the data in the first place.



That is what they are asking for, something secure, not something "not implementable"


thanks

nicksa, March 17, 2010 - 10:51 am UTC

Thanks again for your convincing review.
Wish me luck to present to the project team that they should not invest hundreds of thousands of $$ on third party s/w like protegrity while the requirements can be satisfied with column level protection/masking.

Just for my own further understanding
1. why it is a bad design for some vendor to choose card no as part of p-key?

2. Pardon me but I beg to differ that I do not think the concept in PCI is to restrict the access to the whole row for some users/apps. So row level restriction was not a requirement.
During their presentation they(pci team consultants) kept on insisting that the column data for card nos in our databases are stored as ascii , why ? because the dbas and every user who has access to the tables can see the column as ascii. They say that all these users are allowed to see other columns of same row but not the card no AND ONLY one user who is logged from the application user which acutally does the insert/delete/update and select are required to see card no and that too with a encrypt/decrypt function.
We then asked them what about reports which prints the card no for sending to customer. Then the reply was that user who prints the reports can also use decrtpt function.
They did not even talk about storage level encryption.

Anyway I will discuss further with them in light of your view point.
Cheers

PS: I clicked on ur link but it shows me lot of other links
Can you please point me to VPD notes specifically for masking the column only. I want the column to be masked for all users in the database (including sys, system) except the apps user.

cheers


Tom Kyte
March 18, 2010 - 6:56 am UTC

1) because primary keys should NEVER change in value and they uniquely identify a thing - credit card numbers come and go - frequently. To use credit card number as a primary key for someone is like using their address.

Multiple people have the same card number, like addresses.
People change their credit card numbers, like addresses.


2) What are you differing with, I was explaining what this feature can do - VPD. It can restrict access of data to the row and column level - going far beyond your needs.

Your PCI consultants have one implementation they know, and they will sell and they will be glad to implement for you at cost.

Ask them one thing, how do they do key management - and then start poking the 5 million holes that will be in their implementation.



(or course a search link takes you to lots of links, ordered by relevance. I was trying to teach you to fish, rather than giving you a bite of flounder. In short, I was trying to make a painful point - you don't need to be spoon fed this stuff, if someone says "VPD" and you don't know what VPD is, why isn't your first gut reaction to search for VPD in the documentation??? to read about it??? Did you see the link on the first page of results entitled:

Adding Policies for Column-Level VPD


You might consider starting there and seeing what else that links to.

ok here it is

Nicksa, March 19, 2010 - 3:26 pm UTC

Ok I concur with your reasoning for pointing me to hit the docs and read.
I have now managed to test and implement the column level vpd. But i like to share to following test case. Please reply as per the points below:

I have a table as shown below:
SQL> connect oe/oe@shdp
Connected.
SQL> desc cards
 Name                     Null?    Type
 ---------------------
 CUSTOMER_ID               NUMBER(6)
 CUST_LAST_NAME            NOT NULL VARCHAR2(20)
 DATE_OF_BIRTH             DATE
 CARD_NUMBER               NUMBER

I wanted to protect the card_number in such a way that only user called oe can see its values and rest can not.

I logged in as system user to my database and performed the following tasks:
SQL> connect system/oracle@shdp
Connected.
SQL> ed
Wrote file afiedt.buf

  1  SQL> desc users
  2   Name       Null?    Type
  3   ----------------- --------------
  4   USERNAME         VARCHAR2(100)
  5*  GRANTS           CHAR(1)

I then inserted into the above table values ('oe',1);

The while remained as system user i created the following policy:
CREATE OR REPLACE FUNCTION pf_job (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
  con VARCHAR2 (200) default '1=2';
  v_user          varchar2(100);
  V_yes number :=0;
BEGIN
  v_user := lower(sys_context('userenv','session_user'));
  SELECT COUNT(*) into v_yes FROM system.USERS WHERE lower(USERNAME) = v_user and grants=1;
  if v_yes = 1 then
    con := '1=1';
  end if;
  RETURN (con);
END pf_job;
/

Then I logged as oe user and ran select * from cards;
I was able to see the card_number values.

Then I logged as other database users who have select access to the table oe.cards. When I ran the select * from oe.cards. I was see all rows with card_number column as empty i.e. spaces- Pretty cool!!!.

Questions
1. When I connect as sys, the policy does not work and I still see card_number values ?????? why, the policy should only work for oe users and none else, so even dbas etc would not see the values. how to do that????
The issue is with "sys" as system can't see the values.

2. I made the card_number as the p-key and still the vpd column level works, so I reckon the primary key restriction does not allow here. yes i got your point of not having the pkey as card number.

3. I dont want spaces to come for those users who have not access. I want say ******123 (last 3 digits appear) how to do that??
4. I fully understand your point of em third party vendors asking for money and taking for a ride when oracle already provided implicit solution. So is it possible to make the card no appear not as a)null and not even b)masked as *** but appear with some random numbers sort of looks to them that i have actually encrypted the no. For example the actual card no is passed to a random no or some function which generates always unique no which then will be shown to those who do not have access.
5. i know encryption at disk level is part of advanced encryption option and is licensed. But is vpd column level also requires extra license?

Conclusion:
If i cleared above issues (including securing sys) then i could implement column level vpd plus encryption as disk level and there you go.











Tom Kyte
March 20, 2010 - 9:13 am UTC

1) sys is "super user", very much like root. sys is exempt from VPD. If you want to lock down things. Restrict your access to sys - lock it down. NO ONE NEEDS IT DAY TO DAY - not even the most trustest DBA's.

sys is special
sys is magic
sys should not be used unless and until it has to be.

2) the restriction on card number as a primary key comes into play with COLUMN LEVEL ENCRYPTION only . You cannot have a foreign key to it, and you cannot index range scan on an index on card number alone.

3) it is not spaces, it is NULL - as in 'unknown'. If you want what you say you want, you'll either

a) store card number in two fields - a protected field and a non-protected field.
b) start card number as it is, but maintain another unprotected field that mirrors the last digitis.


4) and you'll still have to integrate all of that trivial functionality (come on, doing #3 is rather trivial - you integrating that with your existing application will not be).

5) vpd is part of enterprise edition.



To secure sys (they cannot secure sys, sys will be able to get around anything a 3rd party puts in place in seconds), you secure the SYS account as in restrict access to it. You don't need it to start up, to shutdown, you don't need to use it for 99.999999% of what people use it for. Stop using it.

last bit

nicksa, March 20, 2010 - 10:18 am UTC

You said
"Are you also evaluating transparent data encryption from Oracle - in that case, we can encrypt the tablespace containing the table with credit card numbers AND the index on credit card numbers - and you would not have to change a single thing. That is 11g."

When you said That is 11g, what do you mean? You mean that in 10g, we have to do encryption at individual level(column etc) and in 11g can be done whole tablespace and also no f-keys restriction right??
And this feature of 11g ? does it part of enterprise or need license???

Ofcourse I presume the above encryption is ONLY at the tablspace level for os files . We still need VPD for access at logical database level for users.

PS : Many thanks for your help. The heat is one :-) and am winning and all thanks to you.
Tom Kyte
March 20, 2010 - 10:24 am UTC

10g has column level encryption only.

11g (been out for three years now) has tablespace AND column level encryption.

tablespace level encryption has no limits on constraints, index range scans, function based indexes. It is, like column level encryption, part of the advanced security option.

Encryption - as I've said time and time again, regardless of the provider of said encryption - is useful only for data at rest (on disk) or in motion (on the network). Encryption in itself does not provide any sort of, form of, implementation of ACCESS CONTROL. You do not use encryption for access control, you use ACCESS CONTROL for access control. Anyone that tells you otherwise is "not accurate".

fga

nicksa, March 23, 2010 - 9:10 am UTC

I think its a level of maturity that one needs to understand these concepts and i was not aware of the concepts until you cleared them for me.
I have been able to make my management understand the position of oracle in terms of PCI compliance.
Since we are not on 11g, we are going to apply the available encryption (with the limitation ) on 10g and where it is not possible (for example we do have f-keys as card no), we will resort to compensated controls.
There is a question in terms of access level set up column level vpd.
In the DBMS_RLS.ADD_POLICY the referenced function which I used in my example will prevent all users from seeing the card number except the schema owner and the sys.
The schema owner is actually used from apps server for the dml operations which is fine.
I have been asked to make sure that schema owner user id should only be associated it is logged in from certain program names(module name) plus certain apps servers. So even if some one knows the schema owner, he cant use it to log to toad etc.
Now I can include this logic in the policy function by checking the program name checking from v$session for the session. But would this cause slowness in the performance. Because every dml to that table will go thru this check.
Also people can rename toad.exe to the application name.exe and get away. The only way is restorting the schema user id coming from certain workstations but the devlopment say that that is too wide, as users can use any workstation wher the apps is installed to connect. Yes the application needs to be installed which stores internally the schema passwrd (but again i can rename sqlplus.exe and get away).
any ideas??



Tom Kyte
March 23, 2010 - 12:43 pm UTC

... The schema owner is actually used from apps server for the dml operations which
is fine.
...

so, can you whitelist the IP addresses, that way you can just filter based on the schema name and the IP address it comes from - not the right set of IP's and no joy. It only needs be done once per session creation, not for every DML.

nicksa, March 24, 2010 - 1:51 am UTC

well the users workstations are using dhcp server so ip addresses wont work. Its the same as I can restrict from workstation name (machine) as well. I can ask them to provide me a authorised list of workstations users will be using for the application.
But what would stop a user using toad and other tools from same workstation/ip to connect via that tool using schema owner.
cheers

Tom Kyte
March 26, 2010 - 10:46 am UTC

Umm

... The schema owner is actually used from apps server for the dml operations which
is fine.
...

reconcile that with your statement about end user workstations please??? Who cares about the end users, you are talking about a connection originating from an application - which presumably you actually configure, install, setup and run?



ok..

nicksa, March 27, 2010 - 1:50 am UTC

User's workstations ... well not typical end users but back office people like there is a team who has people to check on credit card authorization , aml operations, etc. These people site and have the vendor based application modules (different modules) installed on their pcs and the head is saying to me that he can not provide me specific machine names because it can change, a pc can go bad and he may get a diff pc name and he would just like to install the app and thats it.
All i see on my database are .exe programs coming from these work stations. All these programs use internally the apps schema owner and pwd.
bad design but thats what i have.

cheers
Tom Kyte
March 27, 2010 - 8:31 am UTC

so basically, you could just get the username and password from these applications, not very secure (they must have them hard coded... you probably cannot even change them in the database :( )

you can use the information in the v$ tables, not 100% perfect - it can be faked - but it should be OK

explain

nicksa, March 28, 2010 - 3:29 am UTC

Can you explain
"you can use the information in the v$ tables, not 100% perfect - it can be faked - but it should be OK
"
As I need to find a way...
Tom Kyte
April 05, 2010 - 9:10 am UTC

what is ambiguous about that?

You told us already yourself that the information in the v$ tables is not 100% perfect (see your original posting up there, YOU told us this - I don't see what is ambiguous here).


question on ecryption

A reader, May 19, 2010 - 8:58 am UTC

Hi Tom

I have read the following in PL/SQL supplied packages guide (10gR2).
What they are trying to say here, do you have an exmaple on this. How can I encrypt the code which is doing encryption ?

"Oracle recommends using the wrap utility of PL/SQL to obfuscate the code within a PL/SQL package itself that does the encryption. That prevents people from breaking the encryption by looking at the PL/SQL code that handles keys, calls encrypting routines, and so on. In other words, use the wrap utility to obfuscate the PL/SQL packages themselves. This scheme is secure enough to prevent users with SELECT access to EMP from reading unencrypted sensitive data, and a DBA from easily retrieving encryption keys and using them to decrypt data in the EMP table. It can be made more secure by changing encryption keys regularly, or having a better key storage algorithm (so the keys themselves are encrypted, for example)."



Tom Kyte
May 24, 2010 - 10:42 am UTC

$wrap iname=input_file_name.sql oname=output_file_name.plb

SQL> @output_file_name.plb



wrap is documented - did you search for it in the documentation?

http://www.oracle.com/pls/db112/search?remark=quick_search&word=wrap

Reader

A reader, August 24, 2010 - 8:32 am UTC

I am in 11gR2 Orale on Linux

I want to use one column encryption using the steps in the link

http://decipherinfosys.wordpress.com/2009/03/01/encrypting-a-single-column-in-oracle/

1. Create column as "col_name varchar2 encrypt"
2. Create wallet

Do I need special licensing purchased to use this functionality

Thanks
Tom Kyte
August 26, 2010 - 10:04 am UTC

You need the Advanced Security Option (ASO) to employ column or tablespace level encryption.

Security Question

Parag J Patankar, January 21, 2011 - 3:51 am UTC

Hi,

There is oracle 10g database belongs to country a is in datacentre country b.

User from country a access database in country b using only frontend e.g. Business Objects.

Now country a, wants to put rule where some columns of few tables should not be displayed. So any user/dba from country b should not able to see data from SQL. But user from country a should able to see data from frontend.

What is a best way to implement this soultion. Pl suggest ( where wallet, dbms_obfuscation ..etc )

thanks & regards
Parag J Patankar




Tom Kyte
January 24, 2011 - 7:11 am UTC

stop thinking encryption - encryption is not about data access control. encryption does one thing: it protects your data in the event of theft of that data, if someone steals your database. Otherwise, encryption isn't useful for access control.


Now, for the users - that is easy - dbms_rls, fine-grained access control, also known as virtual private database. it can be used to mask (hide) columns. You would set up a rule that would tell the database which columns are visible to which users under which circumstances. Fully documented.


for the DBA's it gets trickier. Dba's need to be able to do things like "backup" and "recover". Dba's need to see all of the data (else they cannot backup, they cannot recover, they cannot in general do their jobs).

Tell me - is there a DBA in country A? Or all of the dba's in country B? If there is a DBA in country A - then that DBA would be the dba and there would be no privileged DBA's in country B (you would not grant anyone in country B "dba" for that database - you would use the OS groups to ensure no one in country B could become a DBA for that database).

If there isn't a DBA in country A, then some dba in country B will need to be able to "see" this data - they have to, in order to manage it.

Encryption won't help you - because you still have to manage keys to access the data. Key management is really fairly difficult. You would have to manage the keys in country A somewhere (and keep them secure, private - but never ever lose them). You would have to do the encryption/ decryption in country A (if you did it in the database using dbms_obfuscation - which will be slow and cumbersome at best) - any DBA with competence would be able to get the keys if you did the decryption in the database.

data security

A reader, August 03, 2011 - 8:48 am UTC

Dear Tom,

We have a Hospital management system. I have a definite requirement from my client that they want to keep some data like (salary of employees,name,location in some tables) and also various patient's data secure, even from a DBA. Now I have to design the security system in that database (running in 10.2.0.4 version in Linux server). My choices are:

1) I will encrypt those columns with some Hexadecimal values while insertion/update and decrypt for vieweing purpose. What do you suggest I should go with? May be the decrypt feature will use a password which only the organizational Admin has the access.
2) I heard about TDE or Transparent data encryption. I heard this is a type of column hiding until and unless a password is provided to view that column. But as a "patient data" can change regularly, how can I hiding and unhiding be used in those important columns? What to do here?

What would be the best secured choice? or you can even suggest any other choice of your own?
Tom Kyte
August 03, 2011 - 9:51 am UTC

1) encryption is about protection of data at rest, protection of data from being stolen.


Tell me, how would you manage the encryption keys? How would your application get access to this information when it needs it? How would the key be released to your application?

Your application would need this key so it could retrieve the data and display it to the right people. It would need this key so it could create new data in the database.

So, tell me about this key management - how would you do it. Bear in mind, that no matter what approach you come up with - we'll rip it apart and show you that it does not work.


2) again, encryption is about protecting the data from theft. encryption is NOT about access control.

If you have a user that has sysdba - they'll be able to see *anything*. Therefore, limit the number of users with sysdba. That will protect the sensitive data.

If you have a user that has DBA and you are not using the database vault
http://www.oracle.com/us/products/database/options/database-vault/index.html
then they can see anything. Therefore, limit, lock down, restrict the use of 'DBA' to only those very few that actually need it - perhaps only for the times they need it.


You cannot hide everything from a sysdba - there will be those individuals that must be vetted to permit them to do their job (backup, recovery, etc).

Encrypting the data won't work. If you think it will, spec it out for us so we can tell you where it all falls apart.


data security

A reader, August 03, 2011 - 11:53 am UTC

Dear Tom,

Thanks for the response. But we are using 10.2.0.4 and I guess database vault comes with 10.2.0.5 version.
We have a schema which owns those important tables and that schema and few other schema has DBA role privileged. I will definitely revoke that privilege.
But please tell me isn't there any such way by which no one could actually see the correct data of a table?
Tom Kyte
August 03, 2011 - 1:25 pm UTC

But please tell me isn't there any such way by which no one could actually see the correct data of a table?


what use would that be ??

just drop the table I guess.

Authorized users see the data. A DBA is an authorized user.

Data vault existed with 10.2.0.4 - but in any case, 10.2.0.5 supercedes 10.2.0.4 - that shouldn't be a consideration.

Differences in Storing Encyrpted Data in Varchar2 and RAW

A reader, September 16, 2011 - 11:30 am UTC

Hi,
We used your functions given in this link i.e Function Crypt and Decrypt. I have not re pasted it for the sake of neatness.

Though the function handles the data with utl_raw.cast_to_raw is it advisable to store the encrypted data in a Varchar2 column (in table) or go only with raw column for storing encrypted data to avoid errors after character set is changed. Please confirm on this.

Thanks
Tom Kyte
September 16, 2011 - 2:18 pm UTC

is it advisable
to store the encrypted data in a Varchar2 column (in table) or go only with raw
column for storing encrypted data to avoid errors after character set is
changed. Please confirm on this.


the only way you can store encrypted data in a varchar2 column would be to convert the raw data into a string of hex characters - doubling the length of the data.

So, you can use a raw(16) OR a varchar2(32) - if you use a raw, you just bind and insert your raw data. If you use a varchar2 - you would insert the raw data with a call to RAWTOHEX to convert it into a string that is double the size.


Encryption

Parag J Patankar, October 10, 2011 - 2:53 am UTC

Hi Tom,

I am having strange request that user/developer want a script to start and stop a databases whenever he wants in aix 5.3 and above. Databases are Oracle 9.2 onwards.

One of the things, I should take care that other than stop and start databases they should not do any other activity. One way I can think of is write a shell script with runtime parameter and do complete encryption.

Do you suggest any other way ? so I can give only rights of start and stop database.

thanks & regards
Parag J Patankar


Tom Kyte
October 10, 2011 - 10:47 am UTC

grant that user the SYSOPER role.

They will be able to log in with their own credentials:

connect user/password AS SYSOPER

they'll be logged in with public privileges only at that point AND the ability to startup/shutdown the database - and very little else

that is what sysoper is for...

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm#10795

identified externally

Parag J Patankar, October 11, 2011 - 6:32 am UTC

Hi Tom,

Thanks for reply to my question above. Can I stopped user from "CREATE SPFILE" once I given SYSOPER rights

pl suggest

thanks & regards
PJP

Tom Kyte
October 11, 2011 - 8:13 am UTC

no, sysoper includes that privilege.

Encryption

Rahul S. Joijode, October 13, 2011 - 7:07 am UTC

Hi Tom,
Thanks for your suggestion.

As mentioned in previous review, we have 9i/10g/11g databases. As per your suggestion, we have created a new user so as to only grant rights to start and stop database.

However, to the new user created, when we attempt to grant the sysoper privilege, it fails with foll. error;

16:41:11 SYS:IVI6WDD0 > grant sysoper to admin;
grant sysoper to admin
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

When we check our database parameters, we have;

16:41:21 SYS:IVI6WDD0 > show parameter remote

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string NONE
remote_os_authent boolean TRUE
remote_os_roles boolean FALSE

The remote_login_passwordfile parameter is set to none in our environment, because we have developers connecting remotely to the db using sqlplus /. Thus, the remote_os_authent parameter has also been set to true.

So, please suggest whether the parameter remote_login_passwordfile should be set to 'exclusive' only to grant sysoper privilege to the user, or is there any other alternative we can still grant the sysoper privilege to the user keeping the parameter to 'none'.


Thanks,
Rahul S. Joijode


Tom Kyte
October 13, 2011 - 7:50 am UTC

remote_os_authent = true!!!!

you would want to set that off really. Not the most secure approach. It is deprecated in 11g by the way.


You would want to create a password file (orapwd) and enable it to allow for remote sysoper connections.

Oracle not safe.

Elboghdady, March 15, 2012 - 6:25 pm UTC

Just in one click, we can view the wrap code. It’s very simple huh? So, my conclusion is the wrap package feature in oracle is not safe.
http://leosendra.wordpress.com/2010/05/07/how-to-wrap-and-unwrap-package-oracle-10g/

CHECKSUM equvalent function

Ravi B, April 16, 2012 - 12:48 pm UTC

Hi Tom,

I am converting some SQLSERVER queries to Oracle.
Is there any equivalent function in ORACLE for CHECKSUM in sqlserver?

CHECKSUM ( * | expression [ ,...n ] )
RETURNS integer.

Thanks,
Ravi


Tom Kyte
April 16, 2012 - 4:12 pm UTC

owa_opt_lock.checksum



CHECKSUM

Ravi B, April 17, 2012 - 2:15 pm UTC

Thanks! That is exactly what i was looking for.

Implementing original request in 10g & 11g

Doug, May 15, 2012 - 10:51 am UTC

How would something similar to the original request be best accomplished in 10g and 11g? I have a package that is sent to clients on various OS platforms running either 10g or 11g and it produces data output to a spool file using dbms_output. They then upload the resulting text file to a website for analysis and review. I am looking for a way to encrypt or somehow mask the output being generated to the spool file and then have a way to unencrypt it when it arrives to the webserver. Is the dbms_crypto package the way to accomplish this or another method? Would we need Oracle installed on the web server side to unencrypt the file or would any encryption utility work as long as the key was known?
Tom Kyte
May 15, 2012 - 1:58 pm UTC

You'll want to use some encryption out of the database - not dbms_crypto - if you ask me.

Else you'll need a database on the app server to decrypt with.


Follow-up

Sachin, February 18, 2014 - 3:31 pm UTC

Tom, great knowlegde bank. with the help of your suggestion i am able to encrypt and decrypt the data except:
1. when the input data contains the european language characters.
ex. Távközlési Kft even though rpad will make it multiples of 8, it's failing at the encrytion mentod. you might well know the reason for this but i am nowhere near to get a solution for this as to how shall i make this a multiple of 8. i have used exactly the same procedue that you provided as examples.

could you please help me how can i handle these strings ?

solved it tom

sachin, February 19, 2014 - 5:16 am UTC

Hi Tom, I manage to solve the problem. They key was to use the byte functions. Here is more how you can handle the multilingual encryption for the benifit for community.

l_data := rpad( p_str, (TRUNC(LENGTHB(p_str)/8)+1)*8, chr(0) );
if(LENGTHB(l_data) MOD 8 >0)
then
l_data:=SUBSTRB(l_data,0,lengthb(l_data)-(lengthb(l_data) MOD 8));
end if;

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library