3 ms·

There are security matters too though, which no-one ever seems to get right. Stored Procedures correctly managed are a more secure option than an ORM executing

There are security matters too though, which no-one ever seems to get right. Stored Procedures correctly managed are a more secure option than an ORM executing arbitrary SQL code, but the reason why is misunderstood consistently (I've had experienced DBAs get this wrong and insist to me that stored procedures prevent SQL injection attacks which is completely false). To pick an arbitrary example out of thin air: If my application requires 10 different calls to the database with variables, then I can create 10 stored procedures. I can set the permissions for the account that the calling application is using to only have EXEC privileges and only on those 10 procedures. This means that if the credentials were to leak the damage is limited to tasks the application could conceivably have performed anyway, albeit without any application-enforced validations on the variables passed to the calls. If I use an ORM, I have to give the SQL account the client application is using more-or-less carte-blanche access to the database as it could conceivable read from or write to anywhere in the database through arbitrary SQL. Sure, maybe I scope the ORM to a schema and restrict on that, but it's not nearly as granular and fundamentally misses the point that I've opened a massive attack surface unnecessarily by creating a SQL account with generally free reign on the database that exists in the application layer. On a recent project, mostly to see how practical it was, I built an application that used stored procedures with all of the validation being done directly inside the procedures in SQL. This had quite a few benefits: I only had to maintain validation in one place. I could rely on the formatting constraints I already needed in the database anyway. The validation rules were beside the data and were much faster to edit and maintain over time. The validation messages had to be pulled as keys making globalisation of the application much simpler later within the client application. Different strokes for different folks, but there are literally decades of reasoning behind why RDBMS systems are the way they are that is completely bath-watered by ORMs.

pbz 6y ago
"If I use an ORM, I have to give the SQL account the client application is using more-or-less carte-blanche access to the database as it could conceivable read from or write to anywhere in the database through arbitrary SQL" You can give table level permissions (and even specific columns if you want) to the ORM db account. From a security point of view there should be no difference.
- tonyarkles 6y ago
  I agree with both of you, although have never set up a DB in either style :) One small point: the proposed stored procedure approach seems, qualitatively to me, to be less error-prone. The consumer in the sproc approach either does or doesn’t have access to specific sprocs, while managing fine-grained per-column permissions seems easy to screw up in either a too-liberal or too-restrictive way.
mtcoope 6y ago
I have a few questions I'm curious about. 1) How are you unit testing all of this? 2) How are you source controlling all this? 3) I'm assuming you are not versioning anything but if so how are you? 4) How difficult would it be for someone else to come support this? Number 4 has been the nightmare I've seen with excessive stored procs.
- headmelted 6y ago
  The project is end-of-life now so most of this is moot but to answer these queries: 1) SQL calls in a separate command-line application. 2) Git. It’s in a .NET Database project. 3) See 2. 4) Not overly to be honest - the organisation uses SQL procs pretty extensively as is, so this isn’t anything particularly new or out of the ordinary other than the validations are held in a different part of the stack.

pbz 6y ago

"If I use an ORM, I have to give the SQL account the client application is using more-or-less carte-blanche access to the database as it could conceivable read from or write to anywhere in the database through arbitrary SQL" You can give table level permissions (and even specific columns if you want) to the ORM db account. From a security point of view there should be no difference.

tonyarkles 6y ago
I agree with both of you, although have never set up a DB in either style :) One small point: the proposed stored procedure approach seems, qualitatively to me, to be less error-prone. The consumer in the sproc approach either does or doesn’t have access to specific sprocs, while managing fine-grained per-column permissions seems easy to screw up in either a too-liberal or too-restrictive way.

mtcoope 6y ago

I have a few questions I'm curious about. 1) How are you unit testing all of this? 2) How are you source controlling all this? 3) I'm assuming you are not versioning anything but if so how are you? 4) How difficult would it be for someone else to come support this? Number 4 has been the nightmare I've seen with excessive stored procs.

headmelted 6y ago
The project is end-of-life now so most of this is moot but to answer these queries: 1) SQL calls in a separate command-line application. 2) Git. It’s in a .NET Database project. 3) See 2. 4) Not overly to be honest - the organisation uses SQL procs pretty extensively as is, so this isn’t anything particularly new or out of the ordinary other than the validations are held in a different part of the stack.