286
Part II: Building Databases and Working with Data
SELECT
ProductCategoryID,
Name ProductSubCategory
FROM Production.ProductSubcategory
WHERE ProductCategoryID = 1;
WITH CHECK OPTION;
go
INSERT INTO vComponentsProductSubCats(ProductCategoryID,
ProductSubCategory)
VALUES(2, 'Bike Pedal');Server: Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK
OPTION and one or more rows resulting from the operation did not
Qualify under the CHECK OPTION constraint.
The statement has been terminated.This time the INSERT failed and the error message attributed the cause to the
WITH CHECK OPTION in the view, which is exactly the wanted effect.Some developers employ views and the WITH CHECK OPTION as a way to provide row-level
security—a technique called horizontally positioned views. As in the subcategory view exam-
ple, they create a view for each subcategory and then give users security permission to the
view that pertains to them. Although this method does achieve row-level security, it also
has a high maintenance cost.For the application, row-level security can be designed using user-access tables and stored procedures,
as demonstrated in Chapter 35, “Row Level Security,” but views can help enforce row-level security for
ad hoc queries.Within Management Studio’s View Designer, you can enforce the WITH CHECK OPTION
within the View Properties form. Actually two properties must be enabled. The fi rst is Update
Using View Rules, which prohibits Management Studio and MDAC from decoding the view and
directly accessing the underlying tables. Only when Update Using View Rules is enabled can
the second option, WITH CHECK OPTION, be enabled. To access the properties of a view from
the View Designer, either use the F4 key or on the menu choose Views ➪ Properties Window.
Expand the Update Specifi cation Property and set both of its properties to Yes.Protecting the View
Three options protect views from data schema changes and prying eyes. These options are
simply added to the CREATE command and applied to the view, in much the same way that
the WITH CHECK OPTION is applied.Database code is fragile and tends to break when the underlying data structure changes.
Because views are nothing more than stored SELECT queries, changes to the referenced
tables may break the view.c11.indd 286c11.indd 286 7/30/2012 4:41:12 PM7/30/2012 4:41:12 PM
http://www.it-ebooks.info