You have an application with a Users table, and the Users table has various columns to indicate what rights a User has within the application.

Ignoring for the purposes of this question what should be done in server-side app code to implement user rights authorization, I am wondering from a dba's perspective (and security perspective) whether it is a good idea or an anti-pattern to include validation of the user's rights inside of the stored procedure.

For instance, imagine a generic blog or CMS app. It has a stored proc where the UserID is one of the parameters to the proc. The Users table has a column to flag whether the User is can be an Editor in a certain section of the CMS. The stored proc then includes a step to verify against the Users table that yes, given this UserID, this UserID has the the Users.Editor flag set to true, so this user can go on with the rest of the stored proc; if the user has the flag set to false, then the proc has logic to not let him continue.

Should the server-side app code (e.g., .NET) handle all of this, or is adding a check inside the proc, even if somewhat redundant, still worth doing?

Part of the reason I am curious is because beyond this article, in which the author says, "...role checks inside stored procedures is nuts...", I am not able to find much about the subject. http://codebetter.com/jeremymiller/2005/07/05/good-and-evil-in-the-garden-of-stored-procedures/

  • My opinion is this should be closed as primarily opinion-based. It's not really clear what your main concerns are (granted, I haven't read the blog post you've referenced). It does seem odd to check that the user can run the procedure, which is something that's usually handled with permissions on the procedure itself; however, I don't know what you really mean by user. If it is a user of the app and is completely self-contained within the database, then I don't really see the problem with validating that the user can do the thing you're about to let them do... – Aaron Bertrand Apr 25 '14 at 16:07
  • @AaronBertrand - I have updated the question in an attempt to make it clearer. – mg1075 Apr 25 '14 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.