Views are objects within SQL Server that provide a
layer of abstraction between the end users and the underlying schema.
Rather than directly access the base table, the users query a
"virtualized table" that holds only the data that is specific to their
needs.
Users can execute SELECT statements
against a view in the same way that they would if they were querying the
underlying tables. If the query that is used to create the view is an
updateable query, in other words, one that references a single base
table and does not present aggregated data, then UPDATE, INSERT and DELETE statements can also be executed against the view. It is worth noting that executing an INSERT
statement against a view that does not contain all of the underlying
table's columns will result in an error if the columns that are not
included in the view do not allow nulls and do not have a default value.
This abstraction of the
database schema, using views, means that data can be represented in a
more friendly way to the end user. The query that defines the view can
perform any required aggregation of the data, thus saving the user from
having to perform complex joins, summing, grouping and filtering to
return the required data. Views are often used to report sales and to
identify trends to which management needs to respond.
Views also have an
important security function. As well as providing a useful reporting
mechanism for end users, they provide a mechanism by which to prevent
those end users viewing any data that their role does not have
authorization to access. If a user had direct access to a table, say our
Credit_Report table, which contained a
mix of low and high sensitivity columns then it would be difficult to
prevent the user from viewing the high-sensitivity items. Instead, we
can create a view that exposes only the low-sensitivity columns and give
the role permission to query that view, rather than the underlying
table, as illustrated in Figure 1.
Therefore,
through a view, the security administrator can allow users access to the
aggregated data they need for reporting, while obscuring the structure
of the schema and reducing the risk of accidental or intentional
disclosure of sensitive data.
Creating Views
Views can be created by using the CREATE VIEW method. The following is an example of the syntax of this method:
CREATE VIEW [View Name] AS [Select Statement]
This method's arguments are:
In our sample database, we will create a view as shown in Listing 1, which will present the basic borrower information without revealing any sensitive data:
Once the view is created we can assign permissions to the view.
Assigning Permissions to Views
As a result of that
process, we created some Database Roles and added some SQL Server Logins
as members. These roles were designed to manage the permissions to
database objects; therefore controlling the disclosure of sensitive
data.
In our sample database, we have
utilized views to abstract the architecture of our schema. We will not
grant the Database Roles permission to access the table objects. If a
user, other than the database owner, were to access the database through
SQL Server Management Studio and try to view the table objects, none
would appear. Instead, we will grant permission to access only the view
objects. When stored procedures and user defined functions are created
they too will have the appropriate permissions granted to them.
Tthe columns used in our view, named vwBorrower,
contain data that is classified as "medium" and so we will need to
grant permissions to access this view to the Database Role named Sensitive_medium.
For data integrity purposes, we do not want users deleting records. Also, the vwBorrower view is not updatable since it contains joins to other tables; therefore, only the SELECT privileges are granted, as shown in Listing 2.
Since the Database Role named Sensitive_high is included as a member of the Sensitive_medium Database Role, we do not need to explicitly grant permissions to the Sensitive_high Database Role.
At this point, every login that is a member of the Sensitive_meduim or Sensitive_high role will have permission to access our view. However, let's say we have a specific user, with a SQL Server Login of JOHNSONTE, who meets the overall requirements for membership of the Sensitive_medium role but should be restricted to specific data due to other policies. In this case, the internal policy dictates that JOHNSONTE should not have access to details regarding a borrower's loan history.
To deny the SQL Server Login JOHNSONTE of SELECT privileges to the vwBorrower view, the command shown in Listing 3 would be executed.
At some point during the course of business, the previous internal
policy has been changed and this user is now allowed access to loan
data. To remove the previous DENY that was implemented we will use the REVOKE command. This command removes any previously granted or denied permissions. The use of the REVOKE command is illustrated in Listing 4:
The result of this action removes the restriction to that view and his login is now consistent with the other members of the Sensitive_medium Database Role.
