I have been playing aroudn with column level encryption and nhibernate
and I have found a simple yet incomplete way to leverage SQL Servers
column level encryption.
IList<Person> people = new List<Person>();
using(var transaction = session.BeginTransaction())
{
try
{
OpenEncryptionKey(session);
people =
session
.CreateCriteria<Person>()
.List<Person>();
CloseEncryptionKey(session);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
}
}
private static void OpenEncryptionKey(ISession session)
{
var command = session.Connection.CreateCommand();
session.Transaction.Enlist(command);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "OpenKey";
command.ExecuteNonQuery();
}
private static void CloseEncryptionKey(ISession session)
{
var command = session.Connection.CreateCommand();
session.Transaction.Enlist(command);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "CloseKey";
command.ExecuteNonQuery();
}
The code snippet above wraps the call to the DB in a transaction and
further wraps queries to the database in calls to open and close the
encryption key. The Person entity is mapped to a view that decrypts
the encrypted fields. Unfortunately you cannot insert into computed
columns on a view so you will need to do all saves/updates/ deletes
via stored procedures.
Now while this code works in the simplest case. Where it will fail is
if you needed to lazy load an entity that has encrypted fields, since
the encryption key will be closed at the end of the transaction. In
order to make this work I would need the ability to open and close the
key immediately before and after commands are sent to the database. I
have looked at the event listeners and none of them seem to give me a
way of doing this. The PreLoadEventListener looks promising but, since
I have no way of delimiting the begining and end of a batch of
statements being sent to the database I would essentially be opening
and closing the key for every entity loaded from the database. While
this could work it is horribly inefficient. What I would love to know
is where can I hook into nhibernate just before it sends a list of
commands over a connection and after it has received all its results
from the connection.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
