Are there any risks to granting users SQL Server SHOWPLAN permission?

Posted on

Question :

I’m doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, “Show Execution Plan” has been the most effective way to understand the performance of individual queries and procedures.

What risks are inherent on granting this permission? Is there a legitimate justification for this restriction on a development copy of the database?

Note: This SQL IT group has 200+ databases under a single SQL Server instance. Thanks.

Answer: I am taking the lack of response to mean that there are no significant security risks, other than the one noted below. Basically, restricting this on a development database is counterproductive.

I will update this if anyone comes up with a better answer. Thanks for your comments!

Answer :

Take a look at Showplan Security in Books Online, which says:

Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission
can view queries that are captured in Showplan output. These queries
may contain sensitive information such as passwords. Therefore, we
recommend that you only grant these permissions to users who are
authorized to view sensitive information, such as members of the
db_owner fixed database role, or members of the sysadmin fixed server
role. We also recommend that you only save Showplan files or trace
files that contain Showplan-related events to a location that uses the
NTFS file system, and that you restrict access to users who are
authorized to view sensitive information.

For example, consider the following query:

SELECT COUNT(*)  FROM table_1  WHERE column_1 < 10 

If a malicious user produces Showplan output for a set of queries like
this example, and replaces the value “10” in the predicate with
different constants each time, the user could infer an approximate
data distribution of the column values for column_1 in table_1 by
reading the estimated row counts.

This risk does seem negligible, especially given this is a development server, where all queries would be from devs and IT people, not users.

Leave a Reply

Your email address will not be published. Required fields are marked *