Aim Mangkorntong's Web Log

Managing Secured Resource Permissions in "Oslo" Repository

What security model have we adopted in “Oslo” Repository ?  How can we manage secured resource permissions in “Oslo” Repository?

Our main goal for the security model is the ability to provide a unified, flexible and yet secured approach for accessing repository contents from different type of database-enabled applications (e.g. SQL Reporting Services and Excel).  

In "Oslo" Repository, we adopted the CardSpace security model (i.e. security claim-based).  With claims based security, it is possible to emulate Access Control List (ACL) and Role-Based Access Control (RBAC) based security and therefore supporting the security models used in a wide range of products.

The claims-based security model, essentially, consists of the following concepts:

Concepts	Description	Examples
Claims	Claim type-value pairs	(Name = ‘Anthony’)
Claim types	Used for partitioning claims into related sets	Windows SID claim type
Issuers	Parties that issue a claim. This is identified by a certificate.	Contoso Corporation
Resources	Resource type-value pairs  that are secured by the system	(Folder = ‘.Net Framework 2.0’)
Resource types	Used for partition resources into related sets.	A folder resource type
Operations	Secured operations that can be performed on secured resources.	A read operation
Permissions	The authorization model that allows principals with a valid issued claim to perform an operation on a particular resource.	A principal with the domain group ‘MyDomain\IT Admin’ may read the folder ‘.Net Framework 2.0’

The diagram below provides the overall pictures of the security model in "Oslo" Repository. In practice,  resources, claims and operations are referred to by their GUIDs.

In terms of users and database roles, there are 2 distinguished users for security purposes:

·         RepositoryOwner: A loginless user that owns all repository objects.

·         RepositoryService: A loginless user that repository service runs as.

Below is a list of repository user roles defined in "Oslo" Repository.

·         RepositoryAdministrator: A role that allows users to perform administrative operations such as granting resource access.

·          RepositoryReaderWriter: A role that allows users to read and update the repository content.

·         RepositoryReader: A role that allows users to read the repository content.

·         RepositoryUser: A role that allows users basic access to the repository.

·         RepositoryChangeTrackingReader: A role for users authorized to read SQL Server Change Tracking records (not CDC records).

·         RepositoryChangeDataCaptureReader: a role for users authorized to read CDC records (not Change Tracking records).

There is a role used by repository infrastructure:

·         RepositoryChangeDataCaptureService: An internal role that is used to access CDC records.

The security model comes with a number of helper functions for managing resource permissions in a repository. Below are some examples to give you some idea of how to play around with the security model in "Oslo" Repository. Note that the wrapper functions for the code below are already provided for Folder resources (see my previous blog for more details). For a complete set of examples, click here.

-- Returns the detailed summary of resource permissions of the current session

select CP.[ResourceKind],

       CP.[ResourceKindDescription],

       CP.[Resource],

       CP.[Operation],

       CP.[OperationDescription],

       CP.[MayGrantOrRevoke]

from [Repository.Item].[CurrentPermissions] as CP

 

-- Returns a summary of resource permissions of the curresnt session

select SP.[Resource],

       SP.[ResourceKind],

       SP.[Operation],

       SP.[MayGrantOrRevoke]

from [Repository.Item].[SessionsPermissions] as SP

 

 

-- Obtain the security claims associated to the current user session.

select  TSSC.[Claim],  DP.[Name] as [PrincipalName]

from [Repository.Item].[TheSessionsSecurityClaims] as TSSC

    inner join [Repository.Item].[SecurityClaims] as SC on TSSC.[Claim] = SC.[Id]

    inner join sys.database_principals as DP on SC.[Claim] = DP.[sid]

  order by DP.[Name]

 

-- Give the new principal the read access to the folder '/Repository/New Folder'

declare @folderId int =  [Repository.Item].[PathsFolder]('/Repository/New Folder');

declare @readOperation [Repository.Item].[SecuredOperationId];

declare @folderResource [Repository.Item].[SecuredResourceKindId];

 

select @readOperation = SO.[Id]

from [Repository.Item].[SecuredOperations] as SO

where SO.[Name] = 'http://schemas.microsoft.com/Repository/2007/10/Operations/Read';

 

select @folderResource = SRK.[Id]

from [Repository.Item].[SecuredResourceKinds] as SRK

where SRK.[Name] = 'http://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder';

 

execute [Repository.Item].[GrantPrincipalPermission]   

  @principal = 'MyDomain\Bob',

  @resourceKind=@folderResource,

  @resource= @folderId,

  @operation=@readOperation,

  @mayGrantOrRevoke=0;

 

-- View a list of readable and updatable folders

declare @folderResource [Repository.Item].[SecuredResourceKindId];

 

select @folderResource = SRK.[Id]

from [Repository.Item].[SecuredResourceKinds] as SRK

where SRK.[Name] = 'http://schemas.microsoft.com/Repository/2007/10/Identity/Resources/Folder';

 

select * from [Repository.Item].[ReadableResources](@folderResource);

select * from [Repository.Item].[UpdatableResources](@folderResource);