Thursday, April 17, 2008

DBMS_CRYPTO: Example


Updated 03/08/2010
Original source can be found here.

Based on comments, specifically from caringaboutsecurity down below, I've (finally) updated the source code.

Another point, he is correct that there is no key used in this code. Salt is used, but no key. A key would need to be passed in along with the SSN and stored in some location. This code is not concerned with the overall security aspects, just to encrypt the value. I am not qualified to speak towards the over-arching domain of security.

The package specification is also provided in this updated version.

In the comments from my previous post, Tyler and Tom both mentioned the fact that you should never store sensitive data as plain-text in the database. I used a bad example unfortunately, but Tom did mention the DBMS_CRYPTO package which I have used before.

Since I have used it and published a working example to the OTN forums, I figured I'd put it here as well.

The goal is the encrypt a SSN and store the encrypted string (RAW) in the database. In conjunction with VPD, you should be able to easily control who can see the plain-text version of the SSN. To do that you will need to create another function which decrypts the encrypted SSN.

This is a pretty basic example, but it should help to get you started. You can read more about the DBMS_CRYPTO package in the docs.

Specification
CREATE OR REPLACE
PACKAGE p_encrypt
AS
FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW;
FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2;
END p_encrypt;
/
show errors
Body
CREATE OR REPLACE
PACKAGE BODY p_encrypt
AS
--DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
--IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
--THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
G_KEY RAW(250) := utl_i18n.string_to_raw
( data => G_STRING,
dst_charset => G_CHARACTER_SET );
G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
+ dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;

FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
IS
l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, G_CHARACTER_SET );
l_encrypted RAW(32);
BEGIN
l_ssn := utl_i18n.string_to_raw
( data => p_ssn,
dst_charset => G_CHARACTER_SET );

l_encrypted := dbms_crypto.encrypt
( src => l_ssn,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );

RETURN l_encrypted;
END encrypt_ssn;

FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
IS
l_decrypted RAW(32);
l_decrypted_string VARCHAR2(32);
BEGIN
l_decrypted := dbms_crypto.decrypt
( src => p_ssn,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );

l_decrypted_string := utl_i18n.raw_to_char
( data => l_decrypted,
src_charset => G_CHARACTER_SET );
RETURN l_decrypted_string;
END decrypt_ssn;

END p_encrypt;
/
show errors
I mention it in the comments of the code, but do not forget to wrap your PL/SQL before you load it, otherwise someone will be able to easily see your salt/key.

8 comments:

Anonymous said...

Hi,

There's a user who is maintaining password information in a spreadsheet. We are going to create a table to store this information. We would like to protect the password column by using encryption.

I was looking over your code and I'm very new to PL/SQL. I do not see any reference to a table for which this code is being used.

Eventually, we're going to use APEX so she can maintain this table. After she does her entries and clicks Submit, we need the password to be encrypted. Would you know if this code can be placed within APEX?

Am I missing something?
Can you help me out here?

Unknown said...

"String literals, number literals and names of variables, tables and columns remain in plain text within the wrapped file. Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret."

oraclenerd said...

I had read that too, but I wrapped it anyways to make sure. None of what you mention is visible to the naked eye.

Anonymous said...

You write, "You will need to create another function which decrypts the encrypted SSN."

However you DO provided that function.


hbradshaw: There is indeed no reference to any tables. Oracle's Transparent Data Encryption might be better for you: http://download.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm (see section 3.1.2 - When to Use Transparent Data Encryption)

I see several reasons this strategy won't work.

1)There's what akaDruid quoted.
2) The encrypted data is subject to an easy-to-launch rainbow table-base dictionary attack, as there are only a billion possible SSNs (10^9, or about 2^30) (and even less than that since info on how SSNs are constructed is public, but who cares, as 2^30 is such a small number. You mention salt/key, but AFAICT there is no salt.

3)DES is no longer recommended by the National Institute of Standards and Technology (NIST).

oraclenerd said...

@caringaboutsecurity

I did provide the decrypt function...my cut and paste outwitted my typing.

1) in 11g, plain text IS NOT visible after the code has been wrapped. However, you could probably crack that wrapped code if you were so inclined.
2) You have to store the SSN somewhere. With good access control and other means, you should be able to prevent the ability to crack these encrypted SSNs. However, if someone does gain access, like you said, it's just a matter of time.

BTW, this is the salt, though I called it a key: G_KEY RAW(32) := UTL_I18N.STRING_TO_RAW( 'some_random_string_stuff_goes_here', 'AL32UTF8' );

3) I did not know that. Thanks for pointing that out. I'll have to research it and provide a better example.

Thanks

chet

Unknown said...

This is actually a pretty interesting thread, I've bookmarked it for future reference

Anonymous said...

Chet,

You say

BTW, this is the salt, though I called it a key: G_KEY RAW(32) := UTL_I18N.STRING_TO_RAW( 'some_random_string_stuff_goes_here', 'AL32UTF8' );

I don't think so. It's not salt if it's the same value for every encrypted data block. Different salt values are to be used for different data blocks by the key derivation function.

As you can see from my blog, I have a keen interest in PII stored in databases.

-Matthew Elvey

oraclenerd said...

@matthew

OK, so I was in a hurry. Sorry about that.

I won't post anymore "facts" until I do a bit more research. :)

I do appreciate that security/encryption is not as cut and dried as I portrayed it.

I had a friend give a demo showing us the difference between certain encryption algorithms (the one where the picture is only slightly blurred). So I should no better.

I appreciate the link. I'll try to be more thoughtful in the future...perhaps you should do a guest post? ;)

chet