DBMS_DDL- Securing the source code

DBMS_DDL Package

DBMS_DDL is one of the package that guard the casual viewing of the source code, also it has one of the procedure to compile the database object , we will see the procedure and functions one by one.

1. DBMS_DDL.ALTER_COMPILE :-

DBMS_DDL.ALTER_COMPILE (
   type             VARCHAR2, 
   schema           VARCHAR2, 
   name             VARCHAR2
   reuse_settings   BOOLEAN := FALSE);

 the above procedure is deprecated, but it is still available in the package for backward compatibility.  

We can use below statement to re-compile the database object :-

ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.] <name> COMPILE [BODY]   

2. DBMS_DDL.WRAP :- 

Used to hide (obfuscate) your PL/SQL source code. Traditionally this has been done using the wrap utility, but Oracle 10g Release 2 also allows this to be done dynamically using the DBMS_DDL package.

Lets do the particle step by step :-

Before starting we should know how to extract the source code for the procedure or function or package which we want to encrypt , that will learn in the code it self while wrapping the PL/SQL Code. Two ways we can do :-

1.  Using the ALL_SOURCES :- You can get the whole code from all_source and same you can use while obfuscating or encoding the code

For example :-

  DECLARE
   code_extract             DBMS_SQL.varchar2a;
   l_wrap     VARCHAR2 (32767);
   l_encode   VARCHAR2 (32767);
BEGIN
   SELECT text
     BULK COLLECT INTO code_extract
     FROM all_source
    WHERE TYPE = 'PROCEDURE' AND name = 'ENCODE_TEST';
   l_wrap := 'create or replace ';
   FOR i IN 1 .. code_extract.LAST
   LOOP
      l_wrap := l_wrap || code_extract (i);
   END LOOP;
   DBMS_DDL.CREATE_WRAPPED (l_wrap);
END;

You can see the following things in the code

•  First we extracted the code from all_source , after that we have used create_wrapped procedure to encode the source code. Once the above block completed , the source code of the procedure will be in encoding format, make sure you take the backup of source code before performing the above steps.

Encoded Source Code Post Block Execution :-

"procedure encode_test wrapped
a000000
1f
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
5e 8d
RPPtb62smzxirFNbCnlmJtt4RPEwg5nnm7+fMr2ywFxa1wz/rqHwlqFi0e4JpuFycHxDXfy5
KVvJvlc6Ocb4B0A2RkB9+1I00LBPCkBFgCfc3LzmMD/M3BhF6CpUawpPmUavpqbGhgTf
"

I Hope the post helps in basic understanding of package how to use and how it can help in compiling the invalid object ,you use with the dynamic SQL as well to create the automated script , before using  the package make sure you have execute grant over the package.

You can test the script live on :-
https://livesql.oracle.com/



Please comment your feedback about the article .. and share if you like :)

Wish you a Happy new year!!!

DBMS_CRYPTO to encrypt and decrypt the Data in Oracle Database

DBMS_CRYPTO Let's Encrypt the Data..

Whats' the special in this post?
In this post i will be showing step by step demonstration with scripts and description of DBMS_CRYPTO ,post contains mixture of all blogs and tutorial for easy understanding.

Here we go :-

First we need to understand the basic terminologies :-

What is Encryption and Encryption Algorithm ?

Encryption is the process of converting the data into encoded form , especially to prevent unauthorized access.

Encryption Algorithm is the mechanism by which we can encrypt the data, below are the different categories of the Algorithm :-

Alogrithm Name	Alogrithm Type
Cryptographic algorithms	DES, 3DES, AES, RC4, 3DES_2KEY
Padding forms	PKCS5, zeroes
Block cipher chaining modes	CBC, CFB, ECB, OFB
Cryptographic hash algorithms	MD5, SHA-1, SHA-2 (SHA-256, SHA-384, SHA-512), MD4
Keyed hash (MAC) algorithms	HMAC_MD5, HMAC_SH1, HMAC_SH256, HMAC_SH384, HMAC_SH512

You can get information on each algorithm category on google about its functionality , here we will take few of the algorithm that we will be using to encrypt the data .

