Resource Governor Security
Resource Governor uses existing SQL Server security mechanisms such as authentication, permission levels, and ownership chains. This topic identifies aspects of configuring and using Resource Governor that you should consider to ensure that potential security issues are addressed.
Considerations
The following elements of Resource Governor design and implementation need to be considered to ensure that this feature is as secure as possible when you use it:
Permissions
Resource pool and workload group names
The classifier user-defined function
Permissions
The following permissions are required change or view Resource Governor settings:
To change the Resource Governor configuration, a user requires CONTROL SERVER permission. Permissions are checked when any of the Resource Governor DDL statements are executed.
To view the active configuration provided by dynamic management views, a user requires VIEW SERVER STATE permission.
We recommend that the ability to author or change Resource Governor configuration is given to experienced database administrators.
Resource Pool and Workload Group Names
All resource pool and workload group names are public-facing. When creating pools and groups you should choose names that do not disclose information about the nature of the applications that you are running on the server. For example, a workload group named CompanyPayroll provides an obvious indication of the nature and criticality of the applications that use the workload group.
The Classifier User-Defined Function
The classifier user-defined function (UDF) is stored in master database.
This function is similar to LOGON triggers in its design and implementation and runs after LOGON triggers as part of the login process. The function executes in the login context of the session making a request and classification must finish before a session is actually established. Therefore, all messages originating inside the classifier function that would normally reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.
Warning
Although this release of Resource Governor implements schemabinding to restrict the calls that can be made from within the classifier UDF, all the data returned by the function is not necessarily secure. For more information, see Considerations for Writing a Classifier Function.
Note the following aspects of the classifier user-defined function behavior:
Resource Governor runs this function as a part of classification in the context of the login user by default, or as a designated user if EXECUTE AS is specified in the function.
When Resource Governor runs this function as part of classification, it does not check EXECUTE permission on the classifier UDF. However, all objects referenced by the function are to subject to standard permissions checks, which may allow access based on the ownership chain.
Registering a function as a Resource Governor classifier does not affect its permission levels if it used outside the scope of Resource Governor classification.
Ownership Chains in Resource Governor
You can rely on the default schema-based ownership chaining or use EXECUTE AS to give a single user access to a schema when Resource Governor classification is running. The following code example and comments illustrate how ownership chaining works in Resource Governor.
Note
The following example assumes that SQL logins are enabled.
The following code creates schema users (SchemaUser1, SchemaUser2) that have access to master.
use master
go
CREATE LOGIN SchemaUser1 WITH PASSWORD='your password here';
CREATE USER SchemaUser1 FOR LOGIN [SchemaUser1];
CREATE LOGIN SchemaUser2 WITH PASSWORD='your password here';
CREATE USER SchemaUser2 FOR LOGIN [SchemaUser2];
go
The following code creates a user (NormalUser1) with default login permissions.
CREATE LOGIN NormalUser1 WITH PASSWORD='your password here';
CREATE USER NormalUser1 FOR LOGIN [NormalUser1];
go
The following code creates schemas (Schema1, Schema2) and maps them to the schema users that were created. It also creates a table (groupTable) for the schemas.
CREATE SCHEMA Schema1 AUTHORIZATION SchemaUser1
CREATE TABLE groupTable (uname sysname, gname sysname);
CREATE SCHEMA Schema2 AUTHORIZATION SchemaUser2
CREATE TABLE groupTable (uname sysname, gname sysname);
go
The following code adds values to groupTable.
INSERT Schema1.groupTable VALUES(N'NormalUser1',N'Group1');
INSERT Schema2.groupTable VALUES(N'NormalUser1',N'Group2');
go
At this point, Schema1 and Schema2 are owned by SchemaUser1 and SchemaUser2, respectively. The next code example creates a function that will be used to access Schema1 and Schema2.
CREATE FUNCTION Schema1.classifier() RETURNS sysname WITH SCHEMABINDING AS
BEGIN
DECLARE @n sysname
SELECT @n = gname FROM Schema1.groupTable WHERE uname = SUSER_NAME()
SELECT @n = gname FROM Schema2.groupTable WHERE uname = SUSER_NAME()
RETURN @n
END
go
The following code registers the preceding function as a classifier UDF. Note that SchemaUser1 does not have access permission to Schema2.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=Schema1.classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE
go
As a test, try to login as NormalUser1 from another client connection. Open the Windows Event Viewer. You should see a classifier failure entry in the Application log. NormalUser1 inherits access rights for Schema1.groupTable by ownership chaining from Schema1.classifier. However, Schema1 does not have permission to access Schema2.groupTable.
As another test, grant SELECT permission to SchemaUser1 for Schema2.groupTable.
GRANT SELECT ON Schema2.groupTable TO SchemaUser1
go
Login as NormalUser1. Once again you will see a classifier failure entry in the event log. This failure is caused because the server checks to see whether or not NormalUser1 has the SELECT permission, which is not inherited from SchemaUser1.
In the next code example, another classifier function is created. This time logins are given permission to EXECUTE AS SchemaUser1.
CREATE FUNCTION Schema1.classifier2() RETURNS sysname WITH SCHEMABINDING, EXECUTE AS 'SchemaUser1' AS
BEGIN
DECLARE @n sysname
SELECT @n = gname FROM Schema1.groupTable WHERE uname = SUSER_NAME()
SELECT @n = gname FROM Schema2.groupTable WHERE uname = SUSER_NAME()
RETURN @n
END
go
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=Schema1.classifier2);
ALTER RESOURCE GOVERNOR RECONFIGURE;
go
Because the new function runs in the context of SchemaUser1, and SchemaUser1 has SELECT permission on the Schema2.groupTable, the Schema1.classifier2() function will run successfully for the NormalUser1 login.
Login again as NormalUser1 and check the event log for a classifier failure.
Note
Because NormalUser1 is not granted EXECUTE permission on the Schema1.classifier2 function, NormalUser1 cannot run the function as an ad-hoc query.
For more information, see Ownership Chains.
Testing the Classifier Function
You should test and optimize your classifier function before using it to classify incoming requests. A poorly written function can render the system unusable by timing out and exposing configuration information. You can use a Dedicated Administrator Connection (DAC) to troubleshoot a classifier function while Resource Governor is enabled because this connection is not subject to classification. We recommend that you have the DAC enabled on the server. For more information, see Using a Dedicated Administrator Connection.
Note
If a DAC is not available for troubleshooting; the other option is to restart the system in single user mode. Single user mode is not subject to classification; however it does not give you the ability to diagnose the Resource Governor classifier while it is running.