Ayende @ Rahien

Refunds available at head office

NHibernate – Get Thou Out Of My Database

image There are evil people in this world, and some of them want access to my database. Unfortunately, they are often part of that nasty integration team and they try to integrate directly into my database. I tried beating them with clubs and lobbing arguments about letting other people mess with my implementation details, but they have been persistent. That is when I reached out to a technological solution for the problem.

I want to emphasize that this is the nuclear option and  you want to really consider it before going that route.

We are going to use NHibernate to do that, naturally. Here is how:

image 

Which results in the following schema:

create table [tbl_-1434067361] (
   [col_287061521] INT IDENTITY NOT NULL,
   [col_4699698] INT not null,
   [col_-1966747349] NVARCHAR(255) null,
   [col_-649855325] NVARCHAR(255) null,
   [col_-649855326] NVARCHAR(255) null,
   [col_775690683] NVARCHAR(255) null,
   [col_-2127361396] NVARCHAR(255) null,
   [col_-1334581412] NVARCHAR(255) null,
   primary key ([col_287061521])
)

To make sure that we follow procedure, we are even using the naming convention of the organization! Lovely, isn’t it? It is obvious that this is my people table, right?

All arguments against this schema can be answered using: “It is more secured”.

It might be the nuclear option, but it tend to work :-)

Nitpicker corner: No, I don’t suggest you would do this, that is why the code is an image. This is just an example of how to programmatically modify the configuration and mapping. I only used it once, during a demo to the integration people, to get them to leave my DB alone. If you push something like that for production you should probably be shot.

Comments

Sebastian Markbåge
06/06/2009 02:35 PM by
Sebastian Markbåge

Any NHibernate/DDD introduction tutorial should come with this naming convention by default. THEN if you REALLY need to, you opt out.

SHODAN
06/06/2009 06:00 PM by
SHODAN

Looks more or less as intelligible as the table/column names of a certain Swedish ERP system I've had the misfortune to work with..

Marc Brooks
06/06/2009 07:17 PM by
Marc Brooks

Oh dear, wouldn't a simple ROT13 be safer and still get the point across?

Ayende Rahien
06/06/2009 10:01 PM by
Ayende Rahien

Marc,

Why, this is following the naming convention as laid out. :-)

PandaWood
06/07/2009 12:01 AM by
PandaWood

nitpicker corner is a great idea btw - it's so often necessary

configurator
06/07/2009 02:09 AM by
configurator

Did you steal the nitpicker's corner from The Old New Thing?

Also, this would return different results in 32-bit and 64-bit. Watch out!

Ayende Rahien
06/07/2009 03:39 AM by
Ayende Rahien

Configurator,

Yes, I did.

And I am absolutely shocked(!) that you even considered the possible implications of using this

Duckie
06/07/2009 12:10 PM by
Duckie

SHODAN -> I simply have to guess... Visma?

configurator
06/07/2009 12:16 PM by
configurator

Ayende, once you've been bitten by string.GetHashCode() once, you never forget it. Trust me.

I don't use GetHashCode() for any purpose other than getting the hash code. Any purpose.

FH
06/07/2009 01:38 PM by
FH

Ayende, I have a scenario where I need to do something like you did here. i.e.

1) Make table name and its columns un-understandable

2) Make data of few fields of few tables encrypted (preferably) or make it again difficult to understand with options like ORing or ANDing the data.

I am using NHibernate2.

What do you suggest?

FH
06/07/2009 01:49 PM by
FH

And still HSQL should work for operators = and like etc.

Gunnar Liljas
06/07/2009 06:35 PM by
Gunnar Liljas

This kind of schema obfuscation is used by several large ERPs, CRMs etc. Some even say "Want to work directly with our database? Sure thing. Just buy this document containing a translation to unobfuscated table and column names, and we wont mind."

Ayende Rahien
06/07/2009 07:16 PM by
Ayende Rahien

FH,

You have the first part here.

The second is just setting up UserType with encryption

Jeremiah Peschka
06/07/2009 10:49 PM by
Jeremiah Peschka

I'm curious about why you would have to resort to even threatening something like this. What was the rationale? What were the implementation people planning on doing that you disagreed with?

Ayende Rahien
06/07/2009 11:18 PM by
Ayende Rahien

They wanted to touch my database

Mark
06/08/2009 01:43 AM by
Mark

Jeremiah,

