Remember that a view is a virtual table, and remember that when you submit a query against a view, SQL Server expands the view’s select statement and issues the query against the underlying tables. A view is not limited to being a target of SELECT queries; it can be a target for modifications, too. When you submit a modification against a view, SQL Server will modify the underlying tables. The view in such a case acts as an agent or a vehicle. Of course, you can limit the data that you’re exposing through the view by allowing modifications through the view but not directly against the underlying tables. This way, the view can play a security role to some degree in terms of privacy and disclosure.

For example, one way to achieve row-level security is by using views.

Caution

In this section, I’ll show the simple use of views to provide row-level security. Note that the technique I will demonstrate is imperfect and might be useful for applications in which security is not a firm requirement. For details about row-level security and the disclosure risk of this security mechanism, please refer to the following white paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx.

The code in Example 5-3 creates a table called UserData with a column called loginname that accepts a default value from the SUSER_SNAME function (current login name). The code creates a view that exposes all attributes except loginname only to the current user by using the filter loginname = SUSER_SNAME(). The code denies data manipulation language (DML) permissions against the table to public, and it grants permissions against the view to public. With these restrictions in place, users can access and manipulate only their own data.

USE tempdb;
GO
IF OBJECT_ID('dbo.VUserData') IS NOT NULL
  DROP VIEW dbo.VUserData;
GO
IF OBJECT_ID('dbo.UserData') IS NOT NULL
  DROP TABLE dbo.UserData;
GO
CREATE TABLE dbo.UserData
(
  keycol    INT         NOT NULL IDENTITY PRIMARY KEY,
  loginname sysname     NOT NULL DEFAULT (SUSER_SNAME()),
  datacol   VARCHAR(20) NOT NULL,
  /* ... other columns ... */
);
GO
CREATE VIEW dbo.VUserData
AS

SELECT keycol, datacol
FROM dbo.UserData
WHERE loginname = SUSER_SNAME()
GO

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.UserData TO public;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.VUserData TO public;
GO

Modifications against views have the following limitations:

Data modification statements in violation of these limitations can be issued if there is an INSTEAD OF trigger on the view. An INSTEAD OF trigger replaces the original modification with your own code. For example, you can write your own code to reverse engineer modifications of columns that result from a calculation and issue the modification directly against the underlying tables. I will discuss triggers in Chapter 8.

Be especially careful when you allow modifications against a view defined by a join query. Users who are not aware that the target object for their modifications is a view and not a table might find the effect of their modifications surprising in some cases–for example, when they modify the "one" side of a one-to-many join.

As an example, run the code in Example 5-4 to create the Customers and Orders tables and the VCustOrders view that joins the two.

SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.VCustOrders') IS NOT NULL
  DROP VIEW dbo.VCustOrders;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
  DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
  DROP TABLE dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
  cid   INT         NOT NULL PRIMARY KEY,
  cname VARCHAR(25) NOT NULL,
  /* other columns */
)
INSERT INTO dbo.Customers(cid, cname) VALUES(1, 'Cust 1'),
INSERT INTO dbo.Customers(cid, cname) VALUES(2, 'Cust 2'),

CREATE TABLE dbo.Orders
(
  oid INT NOT NULL PRIMARY KEY,
  cid INT NOT NULL REFERENCES dbo.Customers,
  /* other columns */
)
INSERT INTO dbo.Orders(oid, cid) VALUES(1001, 1);
INSERT INTO dbo.Orders(oid, cid) VALUES(1002, 1);
INSERT INTO dbo.Orders(oid, cid) VALUES(1003, 1);
INSERT INTO dbo.Orders(oid, cid) VALUES(2001, 2);
INSERT INTO dbo.Orders(oid, cid) VALUES(2002, 2);
INSERT INTO dbo.Orders(oid, cid) VALUES(2003, 2);
GO

CREATE VIEW dbo.VCustOrders
AS

SELECT C.cid, C.cname, O.oid
FROM dbo.Customers AS C
  JOIN dbo.Orders AS O
    ON O.cid = C.cid;
GO

Query the view, and examine its contents, which are shown in Table 5-9.

SELECT cid, cname, oid FROM dbo.VCustOrders;

Notice that customer attributes, such as the company name (cname), are duplicated for each matching order.

Suppose that a user who was granted UPDATE permissions against the view wants to modify the company name to ′Cust 42′, where the order ID (oid) is equal to 1001. The user submits the following update:

UPDATE dbo.VCustOrders
  SET cname = 'Cust 42'
WHERE oid = 1001;

Of course, if the target of the update was a table and not a view, you would have seen only one row with ′Cust 42′ in cname when querying the table. However, the target of the update is a view, and SQL Server modifies the Customers table underneath the covers. In practice, cname is modified for customer 1. Now query the VCustOrders view using the following code and examine the output shown in Table 5-10:

SELECT cid, cname, oid FROM dbo.VCustOrders;

What happened was that the cname value was changed for order 1001. The view’s cname value for order 1001 was the cname value from the Customers table associated with order 1001’s customer (customer number 1, as stored in the Orders table). The view returned customer number 1’s cname value for all three of customer number 1’s orders.

When you’re done, run the following cleanup code:

USE tempdb;
GO
IF OBJECT_ID('dbo.VUserData') IS NOT NULL
  DROP VIEW dbo.VUserData;
GO
IF OBJECT_ID('dbo.UserData') IS NOT NULL
  DROP TABLE dbo.UserData;
GO
IF OBJECT_ID('dbo.VCustOrders') IS NOT NULL
  DROP VIEW dbo.VCustOrders;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
  DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
  DROP TABLE dbo.Customers;
GO