Devising a View

Congratulations to Emmanuel Nanchen, analyst/programmer at Manpower Switzerland, and Vishal Gupta, a data architect and DBA at Nexgenix in Irvine, California. Emmanuel won first prize of $100 for the best solution to the July Reader Challenge, "Devising a View." Vishal won second prize of $50. Here’s a recap of the problem and the solution to the July Reader Challenge.

Problem

Ian is the database developer for several SQL Server 2000 and 7.0 installations. As part of his job managing the company’s sales database, he controls security by enforcing Sales and Manager database roles. The database holds two tables: a sales force login table called SalesUser and a sales assignments table called Items that contains a separate list for each salesperson. First, Ian created the login table with relevant columns as follows:

CREATE TABLE SalesUser (
   UserID int PRIMARY KEY ,
   UserName varchar( 128 ) NOT NULL DEFAULT ( SYSTEM_USER ) UNIQUE
   CLUSTERED
)

Then, Ian created the sales assignment table with relevant columns as follows:

CREATE TABLE Items (
   ItemID int IDENTITY PRIMARY KEY,
   ItemDate datetime DEFAULT( CURRENT_TIMESTAMP ),
   Comment varchar( 255 ) NULL,
   UserID int REFERENCES SalesUser
)

Help Ian design a view called UserItems that

lists only the assignments for each salesperson
uses the existing tables
lets the salespeople access their assignments from a simple Microsoft Access application. (The sales staff should be able to view, insert, edit, or delete any of their assignments.)
prevents sales employees who are SQL Server savvy from adding, modifying, or deleting data that doesn't apply to them
lets managers access and modify all sales force data
grants the permissions that the following code snippet shows:

GRANT ALL PRIVILEGES ON UserItems TO Manager, Sales

Solution

Ian can start creating the view by listing the assignments of each person in the SalesUser table, as the following example shows:

CREATE VIEW UserItems (
     ItemID, ItemDate, Comment, UserID
)
AS
SELECT i.ItemID, i.ItemDate, i.Comment, i.UserID
  FROM Items AS o
  WHERE i.UserID = ( SELECT u.UserID FROM SalesUser AS u 
  WHERE u.UserName =SYSTEM_USER 
)

The view lets users who are listed in the SalesUser table access only their own assignments.

To prevent users from adding activities that don’t apply to them, Ian appends CHECK OPTION at the end of the view. The CHECK OPTION clause prevents users from entering data that they can’t see or query through the view. In addition, he adds the T-SQL is_member() function to the WHERE clause as a check mechanism. Ian uses is_member() to confirm that the current database user is a member of the specified database role or Windows NT group. That way, he feels comfortable granting any member of the manager role permission to view and modify all assignments. Here’s the modified view:

CREATE VIEW UserItems (
        ItemID, ItemDate, Comment, UserID
)
AS
SELECT i.ItemID, i.ItemDate, i.Comment, i.UserID
  FROM Items AS i
WHERE i.UserID = ( SELECT u.UserID FROM SalesUser AS u WHERE u.UserName =
SYSTEM_USER ) OR
       IS_MEMBER( 'Manager' ) = 1
WITH CHECK OPTIONS

August Challenge

Now, test your SQL Server savvy in the August Reader Challenge, "Speeding Up the Query" (below). Submit your solution in an email message to [email protected] by July 18. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: While troubleshooting a slow distributed query in his application code, Donald finds that SQL Server is choosing an inefficient execution plan. The distributed query performs a join between a large table residing on remote server Prod1 and a small table residing on local server Prod2 and retrieves most columns from the remote table. However, SQL Server retrieves all the remote table’s rows and performs the join on the local server.

To debug the query, Donald uses Northwind’s Orders and Order Details tables to create a test scenario. On the remote server, he creates the following test table:

-- On remote server Prod1
USE Northwind
SELECT od.OrderID, od.ProductID, od.UnitPrice, od.Quantity, od.Discount
  INTO \[New Order Details\]
  FROM (
         SELECT TOP 500000 od1.OrderID, od1.ProductID, od1.UnitPrice,
		od1.Quantity,
od1.Discount
          FROM \[Order Details\] AS od1
			CROSS JOIN \[Order Details\] AS od2
			ORDER BY od1.OrderID
			) AS od
CREATE CLUSTERED INDEX IX_NewOrderDetail_ID ON \[New Order Details\](
 OrderID )

Then on the local server, he writes the following test query, which resembles his production distributed query:

-- On local server Prod2
SELECT o.OrderID, od.ProductID, od.UnitPrice, od.Quantity, od.Discount
  FROM Northwind.dbo.Orders AS o
JOIN Prod1.Northwind.dbo.\[New Order Details\] AS od
ON od.OrderID = o.OrderID

Help Donald improve his slow distributed query’s I/O performance and execution time.

Comments

Plain text