Creating Security Views (Modeling Services)

[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 implements a form of row-based security by using updatable views. Updatable views in the Modeling Services database use triggers to handle insert, update, and delete operations. This enables Modeling Services to control what rows users can read and update based on Folder permissions. For more information about Folders, see Adding Folders (Modeling Services). Updatable views are always associated with an underlying table. The actual data is in the table, and the view acts as a security gate.

This topic discusses the requirements for adding updatable security views. For more information about updatable views and the security architecture, see SQL Server Modeling Services Security.

Using Computed Values to Define the View

In “M”, a computed value with no parameters results in the creation of a SQL Server view when that “M” model is loaded into a database. For each extent that you want to use with this pattern, you define a matching computed value. This computed value returns the rows from its associated extent, but it filters those rows based on the Folder permissions. The computed value contains a query that joins with the Base Domain Library view, ReadableFoldersView. The following example shows a PhoneNumbersTable extent and its associated PhoneNumbers computed value.

module Contact
{
    import System;
    import Repository.Item;
    
    PhoneNumbersTable :
    {( 
        HasFolderAndAutoId &
        {
            Work : Text;

            Home : Text?;

            Mobile : Text?;
        }
    )*};

    PhoneNumbers () : {{ Id: Integer64; Folder: FoldersTable; 
            Work: Text; Home: Text; Mobile: Text;}*}
    {
        PhoneNumbersTable where value.Folder.Id in ReadableFoldersView().Folder
        select 
        { 
            Id => value.Id, Folder => value.Folder, Work =>value.Work,
            Home => value.Home, Mobile => value.Mobile
        }
    }
}

When creating updatable views, use the naming convention shown here. The view should be the base name of your entity, such as PhoneNumbers, and the extent should use the base name with the word "Table" appended, such as PhoneNumbersTable.

For more information about writing computed values, see Adding Computed Values.

Adding the Triggers

In the previous example, the PhoneNumbers computed value results in the creation of a view in the target Modeling Services database. It also filters the rows returned based on Folder permissions. But it does not yet filter the insert, update, and delete operations based on Folder permissions.

To add the triggers, use the PatternApplication sample. For each extent that follows this pattern, add a PatternApplication::EntityPatternApplications record with the Pattern field set to Patterns.AddViewsInsteadOfTriggers. For more information about the PatternApplication sample, see Using the PatternApplication Sample. The PatternApplication sample internally calls the stored procedure [Repository.Item].[AddViewsInsteadOfTriggers].

See Also

Concepts

SQL Server Modeling Services Patterns

Other Resources

SQL Server Modeling Services Security
View Design Patterns