Clever Geek Handbook

SQL Server NOLOCK for queries is started for authorization

During our login, several requests are launched to validate the login. While evaluating them, I noticed that one of the queries is fired without a NOLOCK prompt.

There doesn't seem to be any particular danger of dirty reads, because the data is unlikely to ever change.

Thinking about it from attempting a DOS-like attack where someone tries to login fail over and over again, my guess is that the lack of NOLOCK lowers our threshold for failure.

I believe this is an extremely unlikely outcome of a DOS attack (I think the web server will go first), but adding a NOLOCK should make it very unlikely to impossible.

So, am I excessive or trivial?

0


source to share


5 answers


Having NOLOCK or not is the least of your problems with a DoS attempt on your server.

I wouldn't sweat.



If, as you say, the data rarely changes, having a NOLOCK there probably won't hurt.

+3


source


Yes, you are overly trivial.



If you are exposed to DOS attacks, NOLOCK on the SQL authorization call is the least of your concerns. Implement some DOS detection, crash tracking + throttle, even some scheduled pauses that won't affect the user but slow down the attack ...

+2


source


Also a very rare case, but still: at the moment someone deactivates the user so that he cannot log in, NOLOCK allows them to log in. Could there be a rogue user / hacker / employee who should be blocked immediately?

You must be concerned about this particular scenario to give up the performance benefit of NOLOCK.

0


source


NOLOCK can potentially improve your performance if you call this query frequently in broader transactions.

Consider the nature of dirty reading - will the timing of this happen really be critical? for example where you add or remove someone from an authorized role.

In an add script, a dirty read will fail in this attempt. (Access is denied)

In a delete script, dirty read will work on this attempt. (access granted)

If the data is changed by manual control, for example. human interaction - their margins for "latency" are usually much higher / uncertain than your databases!

0


source


You would be better off protecting your table by looking at its permissions. A table like this should not allow direct access, all access should be from the stored procs and the permissions set on them.

0


source






More articles: