Skip to Content
Author's profile photo Qi Gao

Column Encryption Decryption on HANA

The last project I’m working on, we have some special requirements to secure sensitive customer data(e.g. social security number, driver license number, credit card number, etc) with encryption and decryption to protect it from data breach. It has nothing to do with recent data breach at Equifax, the client is keen to protect customer sensitive data for a long time before migrating to HANA. Since the customer data security has become such a hot topic after the Equifax data breach, I think it is worth sharing the information with the community.

Originally, I thought this is a pretty simple task. As almost all the systems I worked with before have some type of encryption function and it can be used together with user role management. But after some exploration of the HANA provided functions, surprisingly, I didn’t see any encryption/decryption function provided. The only things you can find which have some relevance to this are disk data volume encryption and so called dynamic data masking. If you contact SAP, this will be the answer you get, but it is not what we are looking for. We want the sensitive data to be encrypted when persisted on HANA, and decrypted at run time when authorized user use it.

After scratching my head for a little while, I came up with an idea to create custom scalar function to do encryption and decryption based on existing HANA functions. The idea is pretty straightforward, and the implementation is quite simple once you know the idea. You can take a look at the examples in the below screenshot, it works very similar to AES encryption function. Basically, you will need have the value need to be encrypted and also supply a passphrase, the value will be encrypted based on the phase phrase.  When it comes to the decryption, if you supply the same passphrase, the value can be decrypted, and it will be the same as the original supplied value.


To implement the encryption function, the idea is basically as following, and you can also find the code snippet here:

  1. Convert the input value into binary format. So it can be used to feed the hash function.
  2. Apply the hash_md5 function on the above generated binary value.
  3. Merge the binary value using bitwise operator to generate a single binary value.
  4. Manipulate the above generated binary value to map it to a range of asc2 code page, so the binary value can be converted back to string successfully.
  5. Convert the binary value back to string.


To decrypt it, it will be the same thing, just the opposite direction.

The end to end solution is a little bit more complex, as it requires security role setup, and determination of security group of the session user, and then determine if the session user has the privilege to decrypt the data when the calculation view is invoked. At high level, the encrypt function will be called through stored procedure within the same session, after the sensitive data landed on HANA, so sensitive data will be encrypted when the data is persisted on HANA. And table function will be used to invoke the decryption function based on the user roles, and the table function will be used as a source in the corresponding master data calculation view. So when the users use the calculation view, their assigned security role will be checked,and then depends on their role, the calculation view will return either decrypted value or encrypted value.

The encryption and decryption function is the core of the solution. This custom function has value for now, but in the future, if SAP delivered AES encryption function within HANA, then this custom function can be replaced.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Anand Nayak Rao Kotti
      Anand Nayak Rao Kotti

      Hello Qi,

      Thanks for information, there is something called format preserving encryption, I wonder how this works in the above equation ? (AES on HANA not sure when it is going to be GA.)

      Author's profile photo Mauricio Sewaybricker
      Mauricio Sewaybricker


      Is possible to share for this part of the function what is filled in each variable?

      I'm trying to implement the function but I got lost when filling the variables.


      Please, if possible, can you share the function code?



      Thanks in Advance!

      Author's profile photo Venkata Sabbasani
      Venkata Sabbasani


      We are looking for this feature to be enabled. Can you please share the implementation steps for this?

      Best Rgds,

      Venkata Sabbasani

      Author's profile photo Alejandro Raitarsky
      Alejandro Raitarsky



      I see that you have shown the code using a encryption and decryption function, but in the code you can not see the whole idea, more important as far as it is shown in several pages, the hash function only works one way. I you were able to pull this off, can you show the code for the encryption and the decryption functions?


      Thank you


      Alejandro Raitarsky

      Author's profile photo 원형 박
      원형 박


      It seems to be a good function. Can you share your function?
      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Hi ,


      Can you help us to understand how did you convert back from HASH to actual value? Can you share the code snippets?

      Author's profile photo fengzheng zhang
      fengzheng zhang

      Hi ,


      Can you help us to understand how did you convert back from HASH to actual value? Can you share the code snippets?

      Author's profile photo Chandrakanth Angannagari
      Chandrakanth Angannagari

      would this work for typical s4hana based application ( or abap on hana) or is it meant mainly for native hana applications ?

      Author's profile photo Lalitha Jagannathan
      Lalitha Jagannathan

      Can you share the function to convert back from HASH  to actual value?