Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question

migrated from stackoverflow.com Jan 9 '12 at 20:44

This question came from our site for professional and enthusiast programmers.

up vote 13 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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