+972 52-548-6969
NHibernate – Get Thou Out Of My Database
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:
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
Any NHibernate/DDD introduction tutorial should come with this naming convention by default. THEN if you REALLY need to, you opt out.
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..
Oh dear, wouldn't a simple ROT13 be safer and still get the point across?
Marc,
Why, this is following the naming convention as laid out. :-)
nitpicker corner is a great idea btw - it's so often necessary
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!
Configurator,
Yes, I did.
And I am absolutely shocked(!) that you even considered the possible implications of using this
SHODAN -> I simply have to guess... Visma?
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.
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?
And still HSQL should work for operators = and like etc.
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."
FH,
You have the first part here.
The second is just setting up UserType with encryption
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?
They wanted to touch my database
If you were using Hibernate - http://www.jasypt.org/
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.
I think this will NOT protect you from a really really nasty integration team.
That looks very much like the AS400/DB2 tables I have to get data from every day. I feal right at home.
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 Method_x123 and Field_x2342 ? (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!
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.
ORM Messiah,
I guess you missed the nitpicker corner.
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.
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.
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.
And you are still missing the nitpicker corner, amazing.
And that wasn't in my team, it was another team totally unrelated to me.
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.!
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?
It should just work, I believe
thanks for example to programmatically modify the configuration and mapping.
i'm a beginners and your post very useful for me.
thanks.
Comment preview