Controlling Data Access With Views in SQL

Database views reduce the complexity of the end-user experience and limit users' access to data contained in database tables. Essentially, a view uses the results of a database query to dynamically populate the contents of a virtual database table.

Why Use Views?

There are two primary reasons to provide users with access to data through views rather than providing them with direct access to database tables:

• Views provide simple, granular security. Use a view to limit the data that a user is allowed to see in a table. For example, if you have an employees table and wish to provide some users with access to the records of full-time employees, you can create a view that contains only those records. This is much easier than the alternative (creating and maintaining a shadow table) and ensures the integrity of the data.
• Views simplify the user experience. Views hide complex details of your database tables from end-users who do not need to see them. If a user dumps the contents of a view, they won’t see the table columns that aren’t selected by the view and they might not understand. This protects them from the confusion caused by poorly named columns, unique identifiers, and table keys.

Creating a View

Creating a view is quite straightforward: Simply create a query that contains the restrictions you wish to enforce and place it inside the CREATE VIEW command. Here’s the general syntax:

CREATE VIEW viewname AS

For example, to create the full-time employee's view, issue the following command:

CREATE VIEW fulltime AS
SELECT first_name, last_name, employee_id
FROM employees
WHERE status='FT';

Modifying a View

Changing the contents of a view uses the exact same syntax as the creation of a view, but use the ALTER VIEW command instead of the CREATE VIEW command. For example, to add a restriction to the fulltime view that adds the employee’s telephone number to the results, issue the following command:

ALTER VIEW fulltime AS
SELECT first_name, last_name, employee_id, telephone
FROM employees
WHERE status='FT';

Deleting a View

It’s simple to remove a view from a database using the DROP VIEW command. For example, to delete the full-time employee's view, use the following command:

DROP VIEW fulltime;

Views vs. Materialized Views

A view is a virtual table. A materialized view is that same view written to disk and accessed as if it were a table in its own right.

When you run a query against a view, the secondary query that sources the view executes real-time then those results feed back into the original main query. If your views are exceptionally complex, or your main query requires a large number of hash joins among several tables and views, your main query will execute with the speed of a turtle.

A materialized view speeds query execution because it functions as a pre-compiled query written to disk and therefore executes as quickly as a table. However, materialized views are only as good as the event procedures that refresh them. In the long run, with good maintenance, materialized views speed things up with a small trade-off in lag refresh time, without the need for a bunch of shadow tables that may become dormant and either eat disk space or source someone else's queries inappropriately.