DBMS_CRYPTO package includes both encryption and decryption procedure and function, we use procedure to encrypt and decrypt the large object data type such as LOB and CLOB and function to encrypt the RAW data type  or we can say directly crypto package will not encode the varchar2 data type first we need to convert the data into RAW type. 

So lets start step by step :- 

FUNCTION  Encrypt (src  IN            RAW,

                                   typ  IN            PLS_INTEGER,

                                   key IN            RAW,

                                    iv   IN            RAW          DEFAULT NULL)

      RETURN RAW; 

Lets understand the parameters of Encrypt function :- 

SRC :- In parameter refers to input string which we will encode , it is of raw type as first we need to convert the VARCHAR2 to RAW type.

TYP :-  All the algorithm  type which we have shown above is assigned a pre-defined value. 

             for example :-  

                        ENCRYPT_AES256  CONSTANT PLS_INTEGER            :=     8;

                        CHAIN_CBC             CONSTANT PLS_INTEGER            :=   256;

                        PAD_PKCS5             CONSTANT PLS_INTEGER            :=  4096;

we can use individual type or we can combine all of three together , to encode data strongly.

Key :- Once we have selected the type of algorithm we will be using, now we can select the key for encryption by using 

1.  DBMS_CRYPTO.RANDOMBYTES (no_of_bytes) 
2. v_key raw(16):= UTL_RAW.cast_to_raw('mykeytoencode');

IV :- If we use block cipher algorithm then we can specify the IV else Default is NULL.

Conversion Rules

• To convert VARCHAR2 to RAW, use the UTL_I18N.STRING_TO_RAW function to perform the following steps:
  1. Convert VARCHAR2 in the current database character set to VARCHAR2 in the AL32UTF8 database character.
  2. Convert VARCHAR2 in the AL32UTF8 database character set to RAW.
  Syntax example:

  UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8');
  

• To convert RAW to VARCHAR2, use the UTL_I18N.RAW_TO_CHAR function to perform the following steps:
  1. Convert RAW to VARCHAR2 in the AL32UTF8 database character set.
  2. Convert VARCHAR2 in the AL32UTF8 database character set to VARCHAR2 in the database character set you wish to use.
  Syntax example:

  UTL_I18N.RAW_TO_CHAR (data, 'AL32UTF8');

What is AL32UTF8 ?

A Unicode database is a database with a UTF-8 character set as the database character set. There are three Oracle character sets that implement the UTF-8 encoding. The first two are designed for ASCII-based platforms while the third one should be used on EBCDIC platforms. AL32UTF8.

Let's write a simple block to encrypt and decrypt the string by using above knowledge :- 

DECLARE


   input_string       VARCHAR2 (200) :=  'Welcome to the world of Oracle';

   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

   encryption_type    PLS_INTEGER :=          -- total encryption type

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

   iv_raw             RAW (16);



BEGIN

   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);

   key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);

   iv_raw        := DBMS_CRYPTO.RANDOMBYTES (16);

   encrypted_raw := DBMS_CRYPTO.ENCRYPT

      (

         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

         typ => encryption_type,

         key => key_bytes_raw,

         iv  => iv_raw

      );

dbms_output.put_line('Encrypted Message: '||encrypted_raw);

   decrypted_raw := DBMS_CRYPTO.DECRYPT

      (

         src => encrypted_raw,

         typ => encryption_type,

         key => key_bytes_raw,

         iv  => iv_raw

      );

   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string); 

END;

O/P :-

Original string: Welcome to the world of Oracle
Encrypted Message: F695024419E4AD850590CC3227C6FD8D828C357D08803DA0168771AC4391F540
Decrypted string: Welcome to the world of Oracle

So the above code shows a simple example of encryption and decryption, i have taken the code and most of the information form :-
https://docs.oracle.com/database/121/ARPLS/d_crypto.htm#ARPLS65690

Hope it helps in basic understanding of package how to use and how it can help in encrypting the data and to decrypt the same, before using make sure you have execute grant over the package.

You can test the script live on :-
https://livesql.oracle.com/

