Securing Your Data
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.
You can also grant table permissions directly to people who have the rights to see all data. Whether you choose to use a view in all cases, for consistency's sake, or use views for some users, while allowing others to query the table directly, is a matter of style.
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:
grant select on vw_salesinfo to public
exec sp_addlogin @loginame = 'AnnDull',
@passwd = 'password',
@defdb = 'pubs'
exec sp_grantdbaccess 'AnnDull'
Using Query Analyzer, log in to SQL Server as AnnDull (in this example using SQL Server authentication) and execute the following:
select * from vw_salesinfo
You will see information only about the book(s) that Ann Dull authored.
As 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.