Skip to Content
Author's profile photo Adhar Bhagat

How to Use Encryption in Data Services 4.0

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


Assigned tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ankit Bansal
      Ankit Bansal

      thanks for info....

      Author's profile photo Former Member
      Former Member

      Thanks adhar..very well done..very useful,keep good work going.

      Author's profile photo Former Member
      Former Member

      Thanks for the info

      Author's profile photo Scott Broadway
      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.

      Author's profile photo Former Member
      Former Member

      That's very nice stuff and informative one

      Author's profile photo Deepak K Gupta
      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

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Srihari Balakrishnan
      Srihari Balakrishnan

      This is for AES, can someone please give explaination on the PGP encryption and decryption in BODS using SFTP. Do we need any third party tool for this purpose? If so please suggest some freewares that will be available.

      Regards,

      Srihari

      Author's profile photo Suresh Chowtipalli
      Suresh Chowtipalli

      which query we needĀ  to use to findĀ  encrypted and decrypted data on DB