Database Reference
In-Depth Information
Let's assume that you have a system that displays the company directory on their intranet. You can define the
view that selects public information from the table, filtering out the employees who do not want their profiles to
be published, and then, grant users select permission on the view rather than on the table. You can see the code in
Listing 9-2.
Listing 9-2.
Views and Security: View creation
create view dbo.vPublicEmployeeProfile(EmployeeId, Name, Position, Email)
as
select EmployeeId, Name, Position, Email
from dbo.Employee
where PublishProfile = 1
go
grant select on object::dbo.vPublicEmployeeProfile to [IntranetUsers]
While you can accomplish this task without the view with column-level permissions and additional filter in the
queries, the view approach is simpler to develop and maintain.
Another benefit of views is abstracting the database schema from the client applications. You can alter the
database schema, keeping it transparent to the applications by altering the views and changing the underlying
queries. It is then transparent to the client applications as long as the views interface remain the same.
In addition, you can hide complex implementation details and table joins and use views as a simple interface to
client applications. That approach is a bit dangerous, however. It could lead to unnecessary performance overhead if
we are not careful.
Let's look at a few examples. Let's assume that you have Order Entry system with two tables:
Orders
and
Clients
.
The code to create these tables is shown in Listing 9-3.
Listing 9-3.
Views and Joins: Tables creation
create table dbo.Clients
(
ClientId int not null,
ClientName varchar(32),
constraint PK_Clients
primary key clustered(ClientId)
);
create table dbo.Orders
(
OrderId int not null identity(1,1),
Clientid int not null,
OrderDate datetime not null,
OrderNumber varchar(32) not null,
Amount smallmoney not null,
constraint PK_Orders
primary key clustered(OrderId)
);
Let's create a view that returns orders information, including client names, as shown in Listing 9-4.
