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:
- Convert the input value into binary format. So it can be used to feed the hash function.
- Apply the hash_md5 function on the above generated binary value.
- Merge the binary value using bitwise operator to generate a single binary value.
- 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.
- 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.