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 as rows or columns. For example, you may want to disallow a group of users from seeing the Salary column of an employee table, or you may want to limit a user to see only the rows of a table they have created.

Example 1

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 grant the appropriate permissions to the sales manager user ID.

Create the new user ID using the GRANT statement. While logged in as a user with DBA authority, execute the following statements:

CONNECT DBA
IDENTIFIED by sql;

CREATE USER SalesManager
IDENTIFIED BY sales;

Define a view that only looks at sales employees as follows:

CREATE VIEW EmployeeSales AS
  SELECT EmployeeID, GivenName, Surname
  FROM Employees
  WHERE DepartmentID = 200;

The table reference could be qualified with the owner to avoid an ambiguous reference to an identically named table.

Give SalesManager permission to look at the view:
GRANT SELECT ON EmployeeSales TO SalesManager;
You use exactly the same command to grant permission on views and on tables.

Example 2

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:

Create the view.

CREATE VIEW OrderSummary AS
  SELECT OrderDate, Region, SalesRepresentative, CompanyName
  FROM SalesOrders
    KEY JOIN Customers;

Grant permission for the Sales Manager to examine this view.
GRANT SELECT ON OrderSummary TO SalesManager;

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

CONNECT SalesManager
IDENTIFIED BY sales;
SELECT *
FROM DBA.EmployeeSales;
SELECT *
FROM DBA.OrderSummary;

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

SELECT * FROM GROUPO.Employees;
SELECT * FROM GROUPO.SalesOrders;

Other permissions on views

Discuss this page in DocCommentXchange.