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.
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.
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
If the table you want to restrict column access to is often used in joins with other tables, be sure to include the table's primary and foreign key fields so that the view can be used in those joins. For example, because the contactview view shown in Listing 27.2 contains the CustomerID column, users would still be able to use the view in joins with other tables in the database such as the Orders table.
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 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.
/* 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.
/* 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
Be careful when using views in joins to provide row-level security. What looks like a simple join between a couple of views might actually resolve to a complex multitable query. One time, I was at a customer site trying to identify query performance problems with their database. They had implemented row-level security across their database using views with joins. Each view contained at least a three-table join. A simple query joining three views together was actually resulting in a nine- or ten-table query when the execution plan was generated. Needless to say, joins of nine to ten tables typically do not provide good performance! At the very least, when implementing this solution, you'll want to make sure that the columns specified in the join clauses are indexed.