learn-oracle

Learn Oracle



You are just 10 steps away from becoming a Oracle DBA. Materialize your dream by following the The 10 Simple Steps .

Encryption and DBMS_OBFUSCATION_TOOLKIT

Topics covered in this article are :

Topics covered in this article are :

What Encryption Standards are Supported?
The DBMS_OBSFUGATION_TOOLKIT package
An Example Demonstration
How secure is DES though?
There are times when data in your system is highly sensitive and it is required that the data is encrypted. Oracle supplies a package called DBMS_OBFUSCATION_TOOLKIT, which allows us to encrypt and decrypt data by applying one of two encryption algorithms and an encryption key. This article should be of interest to both the DBA and developer, especially when designing a new schema where some data is seen to be particularly sensitive.


Usually security in Oracle is managed through granting privileges on a table and allowing this mechanism to regulate who may see what. Additionally there is the new Fine Grained Access Control (FGAC) that can limit access to only the rows that a user is authorised to see.



This is fine for most situations but there are occasions where it would be nice to really tie the data down so that if a casual browser did get to the rows within a table, then the data is unreadable or meaningless in its encrypted format. This is where encryption comes in. Typically encryption is likely to be used for small pieces of data rather than database wide or table-wide. For example, imagine a system that has Internet access to the database; wouldn’t it be nice to encrypt those creditcard details? The number of instances in the national press of creditcard numbers being found unencrypted on the web should tell us that encryption is a good idea. Also, there is data in medical systems that would be good to encrypt – simple things such as parenthood and patient diseases should be encrypted; the fact that someone has diseases such as HIV or an STD, is particularly sensitive and shouldn’t be available for the casual perusal of others.

What Encryption Standards are Supported?

The DBMS_OBFUSCATION_TOOLKIT package supports just two encryption standards; These being DES and Triple DES – DES being an acronym for Data Encryption Standard. Before we dive into the detail, a little history may be in order; The American National Institute of Standards and Technology (NIST) highlighted the need for a secure encryption standard in 1972[1]. In 1974 IBM submitted the ‘Lucifer’ algorithm and the National Security Agency reduced the required key length of Lucifer from 128 to just 56 bits. In November 1976 the Lucifer algorithm became a US federal standard and was renamed to the ‘Data Encryption Standard (DES)’. Ever since then, DES has been a widely accepted and used algorithm for encryption. In essence then DES takes a 64-bit block of plain text, and returns a 64-bit block of encrypted text. Further to this the DES algorithm allows the opposite – supply the key and the encrypted text and Oracle DBA get the plain-text back out again. Although the input key for DES is 64 bits long, the actual key used by DES is only 56 bits in length. This is because the least significant bit is used for parity. The other standard that the DBMS_OBFUSCATION_TOOLKIT package supports is Triple DES. This standard is based upon a 128 bit key rather than just the 56 bit in plain DES. As a result Triple DES is much more secure.

The DBMS_OBSFUGATION_TOOLKIT package

I want to quickly cover the program units within the package and then move onto some simple example code to show how this is used. Within the package there are really just 4 program-units in which we are interested. Each of these is overloaded so that there is a procedure and function for each program-unit, plus duplications that allow different datatypes to be handled. In the package details here I will talk about the procedures rather than the functions, and deal mainly with the versions that encrypt plain text rather than raw data.

As always with all Oracle Supplied Packages, there are a few restrictions of which to be aware. The first is a little annoying but can be overcome – the size of the input buffer that Oracle DBA want to encrypt must be a multiple of 8 bits. Hence the string ‘12345678’ will encrypt, but the string ‘123456789’ will fail. It is possible to get around this by right-padding column values and encrypting as a char but it’s a bit of a pain.

Another restriction is that Oracle DBA cannot encrypt a value twice – that its Oracle DBA shouldn’t encrypt a value and feed it back into the procedure as a value to be encrypted. However, I have tried this and fed the hex value (not the plain RAW value) of the first encryption back into another encryption with no problems. This conversion to hex may be a way around the restriction.



