User Access Checking for Rights on Particular Database Objects or Records

Question

I'm having a friendly debate with a developer about a situation where users are logging in and accessing documents in a web application. When we load the document for the user to view, we have the userID in session and the documentID that may be passed via QueryString.

To prevent the user from modifying the documentID on the QueryString, I propose that the stored procedure that loads the document take the UserId as a parameter to validate rights to the document.

My developer friend suggests that we run a separate procedure to determine access rights to the document earlier in the page and just run a procedure to grab the document when the document should be shown.

Are we missing something? Which is most efficient and safe? I thought passing the UserId with the DocID into one procedure call to check rights and pull the document was a more efficient solution.

Answer 1

The userID should be a session variable. Right. Pass the documentID in the querystring. Yup.

Assuming the documents are stored in the database, I would have a table for permissions: a recordID a userID and a documentID. You do a join with this table when calling up the document. If you don't get a result, you don't get the document. Index it all nicely and it will be fast.

Answer 2

I propose that the stored procedure that loads the document take the UserId as a parameter to validate rights to the document.

I think this is the way to go. If for no other reason than it's safer. If you reuse this proc, and then forget to check the access - you've opened a big hole. This way it's apparent and baked in that you can't get to the doc unless you have access.

Answer 3

Strictly from a performance perspective, passing the UserID along with the DocumentID into one stored procedure would be best. You only have one round trip to the database server. Also, as others have pointed out, if you will be retrieving this document from other pages or applications, if you use the same stored procedure, you insure that you are not bypassing the security to do so.

However there are scenarios where having dedicated security verification stored procedures makes sense. If you have other resources that you want to protect besides documents, and your verfication code isn't trivial, you may not want to duplicate the verfication code in every stored procedure in your database. In that case it may make sense to move the security infrastructure out to your data access layer, and have the data access layer make the db call to authorize access prior to retrieving the requested resource. If you take this route, you don't want to rely on the developer to always have to remember to make an authorization db call prior to requesting a resource.