Locking Down the View
Views are designed to control access to data. Several options protect the data or the view.
The WITH CHECK OPTION causes the WHERE clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved. In a sense, it makes the WHERE clause a two-way restriction.
The WITH CHECK OPTION is useful when the view should limit inserts and updates with the same restrictions applied to the WHERE clause.
To understand the need for the WITH CHECK OPTION, you must first understand how views function without the CHECK OPTION. The following view generates a list of Product SubCategories for the Bikes Product Category:
USE AdventureWorks2008R2 GO CREATE view vComponentsProductSubCats AS SELECT ProductCategoryID, Name ProductSubCategory FROM Production.ProductSubcategory WHERE ProductCategoryID = 1; GO SELECT ProductCategoryID, ProductSubCategory FROM dbo.vComponentsProductSubCats Results: ProductCategoryID ProductSubCatgory ------------------- --------------------- 1 Mountain Bikes 1 Road Bikes 1 Touring Bikes
If someone adds a Bike Pedal Accessory and inserts using the view without the CHECK OPTION, the INSERT is permitted.
INSERT INTO vComponentsProductSubCats(ProductCategoryID, ProductSubCategory) VALUES(2, ‘Bike Pedal'); (1 row(s) affected)
The INSERT worked, and the new row is in the database, but the row is not visible through the view because the WHERE clause of the view filters out the inserted row. ...