java - Web-App using Hibernate that queries a SQL Server 2005 encrypted column -

we devolping web-application using spring 3.1.2 , hibernate 4.1.7 database sql server 2005.

on table we've got column encrypted , need perform queries like, example, one:

open symmetric key passwordfieldsymmetrickey decryption password = 'mypassword'  select id,         plain,        cipher,         convert(varchar(50),        decryptbykey(cipher)) 'decrypted' ts_own.cryptest;  go close symmetric key passwordfieldsymmetrickey  

as solution, someone proposed create view manages decryption need no 1 must see decrypted data, , of course dba example query view.

at same time don't want perform decryption on java side, due heavy aggregation logic expected performed database engine due performance reasons.

a possible solution create view performs decryption, aggregations , encrypts result 1 more time, performing decryption of aggregated values on java side.

does know alternatives?

thank all, luca

from server-side perspective, transperent solution use jasypt. library comes several hibernate usertypes encrypting text/password fields.

as mentioned in reference documentation, there limitations:

but encryption sets limitation on hibernate usage: security standards establish 2 different encryption operations on same data should not return same value (due use of random salt). because of this, none of fields set encrypted when persisted can part of clause in search queries entity belong to.

while hql/sql queries hide decrypting complexity, won't same performance specific database decryption function.

using database decryption functions performs better, queries embedded in views , that's going change dramatically way use hibernate.

you map entities views instead, you'll have pay attention dml statements (some dbs offer updatable views, others give materialized views or might use instead of triggers).

one possible solution open/close symetric use own @decrypt annotation , add aspect insert right after transaction starts , right before ends. work because sql session/connection bound current transaction/thread.