Resource Governor Security

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.

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 (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. 

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.

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.

For more information, see Ownership Chains.

Testing the Classifier Function