Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Using Views

8/6/2011 4:02:45 PM
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.

Figure 1. Illustration of a view.

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:

  • View Name: The textual reference to the view. This should include the schema in which the view belongs.

  • Select Statement: The select statement that is executed to present the data in the view.

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:

Listing 1. Creating a view in the HomeLending database.

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.

Listing 2. Granting to the database role, Sensitive_medium, permission to select on the view vwBorrower.

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.

Listing 3. Denying SELECT privileges to the SQL Server login JOHNSONTE.

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:

Listing 4. Returning SELECT privileges to JOHNSONTE.

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.

  •  Creating and Managing Views in SQL Server 2008 : Indexed Views
  •  Transact-SQL in SQL Server 2008 : Row Constructors
  •  Transact-SQL in SQL Server 2008 : GROUP BY Clause Enhancements
  •  Creating and Managing Views in SQL Server 2008 : Partitioned Views
  •  Creating and Managing Views in SQL Server 2008 : Managing Views, Data Modifications and Views
  •  Creating and Managing Views in SQL Server 2008 : Creating Views
  •  Creating and Managing Views in SQL Server 2008 : Definition of Views & Using Views
  •  Transact-SQL in SQL Server 2008 : Insert over DML
  •  Transact-SQL in SQL Server 2008 : MERGE Statement
  •  SQL Server 2008 : Transact-SQL Programming - The TABLESAMPLE Clause
  •  SQL Server 2008 : Transact-SQL Programming - TRY...CATCH Logic for Error Handling
  •  SQL Server 2008 : Transact-SQL Programming - The APPLY Operator
  •  SQL Server 2008 : Transact-SQL Programming - PIVOT and UNPIVOT
  •  SQL Server 2008 : Transact-SQL Programming - Ranking Functions
  •  SQL Server 2008 : Transact-SQL Programming - Common Table Expressions
  •  SQL Server 2008 : Transact-SQL Programming - The OUTPUT Clause
  •  SQL Server 2008 : Transact-SQL Programming - TOP Enhancements
  •  SQL Server 2008 : Transact-SQL Programming - The max Specifier
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 3) - Generating T-SQL Statements with T-SQL & De-Duping Data with Ranking Functions
  •  SQL Server 2008 : T-SQL Tips and Tricks (part 2) - Using CONTEXT_INFO & Working with Outer Joins
    Top 10
    Exchange Server 2010 : Day-to-day DAG management and operations (part 4) - DAG networks
    Exchange Server 2010 : Day-to-day DAG management and operations (part 3) - Investigating DAG problems & Managing DAG properties
    Exchange Server 2010 : Day-to-day DAG management and operations (part 2) - Building the DAG
    Exchange Server 2010 : Day-to-day DAG management and operations (part 1)
    Windows 7 : Windows Driver Foundation Architecture (part 4) - Tools for Development and Testing
    Windows 7 : Windows Driver Foundation Architecture (part 3) - Driver Frameworks
    Windows 7 : Windows Driver Foundation Architecture (part 2) - Integrated I/O Queuing and Cancellation
    Windows 7 : Windows Driver Foundation Architecture (part 1)
    SharePoint 2010 : Security - Secure Store Service & Using SSS with BCS
    SharePoint 2010 : Security - Claims Based Authentication
    Most View
    iPhone 3D Programming : Anti-Aliasing Tricks with Offscreen FBOs (part 1) - A Super Simple Sample App for Supersampling
    SQL Server 2008 : Explaining XML - Well-Formed XML
    Configuring Power Management Settings in Vista
    Business Intelligence in SharePoint 2010 with Business Connectivity Services : Consuming External Content Types (part 1) - External Lists & External Data
    Windows Server 2008 : Install the Hyper-V Role
    Programming the Mobile Web : WebKit CSS Extensions (part 4) - Animations
    Windows 7 : Understanding TCP/IP (part 2)
    Exchange Server 2010 : Implementing Client Access and Hub Transport Servers - Transport Pipeline
    iPhone Application Development : Using Switches, Segmented Controls, and Web Views (part 1)
    jQuery 1.3 : Table Manipulation - Sorting and paging (part 2) : Server-side pagination & JavaScript pagination
    Windows 7 : Understanding Home and Small-Business Networks
    Using the iPhone Simulator
    Installing Windows Server 2008 R2 and Server Core : Managing and Configuring a Server Core Installation
    Building Your First Windows Phone 7 Application (part 3) - Writing Your First Windows Phone Code
    SQL Azure : Database-Migration Strategies
    Troubleshooting Startup and Shutdown
    Silverlight : Capture a Webcam
    Defensive Database Programming with SQL Server : Using TRY...CATCH blocks to Handle Errors
    Mobile Application Security : SMS Security - Protocol Attacks (part 2)
    Algorithms for Compiler Design: STACK ALLOCATION