Hi,

I am new to NHibernate and am before decision whether to use NHibernate or classic design Web/Database layer. We are using SQL Server 2005. Our Website contains very sensitive user information.

NHibernate
Pros
1 Easy to design, no SP writing
Cons
1 NHibernate will generate ad hoc queries - Security for ad hoc query can not be properly defined (only SELECT, INSERT, DELETE, ... can be allowed / denied for certain columns)
2 If somebody hacks, etc. our Web server she can generate any query e.g. query all sensitive user data, or delete tables; and query will execute successfully because NHibernate requires to have permissions for INSERT, DELETE, ...

Classic DB design (SP)
Pros
1 SP can have fine security permissions
Cons
1 Requires writing SP, etc.

What should you suggest me ?
Do you know some other pros / cons ?

Thank you ... :-)

If someone hacks your webserver, your last worry is what queries they'll generate, IMHO.

If there is security vulnerability in OS, Web server, etc. this can happen, it is not very probably but it is not impossible ...

If you use NHibernate 1.2 RC1, you can call SP's from NHibernate. If you keep the secure items accessible only through SP's you can use NHibernate for most of the data and only call SP's when you need to access the secure data.

However I'm not convinced that security for ad-hoc queries can't be properly defined, or that using SP's gives you a security advantage. If an SP accessed by NHibernate allows access to sensitive data, then the attacker will have that access if the web server is compromised. Since Sql Server 2005 allows setting select/insert/update/delete permissions per-object and per-column, you can set the permissions to exactly what would be allowed by the SP. Whether it's easier to manage permissions for SP's or database columns is debatable.

(edit- I just realized that the above isn't true. I can think of a few occasions when an SP would allow you to restrict access to data beyond normal permissions, but I personally have never had need of them)

If you want to restrict visibility of your db structure, maybe you could map persisted objects to views instead of tables?

One point here, you can use the named connections property of ASP.NET 2.0 to secure the database connection string; that way they have to hack the user name/password used to connect to the SQL Server not just the web server

http://msdn2.microsoft.com/en-us/library/ms998280.aspx

has anyone had an answer to this yet? I am running into the same problem.


[quote="paulh"]One point here, you can use the named connections property of ASP.NET 2.0 to secure the database connection string; that way they have to hack the user name/password used to connect to the SQL Server not just the web server

http://msdn2.microsoft.com/en-us/library/ms998280.aspx[/quote]

What about windows application?