SQL Server Modeling Services Folder Design Patterns

[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.]

SQL Server Modeling Services Folders support granular organization and versioning for Modeling Services data. Folders are created in the [Repository.Item].[FoldersTable] table. They are exposed to users through the [Repository.Item].[Folders] view, assuming that the users have appropriate permissions. Other Modeling Services tables that support the Folder design pattern include a column named Folder. This Folder column is a foreign key to the Id column of the [Repository.Item].[FoldersTable] table. Row instances in these tables can specify the Folder that each row of data belongs to. This allows views and procedures that access the table data to apply more granular security based on what Folders a user has the right to view or change.

The [Repository.Item].[FoldersTable] table also includes a recursive Folder column for expressing a hierarchy of nested Folders. Folders with a NULL value in the Folder column are top-level Folders in the forest.

Tip

It is useful to think of the similarity between Modeling Services Folders and file system folders. File system folders are containers for files. Modeling Services Folders contain rows of data that may exist in one or more referencing tables. Both file system folders and Modeling Services Folders contain subfolders, provide secure access to items contained in the folder, and provide a logical grouping of items for improved organization.

This topic describes the requirements for using Folders in a data model that resides in the Modeling Services database. For more information about  Folders, see How to: Create and Use SQL Server Modeling Services Folders.

Tip

This topic explains how to use the Modeling Services pattern for Folders directly in SQL Server. If you are creating models with Microsoft code name “M”, refer to the topic on using this Modeling Services pattern in “M”. For more information, see Adding Folders (Modeling Services).

Guidelines for Using Modeling Services Folders

The following list covers the guidelines for manually creating and using Folders:

  • Add New Folders to the [Repository.Item].[Folders] View.

  • Create Subfolders with the Folder Column of the [Repository.Item].[Folders] View.

  • Use the Modeling Services Localization Design Pattern to Provide a Display String.

  • Grant Appropriate User Permissions on the Folder.

Note

Creating and managing Folders requires the user to belong at least to the RepositoryReaderWriter role. The user also must have update permissions on the parent Folder. As an alternative, users in the RepositoryAdministrator role can also create and manage Folders.

Add New Folders to the [Repository.Item].[Folders] View

Add new Folders to the database by inserting new instances into the [Repository.Item].[Folders] view. The insert trigger on the [Repository.Item].[Folders] view provides additional benefits, including selection of the next available Folder identifier when Id is null, the creation of a new GUID when GlobalId is null, and the verification that the user has permissions to create the requested Folder.

Tip

There are two other standard ways of creating Folders. First, you can use the Mx.exe tool's createFolder command. For more information, see  Mx.exe Command Line Reference. You can also use Microsoft code name “Quadrant” to create new Folders. For more information, see  How to: Create a New and Nested Folder.

Warning

Do not directly interact with the [Repository.Item].[FoldersTable] table. Even though a database administrator might have permission to add a Folder to the [Repository.Item].[FoldersTable] table, accessing the table instead of the view is unsupported and bypasses the Modeling Services security model. It could also cause future Folder insertions into the [Repository.Item].[Folders] view to fail.

Example

The following T-SQL example creates a top-level Folder named Example_Folder. The insert trigger on the [Repository.Item].[Folders] view generates appropriate values for Id and GlobalId, and the localized description field, DisplayName, is not specified in this example.

use Repository
go
insert into [Repository.Item].[Folders] ([Name], [DisplayName], [Folder])
values (N'Example_Folder', null, null)

Warning

It is permitted to specify an identifier for the new Folder. However, you must use the [Repository.Item].[NewIdInterval] stored procedure to obtain the next available identifier or group of identifiers. If you supply an identifier without using this routine, then the sequence object in [Repository].[IdSequencesTable] may generate future Folder identifiers that violate the primary key constraint. For more information, see  Identifier Design Patterns.

Create Subfolders with the Folder Column of the [Repository.Item].[Folders] View

