RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Satisfy Your Users with Views-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:

  1. Create one view of the columns that everyone should be allowed to see and grant access for that view to the "public" group.

  2. 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:

use pubs go 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.

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date