Encryption and Decryption

Encryption and Decryption of data is very important for everyone of us . I was just scrolling through found lot of options provide by Oracle. 

Oracle is provide an API -DBMS_OBFUSCATION_TOOLKIT that enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms 

Oracle installs this package in the SYS schema. You can then grant package access to existing users and roles as needed. The package also grants access to the PUBLIC role so no explicit grant needs to be done. 

When we do Encryption and Decryption, Most Important part of this Process is Key Management. 

1. Key Management
2. Key Generation
3. Key Maintainance. 

Key management, including both generation and secure storage of cryptographic keys, is one of the most important aspects of encryption. If keys are poorly chosen or stored improperly, then it is far easier for a malefactor to break the encryption. Rather than using an exhaustive key search attack (that is, cycling through all the possible keys in hopes of finding the correct decryption key), cryptanalysts typically seek weaknesses in the choice of keys, or the way in which keys are stored. 

Key generation is an important aspect of encryption. The DBMS_OBFUSCATION_TOOLKIT package includes tools for generating random material that can be used for encryption keys, but it does not provide a mechanism for maintaining them. Care must be taken by the application developer to ensure the secure generation and storage of encryption keys used with this package.

 

As per mosr author on this subject ,we have three options available for Key Maintainance and those are

1. Store the key in the database
2. Store the key in the operating system
3. Have the user manage the key

In this post I will not discuss about what option one should choose or what are the Pros and Cons of these 3 .

Here I will just talk about the Encryption and Decryption API provide by Oracle.API DBMS_OBFUSCATION_TOOLKIT provide by Oracle has 3 procedures/Functions

1. DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT
2. DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt
3. DBMS_OBFUSCATION_TOOLKIT.DES3GetKey

Each of these 3 has 4 options (Please refere Oracle user guide or seach www.oracle.com for more details).I will discuss only 1 example each for these 3.

DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT  - this subprogram generate the decrypted form of the input data.

 

DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT(

   input         IN  RAW,

   key           IN  RAW,

   which         IN  PLS_INTEGER DEFAULT TwoKeyMode

   iv            IN  RAW         DEFAULT NULL)

  RETURN RAW;

Please refere oracle.com for other DECRYPT options provide by this API.

Restrictions

You must supply a single key of either 128 bits for a 2-key implementation (of which only 112 are used), or a single key of 192 bits for a 3-key implementation

 

 

DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT – this subprogram generate the encrypted form of the input data by passing it through the Triple DES (3DES) encryption algorithm.

 

DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt(

   input        IN RAW,

   key          IN RAW,

   which        IN PLS_INTEGER DEFAULT TwoKeyMode

   iv           IN RAW         DEFAULT NULL)

  RETURN RAW;

 

Please refere oracle.com for other Encrypt options provide by this API.

Restrictions

 

The DES3ENCRYPT procedure has two restrictions. The first is that the DES key length for encryption is fixed at 128 bits (for 2-key DES) or 192 bits (for 3-key DES); you cannot alter these key lengths.

The second is that you cannot execute multiple passes of encryption using 3DES.

 

DBMS_OBFUSCATION_TOOLKIT.DES3GetKey – this  subprogram take a random value and uses it to generate an encryption key.

 

DBMS_OBFUSCATION_TOOLKIT.DES3GetKey(

   which  IN  PLS_INTEGER DEFAULT TwoKeyMode,

   seed   IN  RAW)

 RETURN RAW;

 

Below is one complete Test case of these 3.

Test case – Store Customer # and Seed in database table ( Restrict the access to Seed Key).

Now Run the pl/sql program , It will take

Employee_id

Credit card # and

Operation (E- Encrypt / D-Decrupt) as parameter

 

If operation is E, it encrypt the Credit crad # and store the encrypted value in database.

If operation is D, it then Decrypt the encrypted CC# and Print it on the screen.

My test exapmle is bad example of encryption and description, but purpose of this post is to just show how this API work .

 

 

create table test_mk

( eid NUMBER,

  sed RAW(1000),

  ekey RAW(1000),

  evalue RAW(1000));

 

select * from test_mk

 

insert into test_mk

(eid,sed)

values

(1,’22222222222222222AAAAAAAAAAA67465465465465465AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFFFFFFFFFFAAAAAAAAAACCCCCCCCCCDDDDDDDDDDEEEEEEEEEEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’)

 

8988988899898898

drop table test_mk

l_string           RAW(2000);– :=’22222222222222222AAAAAAAAAAA67465465465465465AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFFFFFFFFFFAAAAAAAAAACCCCCCCCCCDDDDDDDDDDEEEEEEEEEEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’;

 

declare

l_encrypted_data   RAW(1000);

l_decrypted_data   RAW(1000);

l_key              RAW(1000);

l_emp_id           NUMBER :=&Enter_Emp_id;

l_seed             RAW(1000);

no_rec             EXCEPTION;

l_case             VARCHAR2(1):=’&EnterCase’;

l_evalue           RAW(1000);

l_ovalue           VARCHAR2(20):=’&EnterOriginal_Value’;

BEGIN

    BEGIN

        SELECT sed, ekey,evalue

         INTO  l_seed,l_key,l_evalue

         FROM test_mk

        WHERE eid = l_emp_id;

        EXCEPTION

          WHEN OTHERS THEN

            dbms_output.put_line(‘Error  = ‘||Sqlerrm);

            raise no_rec;

    END;   

CASE

  WHEN l_case = ‘E’ THEN  

      –l_ovalue:=’8988988899898898′;

      dbms_output.put_line(l_ovalue);

      dbms_output.put_line(l_seed);

    dbms_output.put_line(‘l_input = ‘||l_emp_id);

    l_key:=DBMS_OBFUSCATION_TOOLKIT.DES3GetKey(

       which       =>0,

       seed =>l_seed

       );

    dbms_output.put_line(‘l_key = ‘||l_key);

    l_encrypted_data :=DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt

    (input =>l_ovalue

    ,key   =>l_key

    ,which =>0

    ,iv    =>NULL

    );

    dbms_output.put_line(‘l_encrypted_data = ‘||l_encrypted_data);

    update test_mk

    set evalue = l_encrypted_data,

        ekey = l_key

    where eid = l_emp_id;

  WHEN l_case = ‘D’  THEN

  l_decrypted_data:=DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT

    (

     input=>l_evalue

    ,key  =>l_key

    ,which =>0

    ,iv    =>NULL

     );

  dbms_output.put_line(‘l_decrypted_data = ‘||l_decrypted_data);

  END CASE;

EXCEPTION

  WHEN no_rec THEN

  dbms_output.put_line(‘Exception raised- Quitting Process’);

  WHEN OTHERS THEN

  dbms_output.put_line(‘Error – ‘||sqlerrm);

END;