OR mapping and stored procedures.

[Durkin]

Hi. I have written a persistence layer which maps an action on an object to a stored procedure eg. calling Save on an object will call then spSaveObject proc populating the correct parameters with members of the object and filling the members with any data returned. I've been reading up on OR mappings to see if I can improve my layer and most of the articles recommend that the mapping layer creates dynamic SQL for each action and executes it directly on the tables involved. I can see a lot of advantages to this approach, the most obvious being not having to maintain stored procs. However, this approach also bypasses one of the main reasons for using stored procs, namely security. The application user will have access to the full set of data in the tables. Not through the application of course but a connection directly to the database by a malign (or just nosey) user could cause havoc. Am I wrong worrying about this? Should I assume that security policies in the enterprise will not allow this to happen? Is dynamic SQL once again acceptable?

Durkin

 

[chrissie1]

ah, finally someone with an intelligent question. And yes I'm with you in that you should use SP's.

The reason why these people that make the OR-mapping things don't use them is because it becomes more complex and you have less control for some reason. I would recommend using OP. But when you use hibernate, CMP/EJB, nhibernate or some of the others you will find that support for SP's is flacky because they like to keep it simple I guess.

And what else is the DBA going to do. We should keep them busy .

just my 2c

Christiaan Baes
Belgium

"My new site" - Me

 

[chrissie1]

ah, finally someone with an intelligent question.

Did I forget this? ;-)

Christiaan Baes
Belgium

"My new site" - Me

 

[chiph]

If security is a concern, you can always create a separate DB login for that application, and then restrict what it's able to do.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[dragonwell]

There are a couple of O/R Mappers that I know of that fully support stored procs:

iBATIS and the Wilson ORMapper. I like Wilson because by default it uses dynamic sql but you can override this and use stored procs whereever necessary. With iBATIS I am pretty sure you have to write all your queries or sps yourself, and it more or less does the "mapping" part.

If you're really interested, there is a gross abudance of discussions on this topic over at forums.asp.net

Happy New Year

[blue]_______________________________________[/blue]
Business Logic:"AND when tweetle beetles battle with paddles in a puddle, they call it a tweetle beetle puddle paddle battle AND..." - Dr. Suess