t is very common to encounter situations where users need to query a database directly. Even though you’ve created 100 reports in a given application, there will always be the need for that 101st report you didn’t create. Allowing users to write their own reports poses several challenges, however.
For starters, your data model can be quite complicated. How can you simplify your users’ view of the data so that they can understand it well enough to obtain the information they need? Additionally, you need to protect users from seeing sensitive information that they are not authorized to see.
In this 10-Minute Solution I will cover some of the basics of views and show you how to use them to solve thee two issues I raised above. My examples are based on the sample pubs database that works with SQL Server 7 and SQL Server 2000. 
How do you allow users to query your database directly without confusing them with the data model or compromising sensitive data?
Use views to allow users to access data directly:
- Views allow users to query complex data models easily.
- Views allow you to filter information so that sensitive data remains hidden.
A SQL Server view is simply a query that has been predefined, named, and stored in SQL Server. Unlike a stored procedure or function, however, a view can be treated as a table. This allows you to simplify your data model for the user.
This is best illustrated with a simple example. Suppose you wanted to display the author’s name and the book’s title in the pubs database. This requires a three-table join between the authors, titleauthor, and title tables in the pubs database. Instead of joining the tables each time you want this information, you could create this view:
create view vw_authorbooksasselect au_fname as ‘Fname’, au_lname as ‘Lname’,titlefromauthors ainner join titleauthor ta on(a.au_id = ta.au_id)inner join titles t on (ta.title_id = t.title_id)select *from vw_authorbooksthisSimplifying Access to Your Data
The example above illustrates the most common use of views: to simplify the query necessary to retrieve information. Does this mean you should replace all queries that need joins with views?
Definitely not! Programmers should be able to handle multi-join queries. With few exceptions, you, as the database designer, will probably not find it necessary to use a view to simplify queries for programmers building applications that retrieve information from a database. Views are most useful whenever you have users querying the database directly. Even though there are all sorts of point-and-click reporting tools meant to be used by “power users,” a non-programmer can still find it difficult to build the correct query for the problem he or she has.
Consider a user of the pubs database who is trying to assess the importance of various authors to the business. He’s decided that it would be useful to see the author’s name, book’s name, number of books published by that author, and the total sales for the particular book. The query needs several different joins and two subqueries as well.
Rather than give a reason for a frustrated user to call and berate you for the complexity of your database (or to point out a “bug” in your system because his poorly designed query returns clearly incorrect numbers) simply create a view instead, using the code in Listing 1. Then the user can simply select the necessary result with:
Another benefit to providing a prebuilt view to the user is that you can tune the view and ensure that it performs well before the user executes it. This prevents the possibility of a user’s badly written, but functional, query from dragging down the performance of the system.Securing Your Dataselect * from vw_authsales
Views are good at securing sensitive data. Consider a table that contains the name, department, office extension, and other personal information of a company’s employees. You might want that information accessible to everyone in the company. But what if that table also contains everyone’s salary? Clearly that sort of information would be restricted.
Instead of granting permission to various users to query the table directly, use views to restrict views:
- Create one view of the columns that everyone should be allowed to see and grant access for that view to the “public” group.
- Create a second view with the more sensitive information and grant rights for querying that view only to the people who should have access to it.
Listing 2 shows some sample code that creates a “finance” group, creates a table called “employees,” and creates two views that query the table. The “public” group is granted rights to a restricted view, while the “finance” group is granted rights to a second view containing the salary information.
Securing Specific Rows of Information
Another common task that views can be useful for is applying security rules to specific rows. For example, you might want to allow authors to query the sample pubs database, which contains book sales information, but limit each author’s access to data for his or her books only.
To accomplish this task, assign each author a unique user name, which is a combination of the first name and last name. Then create a view that selects only the books where the author’s first name plus last name is equal to the username (see Listing 3). The username can be retrieved by the suser_sname() function.
To test this view, you need to create a login and a user equal to the first name and last name of one of the authors, as in this code:
use pubsgogrant select on vw_salesinfo to publicexec sp_addlogin @loginame = ‘AnnDull’, @passwd = ‘password’, @defdb = ‘pubs’ exec sp_grantdbaccess ‘AnnDull’select * from vw_salesinfoAs you’ve seen, views are a powerful tool that make your database more accessible and useful to your end users while at the time providing you, as the designer and administrator, with valuable protection against performance or security issues. Views should be a part of every good modeler’s tool kit.
