Using views for tailored security

Views are computed tables that contain a selection of rows and columns from base tables. Views are useful for security when it is appropriate to give a user access to just one portion of a table. The portion can be defined in terms of rows or in terms of columns. For example, you may wish to disallow a group of users from seeing the Salary column of an Employees table, or you may wish to limit a user to see only the rows of a table that they have created.

The sales manager needs access to information in the database concerning employees in the department. However, there is no reason for the manager to have access to information about employees in other departments.

This example describes how to create a user ID for the sales manager, create views that provide the information she needs, and grants the appropriate permissions to the sales manager user ID.

1. Create the new user ID using the GRANT statement, from a user ID with DBA authority. Enter:

   CONNECT "DBA" 
   IDENTIFIED by sql;
   GRANT CONNECT 
   TO SalesManager 
   IDENTIFIED BY sales
   

   You must enclose DBA in quotation marks because it is a SQL keyword, just like SELECT and FROM.

2. Define a view that looks only at sales employees:

   CREATE VIEW emp_sales AS
   SELECT EmployeeID, GivenName, Surname
   FROM "DBA".Employees
   WHERE DepartmentID = 200
   

   Identify the table as “DBA”.Employees, with the owner of the table explicitly identified, so that the SalesManager user ID can use the view. Otherwise, when SalesManager uses the view, the SELECT statement refers to a table that the user ID does not recognize.

3. Give SalesManager permission to look at the view:

   GRANT SELECT 
   ON emp_sales 
   TO SalesManager 
   

   Use the same command to grant permission on a view as to grant permission on a table.

The next example creates a view which allows the Sales Manager to look at a summary of sales orders. This view requires information from more than one table for its definition:

1. Create the view.

   CREATE VIEW order_summary AS
   SELECT OrderDate, Region, SalesRepresentative
   FROM "GROUPO".SalesOrders
   	KEY JOIN "GROUPO".Customers
   

2. Grant permission for the Sales Manager to examine this view.

   GRANT SELECT
   ON order_summary
   TO SalesManager
   

3. To check that the process has worked properly, connect to the SalesManager user ID and look at the views you have created:

   CONNECT SalesManager IDENTIFIED BY sales ;
   SELECT * FROM "GROUPO".emp_sales ;
   SELECT * FROM "GROUPO".order_summary ;
   

No permissions have been granted to the Sales Manager to look at the underlying tables. The following commands produce permission errors.

SELECT * FROM "DBA".Employees ;
SELECT * FROM "DBA".SalesOrders;

The previous example shows how to use views to tailor SELECT permissions. INSERT, DELETE, and UPDATE permissions can be granted on views in the same way.