User's Guide

Part II. Tutorials

Chapter 11. Introduction to Views

Example

Views can be used to restrict access to information in the database. For example, suppose you wanted to create a user ID for the sales department head, Moira Kelly, and restrict her user ID so that it can only examine information about employees in the sales department.

Creating the new user ID

First you need to create the new user ID for Moira Kelly using the GRANT statement. From ISQL, connected to the sample database as dba, enter the following:

     GRANT CONNECT TO M_Kelly
     IDENTIFIED BY SalesHead

Granting permissions

Next you need to grant user M_Kelly the right to look at employees of the sales department.

     CREATE VIEW SalesEmployee AS
     SELECT emp_id, emp_lname, emp_fname
     FROM "dba".employee
     WHERE dept_id = 200

The table should be identified as "dba".employee for the M_Kelly user ID to be able to use the view.

Now you must give M_Kelly permission to look at the new view by entering:

     GRANT SELECT ON SalesEmployee TO M_Kelly

Looking at the view

Connect to the database as M_Kelly and now try looking at the view:

     CONNECT USER M_Kelly IDENTIFIED BY SalesHead ;
     SELECT * FROM "dba".SalesEmployee

emp_id	emp_lname	emp_fname
129	Chin	Philip
195	Dill	Marc
299	Overbey	Rollin
467	Klobucher	James
641	Powell	Thomas

However, you do not have permission to look directly at the employee and department tables. If you execute the following commands, you will get permission errors.

     SELECT * FROM "dba".employee ;
     SELECT * FROM "dba".department