1

I have a system that allows users to log in and construct queries to run against a SQL 2012 server. The system needs to be pretty liberal in what queries it allows, so I'm just relying on SQL grants to prevent users from doing things they aren't supposed to.

I'm worried that something like priority inversion could still occur. Say a unprivileged user logs in, finds a table to abuse and runs a dozen or so outer joins on it. Or simply begins a transaction and never closes it.

I want to ensure that unprivileged users cannot block critical processes from more privileged users. I'm thinking of using the Resource Governor to do this, but I'm not sure if this is the best way of if there are other issues I might need to worry about.

What's the best way to ensure that queries from unprivileged users cannot block critical processes?

p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
  • You may be able to limit the Command Timeout. Is there any valid use of a trans? If not then don't allow any query with a trans. Same with cursor. And what about REPEATABLE READ - that could do some real concurrency damage - do they need that? All you can do is limit your exposure if you are letting user TSQL through. – paparazzo Mar 11 '13 at 15:46
  • @Blam Yeah, I'd considered command timeout before, but that's a client-side feature. I'd prefer something on the server. I don't suppose there's any way to prevent a user from setting their isolation level directly. – p.s.w.g Mar 11 '13 at 15:54

2 Answers2

2

Resource Governor can only limit the resources of an arbitrary user (memory, CPU). It cannot prevent priority inversion, and if priority inversion happens the resource governor will only make it last longer.

If users can cause blockage then you must move the users to a safe playground (eg. a standby replica) or you must actively monitor for blockage. There's not much room to maneuver. If these users only do R/O operations then you can try use snapshot isolation to avoid blockage.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • +1 for the link. I never knew this feature existed. I also like the suggestion of a replica db, though I'm not sure this will work in my case. I'll have to research these options a bit more. – p.s.w.g Mar 11 '13 at 15:57
0

We've run into similar requirements and ultimately turned to database snapshots on our failover SQL Server 2008 boxes for unpriviledged users. That gave us some priority segregation and allowed us to make better use of our failover hardware.

Now that we're on SQL Server 2012, we're looking into AlwaysOn. It's not perfect, but if you have a HA environment, it's worth considering. There's a AlwaysOn whitepaper available at the bottom of this page: http://www.microsoft.com/en-us/sqlserver/solutions-technologies/mission-critical-operations/high-availability.aspx.

sqlfool
  • 238
  • 1
  • 2
  • 8