Add new subfolders to an existing Folder by referencing the parent Folder in the Folder column of the [Repository.Item].[Folders] view. Folders can also be moved to other locations in the hierarchy by referencing a new parent Folder. Folders can become top-level Folders if the Folder field is set to null.

Note

Folders cannot reference themselves directly or indirectly. Constraints on the [Repository.Item].[FoldersTable] table prevent this situation.

Example

The following T-SQL example creates a parent Folder with two subfolders. This example first creates a top-level Folder named Parent_Folder. The Id field is not specified, so the view generates a proper identifier during the insert. The generated Folder identifier is retrieved by using the [Repository.Item].[PathsFolder] function. This Folder identifier is then used as the value of the Folder column for the final two Folder inserts. By referencing the Parent_Folder Folder identifier, the final two Folders, Child1 and Child2, become subfolders of the Parent_Folder Folder.

use Repository
go

-- Create a parent folder with a generated Id.
insert into [Repository.Item].[Folders] ([Name], [DisplayName], [Folder])
values (N'Parent_Folder', null, null)
go

-- Obtain the generated Id for the parent folder by name.
declare @parent_folder as int
set @parent_folder = [Repository.Item].[PathsFolder]('Parent_Folder')

-- Insert two child folders that reference the parent folder.
insert into [Repository.Item].[Folders] ([Id], [GlobalId], [Name], [DisplayName], [Folder])
values (null, null, N'Child1', null, @parent_folder)
insert into [Repository.Item].[Folders] ([Id], [GlobalId], [Name], [DisplayName], [Folder])
values (null, null, N'Child2', null, @parent_folder)
go

Use the Modeling Services Localization Design Pattern to Provide a Display String

The DisplayName column of the [Repository.Item].[Folders] view is an identifier that references a localized string for the display name of the Folder. This string is used by tools that understand Modeling Services design patterns. It enables users to be presented with a localized display string of the Folder name. For more information about using localized strings in the Modeling Services database, see Localization Design Pattern.

Grant Appropriate User Permissions on the Folder

When a user adds a new Folder to the [Repository.Item].[Folders] view, the user automatically receives both read and update permissions on the new Folder. There may be other users that also need access to this new Folder. Either the Folder creator or a Modeling Services administrator can grant other users access to this Folder using security procedures in the Modeling Services database.

Modeling Services implements a claims-based security model. Folders provide a basis for securing data within a table. Updatable views look at the Folder permissions for the calling user before determining which rows can be selected or modified. For more information about updatable views and their use of Folder permissions, see View Design Patterns.

There are three stored procedures for granting permissions to Folder resources:

The [Repository.Item].[GrantClaimPermission] stored procedure accepts parameters for the claim kind, the claim, a resource kind, the resource, an operation type, and a flag on whether the granted claim can in turn grant the same permissions to others. In many cases, the claim kind is a database principal or SID and the claim is the actual SQL or Windows SID. In this case [Repository.Item].[GrantPrincipalPermission] is more convenient, because it allows the user to pass in a database principal name and assumes a claim type of SID.

[Repository.Item].[GrantClaimPermission] requires that the user already has the permission that he or she is attempting to grant. The user must also have the mayGrantOrRevoke flag set to 1 for that permission, indicating that the calling user has the right to assign this permission to others. Note that members of the RepositoryAdministrators role are able to set permissions on any Folders.

When calling these stored procedures to grant permissions on Folders, it is important to note that the possible values to pass to these procedures come from several security views.

First, the possible resource kinds are located in the [Repository.Item].[SecuredResourceKinds] view. Folders have a resource kind with an Id of FD900DC4-9E3B-451F-0087-5B536D166AB0 and a Name of https://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder. The actual resource for Folders comes from the Id column of the [Repository.Item].[Folders] view.

Claim kinds are located in the [Repository.Item].[SecurityClaimKinds] view. A user SID kind has an Id of E2133CEF-0DF3-4880-F0A7-FF257A5587B7 and a Name of https://schemas.xmlsoap.org/ws/2005/05/identity/claims/sid. The SID claim value can be any value from the Claim column of the [Repository.Item].[SecurityClaims] view that also has a claim kind of SID. These SIDs are a subset of values in the sys.database_principals system view.

