View Design Patterns

  • Article

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

Updatable views represent a key SQL Server Modeling Services design pattern. An updatable view uses database triggers to support inserts, updates, and deletes. Users and applications benefit from this model, because many development tools are optimized to perform set-based operations against table data. In many cases, an updatable view appears and functions exactly like a table. This is a simpler model than using a separate data access library or set of stored procedures. At the same time, the fact that these are views, rather than tables, allows Modeling Services to include additional logic in the view itself and the associated triggers.

Modeling Services views build on many other design patterns. All requests against a view allow only those operations that are defined by Modeling Services Folder security. Inserts into some views use the design pattern for identifier sequences.

Tip

This topic explains how to use the Modeling Services pattern for updatable views 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 Creating Security Views (Modeling Services).

Guidelines for Views

The following list provides the SQL guidelines for creating updatable views for underlying base tables in the Modeling Services database:

  • Create Views to Secure Read Access of Underlying Base Tables.

  • Grant Permissions to the RepositoryReader and RepositoryReaderWriter Roles.

  • Do Not Use Schema Binding.

  • Add INSTEAD OF Triggers to Create Updatable Views.

Note

Database administrative privileges are required for performing many of these manual design tasks in the Modeling Services database.

Create Views to Secure Read Access of Underlying Base Tables

Create views as the external interface for users and applications to read and modify Modeling Services data. Use the table base name as the name of the view. For example, if the table name is AddressesTable, the associated view name would be Addresses. In the view definition, use the function [Repository.Item].[ReadableFolders] to return a set of Folders that the user has read access to. This can be joined with the Folder column in the underlying base table to filter the returned rows to only those rows that the caller has permissions to see. For more information about designing the base tables, see Table Design Patterns. For more information about the Folder design patterns, see SQL Server Modeling Services Folder Design Patterns.

Examples

Consider a scenario where there is a Contact schema that contains a [Contact].[AddressesTable] table. The following T-SQL example shows the definition of the corresponding [Contact].[Addresses] view.

create view [Contact].[Addresses] 
    ([Id], [Folder], [Street], [City], [State], [ZipCode])
as
select top (9223372036854775807) [AT].[Id], [AT].[Folder], [AT].[Street], 
    [AT].[City], [AT].[State], [AT].[ZipCode]
from [Contact].[AddressesTable] as [AT] with (readcommitted)
inner join [Repository.Item].[ReadableFolders]() as [RCV]  on [AT].[Folder] = [RCV].[Folder];

You should use the view design patterns in conjunction with the Folder design patterns; however, tables that do not use the Folder design patterns can still use updatable views. A more basic security check can be performed to ensure that the caller has access to the default Modeling Services Folder with an identifier of 1. This is done with the [Repository.Item].[SessionMayReadFolder] function and passing it a parameter of 1. This same check would take place in the triggers. The following T-SQL example shows a view definition for a table that does not contain a Folder column as part of the Folder design pattern.

create view [Contact].[Addresses]
    ([Id], [Street], [City], [State], [ZipCode])
as
select top (9223372036854775807) AT.[Id], AT.[Street], 
    AT.[City], AT.[State], AT.[ZipCode]
from [Contact].[AddressesTable] as AT with (readcommitted)
where [Repository.Item].[SessionMayReadFolder](1) = 1;
go

Note

The views in the two preceding examples select the top 9223372036854775807 rows from the base table, which results in returning all possible rows. “Oslo” repository security requires using this format for the view instead of using "select * from". Selecting the top 9223372036854775807 rows ensures that the security conditions are evaluated first before the user-supplied filters. This prevents the user from guessing potential values in the database that they should not have access to.

Grant Permissions to the RepositoryReader and RepositoryReaderWriter Roles

Based on the table design patterns, access to Modeling Services schemas and tables should be limited to the RepositoryAdministrator role and the RepositoryService user. Updatable views are used to gate access to this data, and they require access from normal Modeling Services users.

The RepositoryReader role requires SELECT access to the view. The RepositoryReaderWriter role requires DELETE and INSERT permissions to the entire view. The RepositoryReaderWriter role also requires UPDATE permissions on the view, but this role should be restricted from updating the primary key identifier, Id, or any computed columns.

Example

The following T-SQL example correctly sets permissions on the [Contact].[Addresses] view.

grant select on object::[Contact].[Addresses] to [RepositoryReader];
grant delete, insert on object::[Contact].[Addresses] to [RepositoryReaderWriter];
grant update on object::[Contact].[Addresses]([Folder], [Street], [City], [State], [ZipCode]) to [RepositoryReaderWriter];

Do Not Use Schema Binding

Schema binding on a view prevents the underlying base tables from schema modification. In other words, users and applications are prevented from changing the number and type of columns in the base table. Avoid using schema binding on views, because this reduces the extensibility of the Modeling Services database. It also reduces the ability to version the Modeling Services database.

Add INSTEAD OF Triggers to Create Updatable Views

For a view to provide Folder-level security for inserts, updates, and deletes, the view must be updatable. To make the view updatable, add INSTEAD OF triggers that apply changes to the underlying base table when those changes are made against the view. Instead of manually creating these triggers, use the [Repository.Item].[AddViewsInsteadOfTriggers] stored procedure. To use this procedure, first follow the guidelines for the other Modeling Services design patterns on tables and Folders. Then create the view as described in the previous sections of this topic. Finally, call the [Repository.Item].[AddViewsInsteadOfTriggers] stored procedure. This procedure takes two parameters, @schema and @view, that identify the view on which to create the INSTEAD OF triggers. It also contains the parameters that determine whether the operation is allowed: @insertFilter, @deleteFilter, and @updateFilter. These parameters contain default implementations that implement Folder-based security, so you should not typically override these. The @idGeneration parameter determines the type of identifier pattern used in the underlying table. This affects the implementation of the view. The following table lists the possible values for @idGeneration.

@idGeneration value Description

0

The underlying table does not use any identifier pattern. The insert trigger does not automatically generate the primary key.

1

The underlying table uses and IDENTITY column. The insert trigger allows SQL Server to automatically generate the next available value.

2

The underlying table uses Modeling Services sequence objects. The insert trigger calls the [Repository].[NewIdInterval] stored procedure to obtain the next available identifier.

If the @idGeneration value is set to 2, the final two parameters, @sequenceSchema and @sequence, must contain the schema and name of the sequence object to use. For more information about these different identifier patterns, see Identifier Design Patterns.

Examples

The following example creates the INSTEAD OF triggers for a [Contact].[Addresses] view. In this example, the underlying [Contact].[AddressesTable] table previously defined the primary key Id column to be an IDENTITY field. Note that @idGeneration is set to 1 accordingly.

exec [Repository.Item].[AddViewsInsteadOfTriggers] @schema = [Contact], @view = [Addresses], @idGeneration = 1

In the next example, the underlying [Contact].[AddressesTable] uses sequence objects to obtain the next unique identifier. The following T-SQL statements first create a sequence object and then add the INSTEAD OF triggers. Note that @idGeneration is set to 2 and the @sequenceSchema and @sequenceObject parameters specify the new sequence object to use.

exec [Repository].[CreateIdSequence] @schema = N'Contact', @sequence = N'Addresses' 
exec [Repository.Item].[AddViewsInsteadOfTriggers] @schema = [Contact], @view = [Addresses], @idGeneration = 2,
    @sequenceSchema = N'Contact', @sequence = N'Addresses'

See Also

Concepts

SQL Server Modeling Services Design Patterns
SQL Server Modeling Services Architecture