Today's SQL Server tip

USING VIEWS FOR SECURITY V7.0
Most security is performed using SQL Server's own native security 
system. You can, however, minimize some aspects of this system while 
maintaining a security scenario - by creating views to protect your 
data. Consider the following.

You have an Employee table that houses pertinent information about all 
your employees - name, address, telephone, salary, etc. If you were to 
arbitrarily GRANT SELECT permission on the Employee table to a group of 
users, you could be opening a "personal" security hole larger than you 
may realize. While application security is maintained with this 
scenario, personal security is not.

The advantages of using a view to access the information to the 
Employee table becomes apparent when you assess the sensitive 
information contained within the Salary field.

CREATE VIEW vEmployee 
AS 
SELECT Name, Address, Telephone FROM Employee 
GO 
GRANT SELECT ON vEmployee TO <user> 
GO

This simple view obviously eliminates the Salary field from the 
display. This is called a "vertical restriction."

A user can now SELECT * from vEmployee and retrieve the information he 
or she needs while protecting the sensitive information of Salary.

You can provide an equally protective scheme of only GRANTing column-
level permissions on the Employee table. Note that this will prevent 
users from being able to SELECT * FROM the Employee table. The lack of 
column-level permission on the Salary column prevents users from 
obtaining the information with this method. A user would have to know 
in advance which columns he or she could access in order to retrieve 
the salary information.

------------------------------------------