The "Domain" layer should not be bypassed by anything. It can and should be done by messaging. I am currently having to integrate with two systems that contain much of the same data but all so I have to create a MDM. Unfortunately the are not "enterprise" systems and thus the only way to integrate is at the DB. If they had system messaging (and a domain layer), I would probably be done in a few days.

Onur BIYIK
06/08/2009 07:12 AM by
Onur BIYIK

I think this will NOT protect you from a really really nasty integration team.

A. Nony. Mouse.
06/08/2009 12:29 PM by
A. Nony. Mouse.

That looks very much like the AS400/DB2 tables I have to get data from every day. I feal right at home.

ORM Messiah
06/08/2009 07:39 PM by
ORM Messiah

Dear Ayende,

If your database is so unusable tha tno one can understand it no one can query it except you, that is an unusable database.

A database should be able to be accessed by other people if necessary and should be well designed and descriptive just like writing good and readable code. What if you declared all your methods and fields in your class as Methodx123 and Fieldx2342 ? (Btw, I have seen people do that!)

You are missing the boat here on what databases are all about and what data access layers should be doing ... surely not dictating database design!

Jeremiah Peschka
06/08/2009 09:22 PM by
Jeremiah Peschka

Mark,

Many folks in the database architecture field would disagree and say that the data access layer should be contained in the database because you can never guarantee where data access will come from nor can you guarantee the life cycle of your application language of choice. Which is neither here nor there, I was simply curious what Ayende was worried about the implementation team doing. Personally, as a database developer, I was curious as to why he was concerned about them touching the DB.

Now I know and I can happily go back to writing stored procedures.

Ayende Rahien
06/08/2009 10:40 PM by
Ayende Rahien

ORM Messiah,

I guess you missed the nitpicker corner.

Mark
06/09/2009 01:26 AM by
Mark

Jeremiah,

Oh, i know they do disagree. That is why they are database architects and not system/solution architects. Even if you have the data access layer in the db, you save very little in the application coding wise, They still have to have their own DAL.

I was just further explaining the "why" and trying to help Ayende explain it to you

In applications, the database is part of the application (system). If it isn't then it belongs to another application and should be treated as such. I've done plenty of systems both ways in many languages, databases and platforms. I am not just an "app" developer. That is why I have the skill and ability to see it from all perspectives and make the best decision.

Enjoy the store procs! I'll go back to coding loosely coupled systems.

Mark
06/09/2009 01:29 AM by
Mark

ORM Messiah,

It is called code obfuscation and there are tools to do it pre-deployment. It is a valid concern. Sadly, there is not a good way to do it with the database. Using an ORM makes it much easier though. Using Stored procs is a form of obfuscation too.

ORM Messiah
06/09/2009 02:38 PM by
ORM Messiah

Mark : He didn't mean after deployment, he was talking about other people within the project! Ayende Said: 'Unfortunately, they are often part of that nasty integration team and they try to integrate directly into my database. '

Obfuscating the database so the integration team cannot use is definitely vile. If you have to obfuscate your database so that no one can integrate to it, I repeat you have a useless database. You completely missed the boat on what databases are for.

Objects can present an integration point too, but for reporting and warehousing and other database related purposes other people should be able to use your database.

Ayende Rahien
06/09/2009 02:44 PM by
Ayende Rahien

And you are still missing the nitpicker corner, amazing.

And that wasn't in my team, it was another team totally unrelated to me.

ORM Messiah
06/09/2009 03:26 PM by
ORM Messiah

Its interesting that the ORM and loose coupling debate always sparks up whenever nhibernate is mentioned.

Mark says : ' I'll go back to coding loosely coupled systems. '

Jeremiah says: 'Many folks in the database architecture field would disagree and say that the data access layer should be contained in the database'

If Nhibernate and ORMS solved the dataaccess and loose coupling problem elegantly it would not be popping up all the time. Seems like there is still a lot missing and people keep going back to stored procs and straight data access.!

FH
06/11/2009 07:29 AM by
FH

Ayende,

Can you please shed some lights and explain how setting up UserType with encryption is going to enable HQL or criteria queries involving 'like' operator for string based columns?

Ayende Rahien
06/13/2009 03:15 PM by
Ayende Rahien

It should just work, I believe

basmin
08/02/2009 03:43 PM by
basmin

thanks for example to programmatically modify the configuration and mapping.

i'm a beginners and your post very useful for me.

thanks.

Comments have been closed on this topic.