Finally, the read operation, BA21FDED-D87D-462E-C480-34EEED30CA5D, and update operation, 9C72BE18-9A91-4A76-68BF-046DB0109EE9, are both defined in the [Repository.Item].[SecuredOperatins] view. These operations have the names of https://schemas.microsoft.com/Repository/2007/10/Operations/Update and https://schemas.microsoft.com/Repository/2007/10/Operations/Read respectively.

Examples

Grant a User Read Permissions to Modeling Services Folders with [Repository.Item].[GrantPrincipalPermission]

The following T-SQL example shows how to use the [Repository.Item].[GrantPrincipalPermission] stored procedure to grant a new user access to a Folder. This script first adds a SQL Server login for a domain account named DOMAINNAME\username. It then associates this login with a new Modeling Services database user named username. The script creates a test Folder named Folder1. The Folder resource kind and the read operation identifiers are both queried by name. These parameters are then passed to the [Repository.Item].[GrantPrincipalPermission] stored procedure to give the user read access to the new Folder and any new data that is associated with the Folder. Also note that the @mayGrantOrRevoke parameter is set to 1. This gives the target user the right to grant or revoke the same read permission for other users.

The [Repository.Item].[GrantPrincipalPermission] stored procedure is easier to use than the [Repository.Item].[GrantClaimPermission] stored procedure for granting access to a user, because both the SID claim kind and the SID value itself are generated by [Repository.Item].[GrantPrincipalPermission] procedure.

After running the script below, the target user could log into the SQL Server to see the effect of these permission changes. The user could query the [Repository.Item].[CurrentPermissions] view to see the new claim permissions added for the Folder1 Folder. If there were rows associated with this Folder, the user would see those rows returned from queries to security views.

Note

If you run the script below, change the DOMAINNAME\username name to a valid window domain account in your environment. Also, change each instance of username to reflect the actual user name that you are targeting.

use Repository
go

-- Add a domain account login to SQL Server.
if not exists(select * from sys.syslogins where name = 'DOMAINNAME\username')
create login [DOMAINNAME\username] from windows
go

-- Add this windows login as a user to the Repository database.
if not exists(select * from sys.sysusers where name = 'username')
create user username for login [DOMAINNAME\username];
go

-- Add the user to a RepositoryReader role.
exec sp_addrolemember N'RepositoryReader', N'username'
go

-- Create a new folder.
if not exists (select * from [Repository.Item].[Folders] where Name = N'Folder1')
insert into [Repository.Item].[Folders] ([Name], [DisplayName], [Folder])
values (N'Folder1', null, null)
go

-- Obtain the generated Id for the folder by name.
declare @folder as [Repository.Item].[FolderId]
set @folder = (select Id from [Repository.Item].[Folders] where Name = N'Folder1')

-- Folder Resource Kind
declare @target_resourceKind as [Repository.Item].[SecuredResourceKindId]
select @target_resourceKind = Id 
from [Repository.Item].[SecuredResourceKinds] 
where Name = N'https://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder'

-- Read Operation
declare @target_operation as [Repository.Item].[SecuredOperationId]
select @target_operation = Id 
from [Repository.Item].[SecuredOperations] 
where Name = N'https://schemas.microsoft.com/Repository/2007/10/Operations/Read'

-- GrantPrincipalPermission passing in the new login.
-- The claim kind, SID, and the SID itself are 
-- generated by the stored procedure.
exec [Repository.Item].[GrantPrincipalPermission] 
@principal = 'username', 
@resourceKind = @target_resourceKind,
@resource = @folder, 
@operation = @target_operation,
@mayGrantOrRevoke = 1
go

Note

Any existing database connections for the user will not reflect the additional permissions in these examples. New database connections for the user will reflect the additional permissions.

Note