Please comment your feedback about the article .. and share if you like :)

Thanks,
Mohit

Year 1 @ Infosys

Colors in life is the most integral part. It changes the mood of the people and yes the level of designation in Infosys also ;) Ohh Almighty!! I donno how do I describe the journey from the tag of Internship to color tag to Black tag. It was simply awesome. 

I remember it started on 18th jan 2015, my first day in Infosys waiting eagerly out of the campus to go inside. Students were coming in buses, travellers gaadi etc. All made a queue in sometime and started entering inside the campus. The first person a part from known I met was Shubhankar waiting for his friend Nikita :p I met another girl who forgot her offer letter copy. She was cute and yes innocent too. I will not say the name ;)  We then entered the campus and Ohh its trolly where we had to put the bagde and then moved to another queue. Hehe Infosys follows the queue algorithm I believe when it started. After that we had shifted our luggage to other bus and girls got Innvoa :P to go after that. Oh God! Yes, this is the campus people talk about GEC 2. We went inside and got internship tag and a black pen and detail about next days session. We also got the our respective room keys of a Five Star Rooms. Later we again sat in the same bus and went to our respective ECC (Employement Care Center). I talked to many people in the bus. Two of them from Delhi who were good in cracking joke :p I enjoyed a lot . 

In ECC room, I was pretty much excited. For a student like me who used to stay in college hostel, getting a room with TV n all facilities was like a free Pizza on Buy One Get Two Free offer. It was the first day and me with Basu Vishal went for a bicycle ride. We tried to sit one on one but ha ha the first whitlse that security guard blew and after that how many times I faced these situations Basu Vishal Shivi know :p.

We went to Oasis for dinner and My God the rush there was too much with lots of people with different color tag seeing us as if we came from different planet. Each one wanted to know from which batch we were. We talked with a few in Queue only :p. We had Dum aaloo and it was simply awesome. This is the first day at infy.

Today, it is 19th Jan 2016. A different day. Just the color of the tag changed and responsibility we will be having in near future. All the best for the coming years guys!!!

Do comment and share your memory of first day :)  :) 

Mika Singh at Infy Bangalore DC

Ohhh its great evening what I say a huge mob of people awaiting for the MIKA PAAJI. The total strength of the Infosys is 1.7 k, I think around 1.5k were present. What an evening it was!!

I saw people of different age groups enjoying in their way. I just focused on the age group mentioned in this article. The age group of youngsters having different attitudes and different way of enjoying. Some between 30 and 35 standing idle and listening to songs even some of 25-29 doing the same. Some people having their lunch boxes in the hand, listening the songs and reviving their old days. There was a couple which wanted to dance but couldn't as they were not in group.

People in the group were enjoying like they have achieved something that is simply hard to achieve. It was like a college event rather than a company conducting an event for it's employees. So huge was the crowd.

I was also with my friends. 5 girls and me being the only boy in the group thinking who to dance with. I just knew few steps to dance. Girls with me tried to make me feel comfortable so that I can dance freely and Yeah I danced well =D ;). We had done our training in Mysore where KK not Kailash Kher had come. There we had enjoyed a lot because we had a huge group to enjoy. But after that today in professional life, people are searching for a group. Some standing alone, some looked like they wanted to join groups in which girls were pretty.

It was like different experience for me, I was just analyzing the mob of people where some were enjoying, some were passing comments to make people laugh and some just watching people who are dancing and laughing like me.

After the event which had a tight security arranged by Infosys, I saw a chain of security guard making way so that employees can easily walk or maybe they are providing security to mika singh + vishal sikka mika sikka Having CHICKEN TIKKA last night with Richard and david definitely and that RJ also THE A . I forgot the name of the person who said "LOVE YOU CROWD" three times in a minute.

Finally event finished. I took few selfies as girls were with me and we went to Hotel Shiv Shankar for our dinner. And yes there started the discussion among girls for deciding among Roti, Butter Roti, Fulka, Tandoori Roti etc etc. People around our table were laughing. I just sat silent and listened to the ladies. A boy opposite to our table slowly said "All the best", I just smiled and replied "Thanks".