You then create the view by running this code:
CREATE VIEW VSortedT1OPENQUERY AS
SELECT * FROM
OPENQUERY([<your_server_name>],
N'SELECT * FROM tempdb..T1
ORDER BY keycol')
AS T
If you query the view, you get a sorted output, but you can also update the view. Run the following UPDATE statement to set datacol to bb in the row for which keycol equals 2:
UPDATE VSortedT1OPENQUERY
SET datacol = 'bb'
WHERE keycol = 2
Your modification should run successfully. The downside of using this technique—besides it not being ANSI-compliant—is that any reference to the view invokes a distributed query, which is more costly than a local one.
VIEW_METADATA
The last back door I discuss causes your views to behave in unexpected ways when you manipulate them from graphical tools. I first describe three seemingly unrelated characteristics of a view, then correlate them all through the back door.
The first characteristic is a view's use as a security mechanism. You can grant a user permission to select, insert, update, or delete data from a view without granting access to the base table the view refers to as long as the view and the table have the same owner.
The second characteristic has to do with modifying a view that accesses several tables. Although views are supposed to look and act much like tables do, ANSI intentionally imposes some differences that SQL Server implements. If a view joins tables, you're not allowed to modify more than one base table in one UPDATE or INSERT statement you issue against the view.
The third characteristic has to do with issuing modifications through a view (not to the base table directly) that conflict with the view's query filter. When you issue such modifications, inserted or modified rows seem to disappear from the view. Suppose you created a view that selects all rows from the T1 table I used earlier, using the filter data-col LIKE 'a%'. If you insert into the view a row that has a value of d in datacol, the insertion succeeds. But when you query the view, you don't get the newly inserted row because it doesn't qualify for the view's query filter. To disallow modifications that conflict with the view's query filter, add WITH CHECK OPTION when creating the view. Run the following code to create the view VT1OnlyA, specifying the CHECK option:
CREATE VIEW VT1OnlyA AS
SELECT * FROM T1
WHERE datacol LIKE 'a%'
WITH CHECK OPTION
This view retrieves all rows in which the datacol value starts with a. Run the following INSERT statement to add a row that doesn't conflict with the filter:
INSERT INTO VT1OnlyA
VALUES(4, 'aa')
The statement completes successfully. If you query the view, you get two rows with the keycol values 1 and 4 and the datacol values a and aa, respectively. Next, try to run INSERT and UPDATE statements that conflict with the view's query filter:
INSERT INTO VT1OnlyA
VALUES (5, 'd')
UPDATE VT1OnlyA
SET datacol = 'e'
WHERE keycol = 1
You get an error in both cases, indicating that the statements conflict with the specified CHECK option.
Now let's try the back door. If you perform the modifications through a graphical interface such as Enterprise Manager, the view behaves differently than you intended in respect to the three characteristics I specified. If you have access to the view but not to the base table, you get a security error when you try to modify data through Enterprise Manager. If you try to change several tables by referring to them at the same time through the view in Enterprise Manager, you are successful. But if you insert or modify underlying data through the view in Enterprise Manager, although the modification conflicts with the view's CHECK option, SQL Server won't reject the modification.
The reason views behave differently is that Enterprise Manager requests browse-mode metadata information from SQL Server through the client-side database interface and gets the base tables' metadata information. Enterprise Manager then constructs a query against the base tables instead of against the view, causing the problems.
You can capture the query that Enterprise Manager submits by running a SQL Server Profiler trace while modifying the view. For example, if you run a trace and modify the VT1OnlyA view through Enterprise Manager, you capture the statement that Figure 3 shows. Notice that Enterprise Manager issues an UPDATE statement against the T1 table directly instead of against the view. Fortunately, you can easily prevent such problems. You simply alter the view by using the VIEW_METADATA option:
ALTER VIEW VT1OnlyA
WITH VIEW_METADATA AS
SELECT * FROM T1
WHERE datacol LIKE 'a%'
WITH CHECK OPTION
This option causes SQL Server to send metadata information of the view as if it were a table instead of the base tables' metadata. In this way, you close the back door.
Shut That Door
Although using back doors can be dangerous, you need to be able to recognize them when you run across them in your production databases and be able to provide alternatives. I hope that Microsoft's developers will realize that some of these SQL Server back doors could be of great value to T-SQL programmers as supported features.
End of Article
Prev. page
1
[2]
next page -->
