Example syntax for creating a view of the Northwinds database:
CREATE VIEW vwCustomerAddressList AS
SELECT CompanyName, ContactName, Address, City, Region, PostalCode, Country
FROM Customers
The view defined above eliminates some of the columns from the original Customers table. The database administrator could then prohibit some users from accessing the Customers table by providing access to this view instead. This is an example of column-level security.
- Now, design a new view that implements row-level security and joins two tables. Build the view that shows the above customer information (in the example syntax) for all orders associated with employeeID of "1." The security is enforced by using views to only return the rows that you want the current user to see.
- Next, design a view that displays the above customer information for all orders associated with the employee by the last name of "Davolio."
- How would you make it so that a combination of a stored procedure and a view could be used to display the above information for any employee ID passed as a parameter to the stored procedure? Implement your design.
- Finally, another purpose of views is to summarize data for the user, without the user having to know how to do the summarization herself. Create an aggregate view that summarizes the total dollar amount ordered by each customer. List the company name and the total dollar amount ordered by each customer. Name the first column "Customer Name" and the second column "Total Customer Orders."