Skip to Content

Steps to create encryption job:

1.  Create a new Job as well as Dataflow where you can extract your data.


/wp-content/uploads/2013/01/1_180399.jpg

           

          Source Data:

/wp-content/uploads/2013/01/2_180380.jpg

2. In the Qry_Encrypt Transform you can define encrypt_aes function which is described as:

/wp-content/uploads/2013/01/3_180381.jpg

/wp-content/uploads/2013/01/4_180382.jpg

      In our example, we are encrypting EMP_SALARY column (usually it is confidential), for this we are passing following values in the Arguments:

  

input_string – EMP_SALARY

            passphrase – $G_Passphrase (Global Variable)

 

key_length_in_bits – $G_AESKey (Global Variable)

/wp-content/uploads/2013/01/5_180385.jpg

     Global Variables are declared at Job Level by passing constants value in them. (For more Security we can directly take Passphrase and AES Key from      Database Table rather than Hardcoding it in the job)

/wp-content/uploads/2013/01/6_180386.jpg

Output Data:


/wp-content/uploads/2013/01/7_180384.jpg

  

Steps to create enrichment job:                  

1.  Now encryption of data is done, we may also have some Business Rules to implement. For that we can proceed with different dataflow in same job or  we      can go for different job and dataflow which we did in this Demo.

/wp-content/uploads/2013/01/8_180387.jpg

2.  Here we are applying some Business Rules in Qry_Enrich like : If EMP_COUNTRY is “IN” then make it “India”,if “US” then make it “United States” and so      on:

/wp-content/uploads/2013/01/9_180388.jpg

          Output Data:

/wp-content/uploads/2013/01/10_180392.jpg


Steps to create decryption job:                   

1.  This is the Final Job of Demo, where we are decrypting the encrypted data (done in the first job) with the same Passphrase and AES Key.

/wp-content/uploads/2013/01/11_180393.jpg

2.  In the Qry_Decrypt Transform you can define decrypt_aes function which is described as:

/wp-content/uploads/2013/01/12_180394.jpg

/wp-content/uploads/2013/01/13_180395.jpg

     

     In our example, we are decrypting EMP_SALARY column , for this we are passing following values in the Arguments:

     input_string – EMP_SALARY

     passphrase – $G_Passphrase (Global Variable)

 

     key_length_in_bits – $G_AESKey (Global Variable)

     Since for decryption we have to use same Passphrase and AES Key, thus we are using same global variables which will be declared in this job too with      same hardcoded values.

/wp-content/uploads/2013/01/14_180396.jpg

/wp-content/uploads/2013/01/15_180397.jpg

           Output Data:

/wp-content/uploads/2013/01/16_180398.jpg


To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Scott Broadway

    Fantastic example of how to do it.  Would love to see a follow-up article about why to do it and how this provides a secure data solution for the customer.

    (0) 
  2. Deepak K Gupta

    Nicely framed Aadhar !

    But if you put in stuff as why we do it and what value it will add, that will be good.

    As in if in warehouse I am maintaining encrypted and decrypted both the data, looks not getting more values. Hope you understand.

    Best

    Deep

    (0) 
  3. Hannah Short

    Thanks for this – very helpful!

    One question, if anyone could help that would be much appreciated, can validation and enrichment rules be run on encrypted fields? Above you show a rule applied to EMP_COUNTRY, but this is not encrypted. Could rules equally be applied to EMP_SALARY?

    (0) 

Leave a Reply