OK, bit late I know, but I'm doing more research on CSLA 3.8 inc. reading more of the 2008 book. I'm thinking about how to change our (outdated, CSLA 3, ported from CSLA 2) architecture to support unit testing. This will include updates to our CodeSmith Templates (VB, so maintained by us as more and more people seem to be shunning VB and the templates seem not to be available/up to date.)
The more I read about CSLA's direction, the more I realise that more and more people are using ORMs to do the data access, including using the objects that Linq to SQL or EF generate as DTOs when separating data access from the business objects to support mocking.
I have consciously made the decision not to use ORMs because of the security implications. Of course I see the benefits, don't get me wrong. But the idea of opening up permissions on the base tables in your database simply doesn't fit with the concept of defence in depth. I used to have full permissions on tables in Access databases. That was proven not to be a good plan a time or two, but was always frowned upon by security experts and I was persuaded to change my thinking. Fast forward just 4 or 5 years and the industry is being engulfed by a tide of ORMs that simply forget this teaching.
I am not convinced that most developers are considering the security implications of ORMs sufficiently. Have people had any experiences that convince them that security experts are going along with the tide in respect of ORMs? It seems to me that instead developers are deciding to ignore them (including DBAs who are being talked of as dinosaurs); a case of he who shouts loudest wins.
Rocky, I note that you have posted about a number of projects where Magenic have used ORMs. Have you put in place any compensating controls to stop people being able to use Microsoft Query, or Access, or whatever, to connect directly to database servers and query/modify data at will? Do you always recommend an application server when using an ORM? Is the situation different for web and LAN development environments, perhaps with people just not doing LAN much any more (we do a both, although web is more prevalent)?
There is another way to achieve developer productivity, and that is static code generation. We can generate the majority of SPs and objects very quickly with our templates and retain productivity whilst denying access to all base tables. Obviously the result needs some tweaking sometimes, but that is to be expected. However, not using an ORM leaves me without DTOs for use between layers unless I generate those too, and the amount of code/number of places that would need changing when a property is added is worrying me.
Is it REALLY OK that developers turn their backs on database security? Or are some developers being attracted by shiny and new and forgetting the important security lessons learned from the past?
Your thoughts, particularly real world situations, will be gratefully received.
My thoughts on the matter...
I wouldn't necessarily agree that all ORM's simply ignore database security. EF allows you to map your entities to stored procedures if you wish, instead of directly to the tables. My ORM experience is a little limited, but I believe NHibernate does this as well.
A lot depends on your situation, but in web environments, "direct access" to the database tables doesn't have to be as much of an issue. If your website is an Intranet site, you can set up your site so that the database connection is via the network account that manages your site's application pool. So your users have no direct access to the database, and don't know the ID that is connecting. On publicly-facing sites (or any site that requires a login), you have more work to do to implement this solution, but it's still possible.
Desktop situations do provide some potential issues. But again, EF allows you to map your entities to stored procedures if you so desire. And there is always the app-server concept, as you've described. The trend I see in desktop apps is more of an "occasionally connected" type of situation - something like Outlook, where the desktop app only connects to a local data store, and a separate process talks to the database. This provides a level of isolation as well.
HTH
- Scott
I agree with Scott. I use Linq To Sql, and map every objects CUD to stored procedures. Some procedures manuipulate data in multiple tables if they are doing a certain task (such as invoice canceling, which mainly involves copying rows and updating other rows). The procs are locked down appopriately by limiting their execution to certain database roles, and logons are actually AD groups, not individual users. So including Linq to Sql hasn't changed how I do things at all; except that fetch records and modifying them is more initutive (create objects, set properties which include some object to object ones and you're done).
OK, very interesting, thanks for the responses.
So it looks like some people are using SPs for create/update/delete but allow read access to tables to allow simple querying. Perhaps it is also possible to use SPs for fetch, but then you lose all the benefits of using an ORM really.
Thoughts continue ...
I wouldn't say you lose all the benefits of an ORM if you are 100% SP. For what it's worth, I work in a shop that until recently forced all database access via stored procedures. However, that attitude is beginning to change, and we had discussions with our DBA's before we began to do that. We have the luxury of being primarily a web-development shop, and my team is almost exclusively intranet apps, which makes it even simpler. But even the guys who do the internet sites use L2S, and they don't have any huge heartburn over database access and security. They require more setup than I do, but once you figure it out it just becomes a piece of your development pie. And you'd have to go through most of that even if you weren't using an ORM.
An ORM's primary goal is to provide a more "object-centric" method of talking to your database, without having to concern yourself with the particulars of how that discussion happens. The fact that you're using stored procedures instead of whatever SQL your ORM of choice builds is, in some ways, incidental. Yes, we all know that it's not 100% insulated from the particulars of interacting with a database. But it's still a good tool, and IMHO working with entity objects is a lot easier than working with DataTables/DataSets (even typed ones).
And, like Andy, I still use SP's for the more complicated processes, or for some "direct delete" situations (I see no need to load an object entity just to delete it from the database.) I use EF, and like L2S it just maps the SP to a function call. Again, I don't have to care about all the plumbing involved with talking to the database.
Yes, you could argue that a 100% SP solution means you have to go through more work in your ORM, and that's true. But that's largely a one-time cost which I believe is outweighed by the benefits. If I can let the tool build my DAL (which is essentially how I use the ORM entities), and use other OO techniques to get data into and out of my business objects - well, I think I'm money ahead.
HTH
- Scott
I don't think it's all that different with the desktop, either.
We've supported an ODBC application for years (just now getting CSLA/ADO.NET version into production) in which the connection credentials were not available to the end user. So end users could access the database only via the application, not directly, and the application level security was deemed adequate for protecting the database. This approach translated to CSLA perfectly (and we incidentally also bolted CSLA on top of an ORM, albeit a lesser known one).
Copyright (c) Marimer LLC