NH-3713 - Support Granular Level Encryption

[nhibernate-bot]

Ricardo Peres created an issue — 27th September 2014, 10:57:25:

Several databases support transparent data engine, SQL Server and Oracle to name just two.
It would be interesting to have NHibernate support this out of the box.

---

Alexander Zaytsev added a comment — 3rd October 2014, 0:36:46:

What needs to be done from NH side? Isn't it something that can be configured on server itself?

---

Alexander Zaytsev added a comment — 3rd October 2014, 0:44:36:

Closing as not an issue.

---

Ricardo Peres added a comment — 3rd October 2014, 7:24:50:

@alex: you should first try to understand what this is before saying it is not an issue... This does not involve just configuration on the database side, as you said, NHibernate doesn't need to care about that; basically, on SQL Server (Oracle is more transparent), you may need to configure a key (might be a configuration parameter in NHibernate), add a per-table attribute ("Is Encrypted"), run some SQL before every SELECT:

OPEN SYMMETRIC KEY SomeKey DECRYPTION BY CERTIFICATE SomeCertificate

transform the table columns in an INSERT:

INSERT INTO SomeTable (SomeColumnName) VALUES (EncryptByKey(Key_GUID('SomeEncryptionKey'), 'SomeColumnValue'))

and in a SELECT:

SELECT CONVERT(NVARCHAR(50), DecryptByKey(SomeColumn)) AS SomeColumn FROM SomeTable

and then run another SQL command:

CLOSE SYMMETRIC KEY SomeKey

As you can see, this involves a couple of things that are not easily done in NHibernate:
1 - Map a column as a SQL function for INSERTs and as a different one for SELECTs;
2 - Run SQL before and after a SELECT.

#2 could perhaps be done with an interceptor, in a rather hackish way. My idea would be to add support, at Dialect/Driver level, for this. The base Dialect/Driver class could return the SQL string for SELECTs and INSERTs equal to what it already does (column name), but a Driver/Dialect with knowledge of encryption would return the proper SQL.
See an example in http://www.databasejournal.com/features/mssql/granular-or-cell-level-encryption-in-sql-server.html.

---

Oskar Berggren added a comment — 3rd October 2014, 13:46:15:

<~rjperes> As I understand it, the example you show is not MSSQL feature "Transparent Data Encryption". Maybe that's the confusion? Possibly this issue should have a different title?

---

Ricardo Peres added a comment — 3rd October 2014, 13:49:14:

<~oskar.berggren>: I really don't understand your question. The idea is to support "Transparent Data Encryption", as it is supported by a number o databases, SQL Server being one of them. The example I gave is for SQL Server, and, if this were to be implemented, would be a perfect candidate for initial implementation.

---

Ricardo Peres added a comment — 3rd October 2014, 13:50:53:

Ah: "transparent data engine" instead of "transparent data encryption"! :-D

---

Oskar Berggren added a comment — 3rd October 2014, 14:31:21:

Ehum? The only google it I get for "transparent data engine" is this Jira issue.

This is my understanding of TDE (as quoted from the page you linked in the comment above, emphasis mine):

bq. "Transparent Data Encryption, as its name implies, is completely transparent to your application. This means literally no application code changes (only an administrative change to enable it for a database) are required and hence there is no impact on the application code\functionalities when enabling TDE on a database being referenced by that application whereas in the case of Granular or Cell level encryption a code change is required.

---

Ricardo Peres added a comment — 3rd October 2014, 14:38:08:

<~oskar.berggren>: we are talking a different language! :-)
You obviously never worked with TDE in SQL Server, nor did you read the article. It is not fully transparent. From the article:

--To encrypt data using symmetric key use the EncryptByKey   inbuilt function
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Steve', 'Savage', EncryptByKey(Key_GUID('Key4CellEncryption'),'1111-1111-1111-1111')),
('Ranjit', 'Srivastava', EncryptByKey(Key_GUID('Key4CellEncryption'),'2222-2222-2222-2222')),
('Akram', 'Haque', EncryptByKey(Key_GUID('Key4CellEncryption'),'3333-3333-3333-3333'))
GO
--When you query you will see data in is encrypted
SELECT   FirstName, LastName,   CreditCardNumber FROM dbo.Customer
GO

and

--To decrypt the encrypted data you need to use another inbuilt   function called DecryptByKey
SELECT   FirstName, LastName,   CreditCardNumber, CONVERT(VARCHAR(50), DecryptByKey(CreditCardNumber)) DecryptedCreditCardNumber 
FROM   dbo.Customer
GO

---

Oskar Berggren added a comment — 3rd October 2014, 14:47:37:

Well, no I've never actually used TDE but I've read about it in multiple places.

The article you link to isn't about TDE - it's about granular or column level encryption. As I understand it the whole point of Transparent data encryption is that it's enabled for the whole database by an administration and that it's transparent to the application, since the database engine will encrypt the actual data pages of the entire database. See http://www.databasejournal.com/features/mssql/transparent-data-encryption-tde-in-sql-server.html , linked to by the article you posted.

I suspect the title for this issue should be "Support Granular or Cell Level Encryption"

---

Ricardo Peres added a comment — 3rd October 2014, 14:58:03:

<~oskar.berggren>: you are probably right about the title. It is the same thing, though: TDE is a prerequisite of column level encryption. Like I said, "This does not involve just configuration on the database side": this would be configuring TDE, which NHibernate does not need to know about.

---

Alexander Zaytsev added a comment — 3rd October 2014, 23:19:33:

I agree with Oskar. There are two different things in MSSQL: Transparent Data Encryption and XXX Level Encryption (where XXX is Column, Row, Cell, Tablespace or File). The first one is complete transparent to application and encrypts database on a disk, the others are not transparent and require to call EncryptByKey/DecryptByKey functions.