SQL Server NOLOCK on queries run for authorization

Question

During the course of our application login there are several queries ran, all around validating the login. In evaluating them I noticed that one of the queries is run without the NOLOCK hint.

There does not seem to be any particular danger of dirty read because the data would hardly ever change.

Thinking about it from an attempted DOS type attack by somebody attempting failed logins over and over again I am suggesting that the lack of NOLOCK lowers our threshold for failure.

I believe it is an extremely unlikely result of a DOS attack (I think the web server would go first) but adding NOLOCK should make it go from unlikely to impossible.

So, am I being excessive or trivial?

Answer 1

Having NOLOCKs or not is the least of your worries with a DoS attempt against your server.

I wouldn't sweat it.

If, as you say, the data rarely changes, having the NOLOCKs there probably don't hurt.

Answer 2

Also a very rare case, but still: Just at the moment somebody deactivates the user to prevent them from logging in, NOLOCK lets them in. Could be a rogue user/hacker/employee who needs to be locked out immediately?

You would have to be concerned about this particular scenario to forgo the performance advantage of NOLOCK.

Answer 3

The NOLOCK could potentially improve your performance if you call that query frequently especially in broader transactions.

Consider the nature of the dirty read - would the time window where that can occur be truely critical? e.g. where you add or remove someone from an authorized role.

In the add scenario a dirty read would fail on that attempt. (access denied)

In the remove scenario a dirty read would work on that attempt. (access granted)

If the data changes through manual operation e.g. human interaction - their margin for "latency" is typically much higher/indeterminate than your databases!

Answer 4

Yes, you are being excessively trival.

If you are exposed to DOS attacks, NOLOCK on the SQL authorization call is the least of your worries. Implement some DOS detection, failure tracking+throttle, even some planned pauses that wouldn't effect the user but would slow down an attack...

Answer 5

You would protect your table far better by looking at the permissions to it. A table like this should not allow any direct access, all access should be from stored procs and the permissions set on them instead.