3 ms·

I have experimented with a design that only allows the application to access a set of stored procedures via it's SQL user. It seems to work, if you write compl

I have experimented with a design that only allows the application to access a set of stored procedures via it's SQL user. It seems to work, if you write complicated enough stored procedures you can enforce things like each entry must be created by a logged in user or whatever. Then the application is limited to interactions it's supposed to have, so it can't drop a audit table or something. I even did login via a stored procedure, confirming the hashed password matched and generating a bearer token that future stored procedures require to function.

tialaramex 5y ago
My current project is maintenance for a large system built of shared procedures. In principle this feels like a reasonable strategy but chances are your stored procedures do not enjoy the same creature comforts as your "real" software. In my case they've got commented out code blocks, procedures with names ending V3.bak.tmpMar14.old, randomly different styles and formats from one to the next, and inconsistent naming (AllGroups allGroups or ag?). All of which would be quickly and easily prevented or retrospectively corrected in say C# or Java but there's no easy way to do it with the archaic T/SQL setup here.
- willcipriano 5y ago
  I agree it would require a great deal more discipline to implement then the alternatives. I can do it by myself, just takes about twice as long to do over the normal way, but with a team it would be a nightmare.
- akersten 5y ago
  I'm unfamiliar with T/SQL but of those issues seem like fundamental limitations of stored procedures, just implementation details that need updating. Why is there no way to do it?
  - tialaramex 5y ago
    Of course, everything is possible, but resources are limited and so it matters that it's harder and requires ongoing effort to keep this right. Edited to add: I retrospectively realised the above comment had an auto-correct mistake where "easy way" had been misscorrected to "way way" and so it wasn't clear I meant only that it's needlessly harder

tialaramex 5y ago

My current project is maintenance for a large system built of shared procedures. In principle this feels like a reasonable strategy but chances are your stored procedures do not enjoy the same creature comforts as your "real" software. In my case they've got commented out code blocks, procedures with names ending V3.bak.tmpMar14.old, randomly different styles and formats from one to the next, and inconsistent naming (AllGroups allGroups or ag?). All of which would be quickly and easily prevented or retrospectively corrected in say C# or Java but there's no easy way to do it with the archaic T/SQL setup here.

willcipriano 5y ago
I agree it would require a great deal more discipline to implement then the alternatives. I can do it by myself, just takes about twice as long to do over the normal way, but with a team it would be a nightmare.
akersten 5y ago
I'm unfamiliar with T/SQL but of those issues seem like fundamental limitations of stored procedures, just implementation details that need updating. Why is there no way to do it?
- tialaramex 5y ago
  Of course, everything is possible, but resources are limited and so it matters that it's harder and requires ongoing effort to keep this right. Edited to add: I retrospectively realised the above comment had an auto-correct mistake where "easy way" had been misscorrected to "way way" and so it wasn't clear I meant only that it's needlessly harder