The procedures then;

Remember that each of these has a function equivalent that returns either the encrypted or decrypted key, plus further overloads that allow us to pass different datatypes to the procedures/functions.

DESEncrypt() takes 3 arguments – the string to be encrypted and the encryption key are both IN parameters, with one OUT parameter for the encrypted value. This procedure is overloaded with an equivalent with parameters of type RAW for use in encrypting raw data.

DESDecrypt() is the same as DESEncrypt() except with one OUT parameter for the decrypted value. This procedure is overloaded with an equivalent with parameters of type RAW for use in decrypting raw data.

DES3Encrypt() takes 4 arguments – there is a single extra argument called “which” that sets whether two or Three Key Mode is used.

DES3Decrypt() takes 4 arguments – there is a single extra argument called “which” that sets whether two or Three Key Mode is used.

An Example Demonstration

This demo is an extension of the example that appears in the Oracle 9I PLSQL Supplied Packages manual. What I have attempted to do is to hide the complexity of the DBMS_OBFUSCATION_TOOLKIT package within a new package called encryption, containing just two functions – encrypt and decrypt. Because NIS recommend Triple DES over plain DES I have plumped for this protocol.

The idea here is that the package has a hardcoded encryption key contained in the body, and that through wrapping the source, the key is protected. In this way we can generate the encrypted value for a piece of text, and store it in the database, and still get back at the original value.

This is the package text. Change the key_string to be what Oracle DBA require.

CREATE OR REPLACE PACKAGE encryption AS

  FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2;

END;
/
CREATE OR REPLACE PACKAGE BODY encryption AS

   --
   -- Declare the values for the key.
   -- The package will be wrapped to hide the encryption key.
   --
   key_string          VARCHAR2(32)  := 'A!190j2#Az19?j1@A!190j2#Az19?j1@';
   encrypted_string    VARCHAR2(2048);
   decrypted_string    VARCHAR2(2048); 

   --
   -- FUNCTION ENCRYPT encrypts encrypted string
   --
   FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DES3Encrypt( input_string      => input_string,
                                            key_string        => key_string, 
                                            encrypted_string  => encrypted_string );
      --RETURN  rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string));
     RETURN  encrypted_string;
   END;

   --
   -- FUNCTION DECRYPT decrypts encrypted string
   --
   FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN

      decrypted_string    := null;
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DES3Decrypt( input_string     => input_string,
                                            key_string       => key_string,
                                            decrypted_string => decrypted_string );

      RETURN  decrypted_string;
   END;

END;
/



Use the wrap utility to make your code unreadable – thus hiding the key.


C:\articles\encryption>wrap iname=encryption.sql oname=encryption_wrap.sql

PL/SQL Wrapper: Release 9.0.1.1.1- Production on Tue Dec 31 18:35:53 2002

Copyright (c) Oracle Corporation 1993, 2001.  All Rights Reserved.

Processing encryption.sql to encryption_wrap.sql

C:\articles\encryption>

How secure is DES though?

Please don’t go away from this article with the belief that DES encryption is a sure-fire brick wall to those who are actively trying to get at your data. All encryption can be solved given enough time – even if that time is a very long time..

For example; RSA Data Securities have sponsored open contests to crack the DES algorithm since the late 1990s. In 1998 a company called EFF – Electronic Frontier Foundation succeeded in cracking the 56-byte algorithm in 3 days using a machine that cost just $250,000. In 1999 a global network of some 100,000 PCs was used by “Distributed Net” to break the DES 56byte standard in just a little over 22 hours – it is reported these guys were testing something in the region of 245 billion keys per second. Given a reasonably priced multiple CPU server then, the DES standard isn’t unhackable. As I said, a determined hacker will get in – which is why it is important never rely solely on encryption, but combine it with other security measures.
More Tutorials on Oracle dba ...



Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information


Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info