I have a client - a big pharmaceutical company - who is under pressure from an external auditor (government agency) to use only stored procs for their applications for security reasons. I haven't spoken with anyone directly there about it yet so I don't know what their specific security concerns are, but I believe they are worried about hackers getting control of a SQL login that has permission to base tables. Never mind the fact that a hacker could just execute stored procs instead. Sure it's a little more difficult, but not much.

My application uses LLBLGEn Pro 1.x (the version just prior to 2.0), and I'm wondering if there are suggestions out there as to how to handle this situation.

One thought I had was to upgrade to 2.0, then use the projection framework built-in to execute procs for everything. I have no experience with the new version yet though. Is this a feasible solution?

Any other solutions? A way to restrict ad-hoc query access if someone got a hold of the SQL Login? We're using Windows Authentication by the way, by having the ASP.NET Site run under a specific domain account, which is granted access to a database role.

Uh, wrote:

...I have a client - a big pharmaceutical company - who is under pressure from an external auditor (government agency) to use only stored procs for their applications for security reasons.... Any other solutions? A way to restrict ad-hoc query access if someone got a hold of the SQL Login?...

Ug. I feel your pain.

Thankfully, my LLBLGen projects have been proc-less.

However, as I understand it, one CAN use procs easily with LLBLGen 1. To do that, simply map the procs at generation time then call them from the entity objects. Lock down the database via SQL Server roles/user, assigning the permissions at the proc-level and disallow all others. Having done all that, if someone tries to use Quey Analyzer or LLBLGen-entities, or any other dynamic querying mechanism, then all such dynamic access should be disallowed by SQL Server itself. And so on.

(Details to be worked out in-the-field.)

Note that I have not done it that way; but, I believe it will work.

HTH.

Thank you.

-- Mark Kamoski

mkamoski wrote:

...as I understand it, one CAN use procs easily with LLBLGen 1. To do that, simply map the procs at generation time then call them from the entity objects.

Yes, I suppose that is a solution, though I'm hoping for one where I don't need to modify the LLBLGen generated code at all.

I should add that we actually have another layer on top of LLBLGen, which we're using as the "Business" layer, and LLBLGen is used inside this layer. In practice it's an Adapter pattern implementation. Our client was also nuts about making sure our asses were covered if LLBLGen disappeared overnight, or Holland was attacked by North Korea, or something like that. So we created a layer on top of LLBLGen to create a stable API of our choosing (read:LLBLGen). This does give us an opportunity to use stored procs instead of LLBLGen for CRUD, but the whole point of using LLBLGen was to get away from that kind of mind-numbing (and therefore error prone) coding.

Uh, wrote:

...I should add that we actually have another layer on top of LLBLGen, which we're using as the "Business" layer, and LLBLGen is used inside this layer. In practice it's an Adapter pattern implementation.... This does give us an opportunity to use stored procs instead of LLBLGen for CRUD, but the whole point of using LLBLGen was to get away from that kind of mind-numbing (and therefore error prone) coding.

I see it.

As is obvious, the bad news is that as soon as one is using a SP-based solution, then one inherits all of the awful baggage that comes with using SPs.

On a previous project, I had to use SPs and could not use an OR-mapper. I did NOT want to write a single line of SQL. What I ended up doing was writting a few simple CodeSmith templates to generate the stock SPs, SelectOne(), SelectAll(), SelectByKey1, SelectByKey2, SelectByKeyN, DeleteOne(), and so on. It wasn't that bad. The SPs were generated at the click of a button. Abstract classes were bound to the SPs; but, since the classes were also generated, that was not a problem. Concrete hand-written classes inherited from the Abstract classes. And so on. Not a new idea; but, it works. So, in your case, I would probably recommend using a code generator, like LLBLGen Templage Designer or CodeSmith or MyGeneration or any of the other fine products out there. Then, use LLBLGen to generate objects that bind to those SPs, and so on.

Just a thought.

HTH.

Thank you.

-- Mark Kamoski