Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

1  
Why didn't you test performance on realistic data before deploying to production? –  gbn Jan 9 '12 at 20:49
9  
It's a development server. Your devs should be able to perform the digital equivalent of setting it on fire and throwing it out the window. Not giving them SHOWPLAN is insane. –  Ben Brocka Jan 9 '12 at 21:12
1  
I agree with Ben that you should have full access on a development server. I guess the DBAs balk at this because it is close to view_server_state, is given with dbOwner and generally infers many rights. –  user5094 Jan 10 '12 at 13:34
2  
If this really is a development server then your I.T. people need a kick in the head. You can't tune queries if you can't see what the optimiser is doing. There is no excuse for their policy and it is almost certainly hurting the performance of their production server if people are deploying SQL code that they can't tune onto it. Beatings all round. –  ConcernedOfTunbridgeWells Jan 10 '12 at 13:38
1  
Hey Raoul, I moved the quote block that contains the answer from the body of your question to the body of the answer that referenced it. It is more appropriate there. –  Nick Chammas Jan 10 '12 at 16:56

1 Answer 1

up vote 6 down vote accepted

Take a look at http://msdn.microsoft.com/en-us/library/ms189602.aspx:

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.

share|improve this answer
    
Especially negligable that, given this is a development server, all queries would be from devs and IT people, not users. –  Ben Brocka Jan 11 '12 at 14:05
    
@BenBrocka I agree. –  user5094 Jan 11 '12 at 14:13

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.