Using Views as a Security Mechanism

Views also can be used to limit access to data and simplify permission management. One advantage of using views as a security mechanism is that if the view and its underlying base tables are owned by the same user, no permissions need be granted on the underlying base tables themselves. All permissions can be granted or denied at the view level. The theory here is that if you own the tables or other views that a view accesses, and you grant permissions on the view, you must have wanted to give access to the data stored in the base tables. Therefore, no further permission checking is done. If, however, you have various owners for the objects that a view references, permissions must be checked at each level. If access is denied at any level, access to the view is denied. This is referred to as a broken ownership chain, and it can be a nightmare to administer and keep all permissions configured properly. This can easily be avoided by ensuring that all views and tables are created with the dbo user as the owner.

Views can be used to implement column-level security or row-level security.

Views for Column-Level Security

If users need to access certain columns in a table but should be restricted from accessing columns containing sensitive data, a view can be defined on only the required columns. Listing 27.2 shows a statement that creates a view called contactview, which returns only the required contact information from the customers table. It then grants SELECT permission on the view to the sales role, and revokes SELECT permission on the Customer table from the sales role. Users assigned the sales role would now only be able to view these five columns of data from the Customers table by using the view.

Listing 27.2 Creating a View to Limit Column Access

CREATE VIEW dbo.contactview
AS
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Phone
   FROM  dbo.Customers
Go
GRANT SELECT ON contactview TO sales
REVOKE SELECT on Customers FROM sales
Go

The alternative to using views to limit column access would be to define column-level permissions on the table, granting permissions on the individual columns that the users should be able to access. This can be an administrative nightmare because you are managing permissions at a far more granular level. Performance may suffer because permission checking would now be done at the column level as well as at the table level. In addition, users without permissions on all columns would no longer be able to issue a SELECT * statement against the table and would have to explicitly list the columns they have access to in their queries.

Views for Row-Level Security

Views can also be used to limit access to specific rows in a base table. In SQL Server 2000, this is currently the only method of implementing any form of row-level security. You can enforce row-level security by including a WHERE clause in the view, which limits the rows that are to be returned by the view. Listing 27.3 provides an example of a view called USA_contactview, which returns only the required contact information from the customers table where the customer's country is 'USA'. It then grants SELECT permission on the view to the usa_sales role, and revokes SELECT permission on the Customer table from the usa_sales role. Users assigned the usa_sales role are now only able to view data rows from the Customers table, via the USA_contactview view, where the country is USA.

Listing 27.3 Creating a View to Limit Row Access

/* create view that allows access to only USA Customers */
CREATE VIEW dbo.USA_contactview
AS
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Phone, Country
   FROM  dbo.Customers
   WHERE Country = 'USA'
Go
GRANT SELECT ON dbo.USA_contactview TO usa_sales
REVOKE SELECT on dbo.Customers FROM usa_sales
GRANT UPDATE ON dbo.USA_contactview TO usa_sales
REVOKE UPDATE on dbo.Customers FROM usa_sales
GRANT INSERT ON dbo.USA_contactview TO usa_sales
REVOKE INSERT on dbo.Customers FROM usa_sales
Go

If you used the approach presented in Listing 27.3 to implement row-level security, you would need a separate view for each country or group of countries you wanted to restrict access to. You can implement a more dynamic solution using views if your database provides information on the users and which data they should have access to. For example, in the Northwind database, the Employees table stores the country for each employee. This information can be used to determine which data each employee is allowed to see in the Customers table. Assuming the last name is also the user's SQL Server login name, the view shown in Listing 27.4 uses the user's login name along with a join to the Employee table to dynamically restrict which customers a user can see through the view.

Listing 27.4 Creating a Dynamic View to Limit Row Access

/* create view that restricts the columns and rows users are
   allowed to access in Customers */
if object_id('dbo.contactview') is not null
   and objectproperty (object_id('dbo.contactview'), 'IsView') = 1
    DROP VIEW dbo.contactview
Go
CREATE VIEW dbo.contactview
AS
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Phone, c.Country
   FROM dbo.Customers c
   JOIN dbo.Employees e
     on c.Country = e.country
   WHERE e.LastName = suser_sname()  --login name of the current user
Go
GRANT SELECT ON contactview TO sales
REVOKE SELECT on Customers FROM sales
GRANT UPDATE ON contactview TO sales
REVOKE UPDATE on Customers FROM sales
GRANT INSERT ON contactview TO sales
REVOKE INSERT on Customers FROM sales
Go