Is there any risk involving in granting showplan permission to a dev group in devleopment UAT databases?

Question

Is there any risk involving in granting showplan permission to a dev group in devleopment UAT databases?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 26, 2016 at 11:33 am

#324269

Is there any risk involving in granting showplan permission to a dev group in devleopment UAT databases?
Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Absolutely not. They *should* have that permission, because you want devs to learn to tune their queries. As a DBA, you'll have your hands full with the beasts that are beyond their skills, so let them have a go at tuning the easy ones.

Even on a prod server I would not bother too much about this permission. In a well-designed system, the execution plans contain no real data. In a real system, you probably have a few dynamically generated queries where results from query 1 are hardcoded into query 2 and then visible in the execution plan so under extreme circumstances this could form a privacy breach, but on UAT where you have no sensitive data anyway that should not be a concern at all.

EDIT: Just remembered that even on a well-built platform you might be able to glimpse some real data by looking at the compile-time value and run-time value of parameters; those often come from other queries or from data entry and might be sensitive. So perhaps some restriction on prod is called for after all.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

I agree with Hugo, although I'd be a bit more concerned about production access because of what he notes at the end.

However, in dev, heck yes. Get them access. Get them knowledge!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Hi

I got the below note in the associated Microsoft URL.

https://msdn.microsoft.com/en-us/library/ms187611.aspx

Users who have the SHOWPLAN, the ALTER TRACE, or the 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. Additionally, we 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.

Thanks.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

SQL-DBA-01 (1/26/2016)
Hi
I got the below note in the associated Microsoft URL.
https://msdn.microsoft.com/en-us/library/ms187611.aspx
Users who have the SHOWPLAN, the ALTER TRACE, or the 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. Additionally, we 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.

The VIEW SERVER STATE and ALTER TRACE permissions give access to a lot more than just the plans. If all you're looking at is SHOWPLAN, I don't know of anyting other than what's already been said.

Further, you don't need to grant anything to logins with the sysadmin server role. Those logins have permission to do whatever it is they try to do. Keep a real eye on the logins who have sysadmin privs, especially in production. They should be DBAs only.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

SQL-DBA-01 (1/26/2016)
Is there any risk involving in granting showplan permission to a dev group in devleopment UAT databases?

In stark contrast to what some of the the others have suggested, I say "yes", that's a risk and a problem. Is it a development database or a UAT database? Developers should only have access to development databases, not UAT databases. UAT should be a separate database and it should be protected just like prod because it's one of the last steps before you get to prod.

Granting showplan permissions to Developers on a UAT database suggests that the Developers have the ability to run code on the UAT box and they should generally not. Any privs on that box should only be temporary and only if there's a problem that cannot be duplicated in Dev.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)