Rule Archetype Pattern in SQL Modeling Services – Part 5
In this last post of the series, I’ll wrap up the loose ends around populating Contoso’s business rule repository with some Repository folders and sample data. I’ll also illustrate how to work with the Repository’s security model to lock down Contoso’s model so that users can see only certain segments of the model.
Adding Folders to the Model
I’m going to add some ‘M’ to the Visual Studio project to seed the Contoso business rule model with some Repository folders. First step is to add a new ‘M’ file in Visual Studio:
- Add a new item to the RulesModel project of type “’M’ Model Data”. Name this file “RulesModelData.m” and click the “Add” button.
- In the “Entity Data Model Wizard” dialog select the “Default model” option and click “Finish”.
- Delete all of the default ‘M’ code from the RulesModelData.m file.
The ‘M’ for defining the Contoso seed folders is fairly intuitive. Here’s the initial ‘M’ for the RulesModelData.m file:
1: module RulesModel
2: {
3: import System;
4: import Repository.Item;
5: import System.Globalization;
6:
7: Repository.Item::FoldersTable
8: {
9: ContosoRuleModelsFolder
10: {
11: Name => "ContosoRuleModelsFolder"
12: },
13:
14: ContosoCanadaRulesModelFolder
15: {
16: Name => "ContosoCanadaRulesModelFolder",
17: Folder => ContosoRuleModelsFolder
18: },
19:
20: ContosoUsaRulesModelFolder
21: {
22: Name => "ContosoUsaRulesModelFolder",
23: Folder => ContosoRuleModelsFolder
24: }
25: }
26: }
The ‘M’ above defines a very simple Repository folder hierarchy where business rules for Contoso’s US and Canada operations are segmented under a common root business rules folder. Note that line 5 in the snippet above isn’t being used yet in the ‘M’ code, but it will – stay tuned.
Adding Business Rules to the Model
I’m not even going to try and make up semi-realistic business rules for this post – apologies for my laziness – but I will need to put some data in the model to illustrate the Repository’s Globalization support. The following snippet shows additional ‘M’ to be added to the RulesModelData.m file for adding some business rules to the model:
1: RulesTable
2: {
3: CanadianRule1
4: {
5: Name => "FF38F77A-F551-42CD-913C-90E1E08B7EBC",
6: Folder => FoldersTable.ContosoCanadaRulesModelFolder
7: },
8:
9: CanadianRule2
10: {
11: Name => "15D19A10-f4CB-11DE-8A39-0800200C9A66",
12: Folder => FoldersTable.ContosoCanadaRulesModelFolder
13: },
14:
15: UsRule1
16: {
17: Name => "5BC13260-f4CB-11DE-8A39-0800200C9A66",
18: Folder => FoldersTable.ContosoUsaRulesModelFolder
19: },
20:
21: UsRule2
22: {
23: Name => "812A3920-f4CB-11DE-8A39-0800200C9A66",
24: Folder => FoldersTable.ContosoUsaRulesModelFolder
25: }
26: }
In the snippet above it is worthy to note that the Name attribute of a rule is a Guid. This convention aligns to the Repository’s Globalization pattern. Specifically, the Name attributes in the snippet above now provide an identifier to the localized string(s) for a rule’s Name. Now I need to add the localized business rule Names.
Adding Localized Content to the Model
The following ‘M’ code snippet illustrates the code to be added to the RulesModelData.m file for the localized business rule names:
1: System.Globalization::StringsTable
2: {
3: CanadianRule1English
4: {
5: Id => RulesTable.CanadianRule1.Name,
6: Folder => FoldersTable.ContosoCanadaRulesModelFolder,
7: Locale => LocalesTable("en-CA"),
8: String => "Canadian Rule 1 in en-CA"
9: },
10:
11: CanadianRule1French
12: {
13: Id => RulesTable.CanadianRule1.Name,
14: Folder => FoldersTable.ContosoCanadaRulesModelFolder,
15: Locale => LocalesTable("fr-CA"),
16: String => "Canadian Rule 1 in fr-CA"
17: },
18:
19: CanadianRule2English
20: {
21: Id => RulesTable.CanadianRule2.Name,
22: Folder => FoldersTable.ContosoCanadaRulesModelFolder,
23: Locale => LocalesTable("en-CA"),
24: String => "Canadian Rule 2 in en-CA"
25: },
26:
27: CanadianRule2French
28: {
29: Id => RulesTable.CanadianRule2.Name,
30: Folder => FoldersTable.ContosoCanadaRulesModelFolder,
31: Locale => LocalesTable("fr-CA"),
32: String => "Canadian Rule 2 in fr-CA"
33: },
34:
35: UsRule1
36: {
37: Id => RulesTable.UsRule1.Name,
38: Folder => FoldersTable.ContosoUsaRulesModelFolder,
39: Locale => LocalesTable("en-US"),
40: String => "US Rule 1"
41: },
42:
43: UsRule2
44: {
45: Id => RulesTable.UsRule2.Name,
46: Folder => FoldersTable.ContosoUsaRulesModelFolder,
47: Locale => LocalesTable("en-US"),
48: String => "US Rule 2"
49: }
50: }
As I know nothing of French, I’ll hope you’ll forgive my rather ham-handed localization example in the code snippet above. As the snippet illustrates, the Contoso model will be populated with “localized” business rule names in both Canadian English and French. Per the Repository’s Globalization pattern, the combination of the Guid value stored in the business rule Name attribute and the Locale (e.g., “fr-CA”) uniquely identify the localized Name content for each business rule in the model.
Securing the Model
In line with Microsoft’s strategic thinking in the Identity space, the Repository leverages a Claims-based security model for granting access to Repository folders. In the latest SQL Server Modeling Services CTP, some handy store procedures are provided for setting up Repository security.
NOTE – For the purposes of this series, the provided sample code assumes a local Windows account named “TestUser” has been created. Be sure to alter the code based on your config.
The first step in securing Contoso’s model is setting up the Windows account in SQL Server. We can do that by adding the following T-SQL code to the Post.sql file in Visual Studio:
1: if not exists(select * from sys.syslogins where name = '<Machine Name>\TestUser')
2: create login [<Machine Name>\TestUser] from windows
3: go
4:
5: if not exists(select * from sys.sysusers where name = 'TestUser')
6: create user TestUser for login [<Machine Name>\TestUser];
7: go
Cool, now that the user is setup in SQL Server, we’ll add TestUser to the Repository SQL Server role of “RepositoryReader”. The following T-SQL added to the Post.sql file calls the ‘sp_addrolemember’ stored procedure to make this happen:
1: exec sp_addrolemember N'RepositoryReader', N'TestUser'
2: go
At a high level what now has to happen is a claim needs to be created stating that TestUser can exercise the Read operation against the folder containing Contoso Canada’s business rules. Setting up this claim requires three distinct lookups in the Repository to get the needed data to create the claim. The following T-SQL code in Post.sql performs these lookups:
1: declare @folder as [Repository.Item].[FolderId]
2: set @folder = [Repository.Item].[PathsFolder](N'ContosoRuleModelsFolder/ContosoCanadaRulesModelFolder')
3:
4: declare @target_resourceKind as [Repository.Item].[SecuredResourceKindId]
5: select @target_resourceKind = Id
6: from [Repository.Item].[SecuredResourceKinds]
7: where Name = N'https://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder'
8:
9: declare @target_operation as [Repository.Item].[SecuredOperationId]
10: select @target_operation = Id
11: from [Repository.Item].[SecuredOperations]
12: where Name = N'https://schemas.microsoft.com/Repository/2007/10/Operations/Read'
Lines 1 & 2 in the snippet above retrieve the Id for the Contoso Canada business rules folder, lines 4-7 look up the Id for the Repository folder resource kind, and lines 9-12 retrieve the Id for Read operations. With this lookup data in hand, creating the Repository claims only requires a call to the ‘GrantPrincipalPermission’ stored procedure. The following T-SQL code in Post.sql makes the call:
1: exec [Repository.Item].[GrantPrincipalPermission]
2: @principal = 'TestUser',
3: @resourceKind = @target_resourceKind,
4: @resource = @folder,
5: @operation = @target_operation,
6: @mayGrantOrRevoke = 1
7: go
When leveraging the Repository Security Views pattern, the TestUser should now only be able to see Contoso Canada’s business rules.
The Model in Action
After quick rebuild and redeploy of the RulesModel project I can fire up SQL Server Management Studio running as TestUser and hit the Rules view to see the security in action:
Sweet, TestUser can only see Contoso Canada’s business rules.
Next Time
That’s it for this series, stay tuned for some upcoming posts on the SQL Server Modeling Services UML Domain. In the mean time, feedback is always appreciated.
SkyDrive Files