Showplan Security
Showplan execution plan information can be produced by various ways. You can use Transact-SQL SET statement options, SQL Server Profiler event classes, or you can query the dynamic management function sys.dm_exec_query_plan. Each method requires a different set of permissions, which are described in the following sections. For more information about how the SHOWPLAN permission is checked for Transact-SQL batches, see SHOWPLAN Permission and Transact-SQL Batches.
Note
When a SQL Server 2005 database compatibility level is set to 80 by using the sp_dbcmptlevel stored procedure the current SHOWPLAN permission still applies. Setting the compatibility level to 80 does not produce the Showplan permissions behavior of Microsoft SQL Server 2000.
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 "ms189602.security(en-US,SQL.90).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.
Permissions Required to Use Showplan SET Options
The permissions required to use the various Showplan SET statement options are listed in the following table:
| Showplan SET options | Permissions required |
|---|---|
SET SHOWPLAN_XML ON SET SHOWPLAN_ALL ON SET SHOWPLAN_TEXT ON |
For SELECT, INSERT, UPDATE, DELETE, EXEC stored_prodedure, and EXEC user_defined_function statements, the following permissions are required to produce a Showplan:
For all other statements, such as DDL, USE database_name, SET, DECLARE, dynamic Transact-SQL, and so on, only the appropriate permissions to execute the Transact-SQL statement are needed. For more information, see SHOWPLAN Permission and Transact-SQL Batches. |
SET STATISTICS XML ON SET STATISTICS PROFILE ON |
For Transact-SQL statements that do not produce STATISTICS PROFILE or STATISTICS XML result sets, only the appropriate permissions to execute the Transact-SQL statements are required. For Transact-SQL statements that do produce STATISTICS PROFILE or STATISTICS XML result sets, checks for both the Transact-SQL statement execution permission and the SHOWPLAN permission must succeed, or the Transact-SQL statement execution is aborted and no Showplan information is generated. For information about which Transact-SQL statements produce Showplan information, see Transact-SQL Statements That Produce Showplans. |
SET STATISTICS TIME SET STATISTICS IO |
Neither of these SET statement options check for or require the SHOWPLAN permission. |
When Is the SHOWPLAN Permission Checked?
The SHOWPLAN permission is checked when a Transact-SQL statement or batch executes and Showplan information is generated. The check does not occur when a Showplan SET option is set to ON.
Note
The context database for a Transact-SQL batch is set by using a USE <database_name> statement. The SHOWPLAN permission is not checked on USE <database_name> statements and is not checked on the context database.
For more information about the Showplan SET statement options, see the following topics:
- SET SHOWPLAN_XML (Transact-SQL)
- SET SHOWPLAN_ALL (Transact-SQL)
- SET SHOWPLAN_TEXT (Transact-SQL)
- SET STATISTICS XML (Transact-SQL)
- SET STATISTICS PROFILE (Transact-SQL)
- SET STATISTICS TIME (Transact-SQL)
- SET STATISTICS IO (Transact-SQL)
Permissions Required to Display Graphical Execution Plans by Using SQL Server Management Studio
The permissions required to display graphical execution plans in SQL Server Management Studio are listed in the following table:
| Management Studio Execution Plan option | Permissions Required |
|---|---|
Display Estimated Execution Plan |
Requires the same permissions needed to use the SHOWPLAN_XML SET statement option |
Include Actual Execution Plan |
Requires the same permissions needed to use the STATISTICS XML SET statement option |
For more information, see Displaying Graphical Execution Plans (SQL Server Management Studio).
Permissions Required to Display Execution Plans by Using SQL Server Profiler Event Classes
To display execution plans by using SQL Server Profiler event classes, users must be a member of the sysadmin fixed server role, or be granted the ALTER TRACE permission. The SHOWPLAN permission is not checked nor is it required.
For more information, see Displaying Execution Plans by Using SQL Server Profiler Event Classes.
Permissions Required to Display Execution Plans by Using the sys.dm_exec_query_plan Dynamic Management Function
To display execution plans by using the sys.dm_exec_query_plan dynamic management function, users must be granted the VIEW SERVER STATE permission only.
For more information, see sys.dm_exec_query_plan.