13

This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the permission to.

Now of course I understand "least permissions", and why you wouldn't want to just grant it to anybody, but I can't find any guideance on how to evaluate whether it SHOULD be granted or not.

So, my question: What are the security and performance implications of granting a user "View Server State" permission. What can they do that they maybe shouldn't be allowed to do...

Update: one implication is that the user will be able to use DMV's to look at queries. If the queries or query parameters can contain confidential information that the user wouldn't otherwise be able to see, allowing VIEW SERVER STATE would allow them to do so (i.e. dob = or ssn =).

5

There are no significant performance issues that I can think of from granting this permission. From a security perspective, you run the risk of letting a user see what you most details about your weak spots, so for example, a malicious user could view your most common wait stats are, which could help them target a DoS attack against your server.

Is this possible? Definitely. Is this likely? I'm compelled to say No, but remember that it is estimated that 90 percent of attacks against companies are from internal attackers.

| improve this answer | |
3

As an administrator you would view this information as being in your domain (performance / index usage / etc) but there are potentially compelling reasons that a development organization would want this information for a large legacy system they support- identifying zombie tables that are only touched by maintenance processes for example.

In the end it always ends up being an issue of "luck and generosity" since the call on whether any particular request is justified ends up being a soft choice and not a crisp formula. The use of best practice patterns without looking at context is itself a pretty nasty anti-pattern and reality is that many approach their positions with "talk to the hand" as a starting point.

| improve this answer | |
1

Regarding performance implications, I am not aware of any for this or any other permission.

Regarding:

What can they do that they maybe shouldn't be allowed to do

Simply put, they can see things that maybe they shouldn't be seeing. And don't think of this in terms of just SQL Server. This particular permission also governs DMVs such as sys.dm_os_sys_info and quite a few others that provide insight into the host machine (hardware, services, etc). You don't always know what info can be used against you. And, even if you are ok with someone seeing everything allowed by this permission now, sometimes DMVs are added in Service Packs / Cumulative Updates, and so maybe a new piece of info gets exposed that you aren't aware of.

I can't find any guidance on how to evaluate whether it SHOULD be granted or not.

Since you already mentioned giving people the minimum permissions needed, what this really comes down to is: does someone need this permission for ad hoc usage? Meaning, does someone need the flexibility of coming up with their own queries? Would creating one or more stored procedures and/or multi-statement TVFs work? If so, then you don't need to grant permissions any user (who is then free to anything allowed by that permission), and instead you grant the permissions to the code (which only does what it is coded to do). Module Signing is how you accomplish this. The general concept is:

  1. Create the stored procedure(s) and/or multi-statement TVF(s) to perform the desired action(s).
  2. Grant EXECUTE on these modules to whatever user and/or roles need to perform these actions
  3. Create a certificate
  4. Sign the module(s) using that certificate (using ADD SIGNATURE)
  5. Copy the certificate to the [master] database (i.e. create a certificate in [master] using the public key of the certificate used to sign the module(s).
  6. Create a login from the certificate copied to [master]
  7. Grant whatever instance-level permissions are necessary to that certificate-based login (which can include adding it to instance-level roles).

For some examples, please see:

| improve this answer | |
0

It's a security problem. You can never go wrong if you follow the Principle of Least Privileged. In other words, if an authenticating principal doesn't need a particular permission, then don't give it to them. Do you give out information regarding the type of locks on your door to other people that don't need to know that about your house? I would hope not. They probably wouldn't do anything, but it's still not prudent.

If we based data principles off of luck and generosity, we'd be in bigger trouble quite a bit more often. Security is an aspect where you should only grant when you can defend why you granted. You're simply giving somebody more information than they need to know. Don't do it. Server state is still sensitive.

| improve this answer | |
  • 1
    Who says they are giving it away needlessly? The OP might need to grant it to someone to investigate a specific issue (e.g. to look at sys.dm_db_missing_index_details) and they want to know what exactly the risks are of doing so. – Martin Smith Oct 23 '12 at 21:34
  • I guess I'm missing the mark with this question, I don't see anything in the question that indicates the necessity for the permission. – Thomas Stringer Oct 23 '12 at 21:48
  • 4
    @ThomasStringer: the question isn't about necessity, it's about risk. To put it in monetary terms, you may know what additional risks this would expose your servers to, and so be able to say no to a penny, and yes to a million dollars. I don't, but I want to. – jmoreno Oct 24 '12 at 1:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.