About the SHOWPLAN Permission
The SHOWPLAN permission is new in SQL Server 2005. To produce execution plan output by using most Showplan Transact-SQL SET options, users must have:
-
The SHOWPLAN permission on the databases that contain objects referred to in the Transact-SQL statement, such as views, stored procedures, or user-defined functions.
-
The appropriate permission to execute the Transact-SQL statement itself.
.gif) Security Note: |
|---|
Only grant the SHOWPLAN permission to trusted users because it might be possible to infer information about SQL Server objects from Showplan output.
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.
|
The SHOWPLAN permission is a database-level permission which:
-
Can be granted, denied, or revoked only by the following users:
-
Members of the sysadmin fixed server role. By default, all members of this fixed server role have the SHOWPLAN permission on all of the databases on the server.
-
Members of the dbcreator fixed server role for databases they create and thus own. By default, all members of this fixed server role have the SHOWPLAN permission on databases they create and thus own.
-
Members of the db_owners fixed database role for databases they own. By default, all members of this fixed database role have the SHOWPLAN permission on databases they own.
-
Supports ownership chaining. When the ownership chain is broken, the permission is checked again at the node where the break occurred. However, because the SHOWPLAN permission is a database-level permission, this check only occurs when queries reference objects in two or more databases. For more information about ownership chaining, see Ownership Chains.
For information about the syntax used to grant, deny, or revoke the SHOWPLAN permission, see Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission.
Example
If User1 has CREATE TABLE, INSERT, and SELECT permissions, and he creates table T (he is the table owner) in database D, inserts rows into the table, and then writes a SELECT query on the table, the query executes successfully. However, User1 is not able to generate a Showplan until he is granted the SHOWPLAN permission on database D.
Caveat
In the previous example, suppose that database D contains view V for which User1 has SELECT permission. After User1 has been granted the SHOWPLAN permission for D, although he does not own V, he can still generate a Showplan on a query posed to V. This Showplan enables him to see the view definition for V, including the tables and views on which V is based. However, if V contains an object, such as a table, that is owned by User1 and which exists in a different database, D2, and User1 is not the owner of D2, the SHOWPLAN permission on D2 is checked and required.