SQL Security and Views

One of the most frequently asked questions is why a view will not allow updates. One final area to review if your view is non-updateable is the use of formulas. You may recall that you can create a column on the fly by indicating, for example, that a column is derived by multiplying two other columns and providing the result.
Since the column is derived by using other values from the table, this will force the view to be read-only. The view can not update the underlying table because the database engine could not know how to apply an update to the values included in the formula for the derived column.

A view can be used in a query that updates data, subject to a few restrictions. Remember that a view is not a table and contains no data, the actual modification always takes place at the table level. Views cannot be used as a mechanism to override any constraints, rules, or referential integrity defined in the base tables.

You can insert, update, and delete rows in a view, subject to the following limitations:

1. If the view contains joins between multiple tables, you can only insert and update one table in the view, and you cannot delete rows.
2. You cannot directly modify data in views based on union queries. You cannot modify data in views that use GROUP BY or DISTINCT statements.
3. All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
4. Text and image columns cannot be modified through views.
5. There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.

The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view. Listing 9.12 creates a view showing customers from Paris using the WITH CHECK OPTION statement.

CREATE VIEW vwCustomersParis
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers
 WHERE City = 'Paris'
WITH CHECK OPTION