This example intentionally uses domain accounts rather than SQL Server logins. You should use domain accounts for access to the Modeling Services database because this provides the best level of security. The Modeling Services database has not been tested with SQL Server logins, and they are not supported.

Grant a User Update Permissions to Modeling Services Folders with [Repository.Item].[GrantClaimPermission]

The following T-SQL example uses the [Repository.Item].[GrantClaimPermission] stored procedure to grant a user update permissions on a Folder.

Note

Note that this example could have also used [Repository.Item].[GrantPrincipalPermission] to grant the user update permissions. The [Repository.Item].[GrantClaimPermission] procedure is used in this example only to demonstrate alternative methods of granting permissions.

Some of the script’s tasks are similar to the first example, such as adding the new database user, adding a new Folder, and obtaining the resource kind and operation. However, because the procedure is more generic to support various claim types, there are additional parameters required to specify that this is a SID claim permission. The SID claim kind is queried by name from the [Repository.Item].[SecurityClaimKinds] view. The SID value is obtained by using the SQL Server SUSER_SID system function. Note that the @mayGrantOrRevoke parameter is set to 0 in this example. The user has been granted update permissions on the Folder1 Folder, but they will not be able to grant or revoke update permissions for other users.

Note

If you run the script below, change the DOMAINNAME\username name to a valid window domain account in your environment. Also, change each instance of username to reflect the actual user name that you are targeting.

use Repository
go

-- Add a domain account login to SQL Server.
if not exists(select * from sys.syslogins where name = 'DOMAINNAME\username')
create login [DOMAINNAME\username] from windows
go

-- Add this windows login as a user to the Repository database.
if not exists(select * from sys.sysusers where name = 'username')
create user username for login [DOMAINNAME\username];
go

-- Add the user to a RepositoryReaderWriter role.
exec sp_addrolemember N'RepositoryReaderWriter', N'username'
go

-- Create a new folder.
if not exists (select * from [Repository.Item].[Folders] where Name = N'Folder1')
insert into [Repository.Item].[Folders] ([Name], [DisplayName], [Folder])
values (N'Folder1', null, null)
go

-- Obtain the generated Id for the folder by name.
declare @folder as [Repository.Item].[FolderId]
set @folder = (select Id from [Repository.Item].[Folders] where Name = N'Folder1')

-- Obtain the user SID.
declare @target_claim as [Repository.Item].[SecurityClaim]
set @target_claim = SUSER_SID('DOMAINNAME\username')

-- SID Claim Kind
declare @target_claimKind as [Repository.Item].[SecurityClaimKindId]
set @target_claimKind = (select Id from [Repository.Item].[SecurityClaimKinds] 
   where Name = 'https://schemas.xmlsoap.org/ws/2005/05/identity/claims/sid')

-- Folder Resource Kind
declare @target_resourceKind as [Repository.Item].[SecuredResourceKindId]
select @target_resourceKind = Id 
from [Repository.Item].[SecuredResourceKinds] 
where Name = N'https://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder'

-- Update Operation
declare @target_operation as [Repository.Item].[SecuredOperationId]
select @target_operation = Id 
from [Repository.Item].[SecuredOperations] 
where Name = N'https://schemas.microsoft.com/Repository/2007/10/Operations/Update'

-- GrantClaimPermission passing in the SID.
-- For this example, the SID claim and claim kind
-- are explicitly provided.
exec [Repository.Item].[GrantClaimPermission] 
@claimKind = @target_claimKind, 
@claim = @target_claim, 
@resourceKind = @target_resourceKind,
@resource = @folder, 
@operation = @target_operation,
@mayGrantOrRevoke = 0
go

Note

Note that in the preceding examples, the user creating the new Folder is automatically granted read and update permissions on that new Folder with the mayGrantOrRevoke field set to 1. This enables the user who is executing the script to grant the same claim permissions to another user.

See Also

Concepts

SQL Server Modeling Services Design Patterns
SQL Server Modeling Services Architecture

Other Resources

Videos on “Oslo”