Hibernate web application that queries SQL Server 2005 encrypted column

Question

we are canceling the web application using Spring 3.1.2 and Hibernate 4.1.7 with a SQL Server 2005 database.

We have an encrypted column on the table, and we need to execute some queries, for example, the following:

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey DECRYPTION BY PASSWORD = 'myPassword' SELECT id, plain, cipher, CONVERT(varchar(50), DecryptByKey(cipher)) AS 'Decrypted' FROM TS_OWN.cryptest; GO CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey 

As a solution, someone suggested creating a view that controls the decryption, but we need no one to see the decrypted data, and of course, the DBA can request this view.

At the same time, we do not want to perform decryption on the java side due to some large aggregate logic, which is expected to be executed using the database engine due to performance reasons.

A possible solution is to create a view that decrypts, aggregates and then encrypts the result again, decrypting the aggregated values ​​on the Java side.

Does anyone know of alternatives?

Thanks everyone, Luke

Answer 1

From a server-side perspective, the most transparent solution is to use Jasypt . There are several Hibernate UserTypes in this library for encrypting text / password fields.

As mentioned in the reference documentation, there are limitations:

But encryption sets a limitation on the use of Hibernate: security standards establish that two different encryption operations on the same data should not return the same value (due to the use of random table salt). Because of this, none of the fields that are set to be encrypted when saved can be part of the WHERE clause in your search queries for the object to which they belong.

As long as your HQL / SQL queries hide the complexity of decryption, you will not get the same performance as with a specific database decryption function.

Using the database decryption functions performs better, but then all your queries will be embedded in the views and that will change dramatically the way you use Hibernate.

Instead, you could map objects to views , but you need to pay attention to DML statements (some DBs offer updatable views , others give you materialized views, or you can use INSTEAD OF triggers).

One possible solution for OPEN / CLOSE SYMETRIC is to use your @Decrypt annotation and add an aspect to insert them right after the start of the transaction and before it ends. This will work because the sql session / connection is bound to the current transaction / thread.