SQL Server Modeling Services Security
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
The SQL Server Modeling Services expands on the robust security model for SQL Server 2008 through the use of Modeling Services Folders, updatable views, and a claims-based security model.
Modeling Services Folders
Folders act as the primary security boundary in the Modeling Services database. Folders organize instances of Modeling Services models and enable versioning with subfolders. A user can have access to the current version of a model identified by one Folder but not have access to a different version of the same model identified by a different Folder. To better understand the security model, it is best to first examine the design of the updatable views and then progress to the underlying security tables and functions in the Repository.Item schema that support them.
Updatable Views
Updatable views in Modeling Services enable flexible and secure access to base tables without exposing the tables directly. Although stored procedures could have been used to restrict access to the underlying data, stored procedures are too limiting for ad-hoc queries, applications, and reporting needs. One reason for this is that many tools and data-access technologies are optimized for table-based operations rather than stored procedure calls. It is also difficult for stored procedures to accurately predict all of the various queries that users and applications will require. Conversely, accessing the base tables in the Modeling Services database directly has the disadvantage of providing too much freedom with the potential for causing breaking changes to the Modeling Services infrastructure. To address both concerns, Modeling Services uses updatable views with SQL Server INSTEAD OF triggers. SELECT queries against these views return rows from the base tables that are filtered to show only those rows that the user has permissions to see. INSERT, UPDATE, and DELETE statements against the views are redirected to three separate INSTEAD OF triggers that perform the required updates on the base tables. As with the SELECT queries, the triggers validate that the user has the proper permissions before performing any updates to the data in the base tables.
The following is a T-SQL example of the [System_Runtime].[Namespaces] view, which provides access to the information in the [System_Runtime].[NamespacesTable] base table.
create view [System_Runtime].[Namespaces]
([Id], [Documentation], [Folder], [Name])
as
select top (9223372036854775807) [NT].[Id], [NT].[Documentation], [NT].[Folder], [NT].[Name]
from [System_Runtime].[NamespacesTable] as [NT] with (readcommitted)
inner join [Repository.Item].[ReadableFolders]() as [RCV] on [NT].[Folder] = [RCV].[Folder];
This view illustrates the Modeling Services pattern for securing read access to the base tables. The view filters the rows of the [System_Runtime].[NamespacesTable] to only those that the user has permission to see. It does this by joining the base table, [System_Runtime].[NamespacesTable], with a table of Folder identifiers returned by the [Repository.Item].[ReadableFolders] function. This function returns the Folder identifiers that the user can access based on the permissions associated with the current database session.
In this example, assume that data has been loaded into different Folders representing versions of the .NET Framework, such as 1.0, 2.0, and 3.0. If the user had access only to the 2.0 Folder, only 2.0 namespace rows would be returned by the [System_Runtime].[Namespaces] view. Note that there is a column named "Folder" in most Modeling Services base tables, so that each row is associated with the Folder to which it belongs. This enables an efficient join on the Folder column in the base table with the accessible Folders returned by [Repository.Item].[ReadableFolders] and other Modeling Services security functions. Modeling Services views on tables without a Folder column implicitly treat rows in those tables as having a Folder identifier of 1.
The preceding view definition of [System_Runtime].[Namespaces] creates a view that applies security filters on the data in the underlying [System_Runtime].[NamespacesTable] base table. However, this does not address the update scenario, because SQL Server views are read-only by default. To allow users to modify the data exposed by the view, the Modeling Services adds INSTEAD OF triggers to the views for any modification requests. There are three separate instances of the INSTEAD OF trigger that are added for each view, corresponding to insertions, updates, and deletions. The following example shows an INSTEAD OF trigger for redirecting updates on the [System_Runtime].[Namespaces] view.
create trigger [System_Runtime].[Namespaces_InsteadOfUpdate_Trigger]
on [System_Runtime].[Namespaces]
with execute as 'RepositoryService'
instead of update
as
begin
declare @illegalContainerId [Repository.Item].[FolderId] =
(select top (1) [U].[Folder]
from ( (select [I].[Folder] from inserted as [I]
union
select [D].[Folder] from deleted as [D])) as [U]([Folder])
where [U].[Folder] not in
(select * from [Repository.Item].[UpdatableFolders]()));
if @illegalContainerId is not null
begin
execute [Repository.Item].[RaiseErrorPermissions]
@resourceKind = 0xc40d90fd3b9e1f4500875b536d166ab0,
@resource = @illegalContainerId,
@operation = 0x18be729c919a764a68bf046db0109ee9;
end
else
if (update([Id]))
begin
execute [Repository].[RaiseErrorReadOnlyColumn]
@schema = N'System_Runtime',
@table = N'Namespaces',
@column = N'Id';
end
else
begin
update [System_Runtime].[NamespacesTable]
set [Documentation] = [I].[Documentation],
[Folder] = [I].[Folder],
[Name] = [I].[Name]
from inserted as [I]
where [System_Runtime].[NamespacesTable].[Id] = [I].[Id];
end
end
This trigger intercepts update statements on the view. The row being modified is provided to the trigger in a table named deleted. The trigger compares the Folder identifier of the modified row with the results returned by the [Repository.Item].[UpatableFolders] function. If the user has permissions for updating this Folder, then the update is applied to the underlying base table, [System_Runtime].[NamespacesTable]. The requested update is contained in a trigger-supplied table named inserted. If the user lacks permissions for this update, an error is raised with the [Repository.Item].[RaiseErrorPermissions] stored procedure. This procedure formats a descriptive error of what permissions are denied and exactly what Modeling Services database objects are involved.
Note
For more information about Folders, watch the two-part video on repository Folders.
Claims-based Security
Modeling Services implements a claims-based security model that is aligned with other Microsoft investments in identity technologies, such as Active Directory Federation Services and Windows CardSpace. In this model, users and applications present claims to the Modeling Services database that grant the ability to access or modify Modeling Services data. One type of claim is the user’s Security Identifier (SID), obtained when the user logs on through Windows Authentication. However, claims-based security is not restricted to Windows Authentication, and custom claims about the user can be passed using SAML tokens. For example, a custom claim could include the department that the user belongs to or the applications that are available to the user. With all claims, it is important that Modeling Services trust the identity provider that issued the claims. In the case of the user’s SID, the identity providers are the operating system and Active Directory. For custom claims, there might be other trusted identity providers that are not based on Windows Authentication. Modeling Services associates claims with allowable operations on resources. Examples of operations include reading and updating data. A Folder is an example of a resource.
There are several views that support this claims-based security model in the Modeling Services database. The following list describes these security-related views in the Repository.Item schema.
| View | Description |
|---|---|
For each claim, records which operation can be performed on a resource (for example, it could grant a SID claim the update operation on a Folder resource). |
|
A list of claims and their kinds. |
|
Description of the kinds of claims. |
|
Description of the kinds of resources. |
|
List of securable operations. |
Note
Note that there is one important base table, [Repository.Item].[SecuredResourcesTable], that is not directly exposed through an associated view.
To illustrate the relationships among these views, consider a query on the [Repository.Item].[SecuredResourcePermissions] view.
use Repository
go
select top 4 Id, Claim, [Resource], Operation, MayGrantOrRevoke
from [Repository.Item].[SecuredResourcePermissions]
order by Id
Note
Note that only a user in the RepositoryAdministrator role can run these queries, because normal users are restricted from accessing this security information.
This view links claims to resources and operations on those resources. The following example shows the output from this query.
| Id | Claim | Resource | Operation | MayGrantOrRevoke |
|---|---|---|---|---|
1 |
1 |
1 |
9C72BE18-9A91-4A76-68BF-046DB0109EE9 |
1 |
2 |
1 |
1 |
BA21FDED-D87D-462E-C480-34EEED30CA5D |
1 |
3 |
2 |
1 |
9C72BE18-9A91-4A76-68BF-046DB0109EE9 |
1 |
4 |
2 |
1 |
BA21FDED-D87D-462E-C480-34EEED30CA5D |
1 |
The Claim column of the [Repository.Item].[SecuredResourcePermissionsTable] table has a foreign key constraint to the [Repository.Item].[SecurityClaimsTable] table, which contains the kind and value of the actual claim (for example, a Windows SID 0x010100000000000512000000). The Resource column has a foreign key constraint to the [Repository.Item].[SecuredResourcesTable] table, which contains the kind and value of the resource (for example, a Folder with an identifier of 1). The Operation column has a foreign key constraint to the [Repository.Item].[SecuredOperationsTable] table, which contains the name and description of the allowed operation (for example, Update Operation). The combination of the values of one row creates an understandable statement about the granted claim permission. For example, “The Windows SID claim 0x010100000000000512000000 can perform the Update Operation on the rows belonging to the Folder 1.” The MayGrantOrRevoke bit field indicates whether the user associated with the claim is able to grant or revoke permissions for the same resource and operation for other claims.
Database sessions are automatically mapped to a list of claims. SQL Server creates a database session for each user that opens a connection to the database. Modeling Services uses a server-wide trigger, Trigger_Repository_Repository_Repository.Item_Logon_SetSecurityClaims, which gets called for each user logon. This trigger calls the stored procedure [Repository.Item].[SetLogonSessionsSecurityClaims] to associate the new database session with the correct claims. This procedure determines the Windows SID associated with the database session. It then looks for an existing SID-based claim in the [Repository.Item].[SecurityClaimsTable] table. An entry is made in the claims cache to map the claim to the database session identifier (also known as the SPID in SQL Server). As seen previously, when a user queries a Modeling Services view, helper functions like [Repository.Item].[ReadableFolders] filter the results based on Folders that the current user can access. These functions work by comparing the current user’s session claims with the desired Folder operations.
Modeling Services manages claim permissions through a collection of database triggers, stored procedures, functions, and foreign keys. These security-related database objects are visible only to Modeling Services administrators and are not intended to be called directly. Database triggers help to maintain the consistency of claims permissions data. When adding a new Folder to the [Repository.Item].[FoldersTable] table, an insert trigger adds the Folder resource to the [Repository.Item].[SecuredResourcesTable] table and then calls the internal stored procedure [Repository.Item].[UncheckedGrantClaimPermission]. This procedure then calls the [Repository.Item].[SetLogonSessionsSecurityClaims] procedure to grant read and update claim permissions to the creator of the Folder and the RepositoryAdministrator role. When a Folder is deleted from the [Repository.Item].[FoldersTable] table, a delete trigger deletes the Folder resource from the [Repository.Item].[SecuredResourcesTable] table. The foreign keys for the resources in the [Repository.Item].[SecuredResourcePermissionsTable] table are created with cascading referential integrity constraints, which means that SQL Server automatically removes claim permissions in this table when the referenced resources are removed. In this way, security data remains consistent in the Modeling Services database.
The Modeling Services database has several stored procedures for granting or revoking claim permissions for an operation on a resource.
| Stored procedure | Description |
|---|---|
Grants a claim permission. |
|
Grants a claim permission where the claim kind is implicitly a database principal (such as a user’s Windows SID). |
|
Grants read or update permissions for a Folder to a database principal. |
|
Revokes a claim permission. |
|
Revokes a claim permission where the claim kind is implicitly a database principal. |
|
Revokes read or update permissions for a Folder to a database principal. |
These procedures require the calling user to have an associated claim on the same operation and resource in the [Repository.Item].[SecuredResourcePermissionsTable] with the MayGrantOrRevoke bit set to 1. Therefore, these procedures are useful for a user to grant their assigned resource and operation permissions to other claims.
Repository Roles and Users
SQL Server security is also used to control the level of direct access that users have to the Modeling Services database objects. For example, using a Modeling Services view might restrict a user from seeing row data for certain Folders, but that same user could be a database administrator, allowing him or her to access the base tables directly. The following table lists the various SQL Server database roles for Modeling Services users.
| Role | Description |
|---|---|
RepositoryAdministrator |
Authorizes administrative operations. |
RepositoryUser |
Provides access for a typical Modeling Services user. |
RepositoryReader |
Allows read-only operations for reporting needs. |
RepositoryReaderWriter |
Allows reading and writing to the Modeling Services database without granting full administrator access. |
RepositoryChangeTrackingReader |
Allows reading of the Change Tracking data. |
RepositoryChangeDataCaptureReader |
Allows reading of the Change Data Capture (CDC) audit data. |
RepositoryChangeDataCaptureService |
Used by the Modeling Services’s Change Data Capture (CDC) service. |
Users and groups can be assigned to these roles to more easily manage groups of users with similar Modeling Services access needs. Database administrators assign users to roles by using the Microsoft SQL Server Management Studio user interface or by running a T-SQL command, such as in the following example.
exec sp_addrolemember N'RepositoryUser', N'user1'
The following table also shows two Modeling Services users for security purposes.
| User | Description |
|---|---|
RepositoryOwner |
A user that owns all Modeling Services objects. |
RepositoryService |
A user for running Modeling Services services. |
The RepositoryOwner and RepositoryService users do not have associated logins.
See Also
Concepts
SQL Server Modeling Services Features
SQL Server Modeling Services Design Patterns
Security Tasks (Modeling Services)