Security: Use views and procedures to limit data users can access

For databases that require a high level of security, granting access directly to tables has limitations. Any privilege granted to a user on a table applies to the whole table. There are many cases when users' privileges need to be shaped more precisely than on a table-by-table basis. For example:

It is not desirable to give access to personal or sensitive information stored in an employee table to users who need access to other parts of the table.
You may want to give sales representatives update privileges on a table containing descriptions of their sales calls, but limit such privileges to their own calls.

While views restrict data access, procedures restrict the actions a user may take. A user can have EXECUTE privilege on a procedure without having any privileges on the table or tables on which the procedure acts.

For strict security, you can disallow all access to the underlying tables, and grant privileges to users or groups of users to execute certain stored procedures. This approach strictly defines how data in the database can be modified.

Examples

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 privileges to the sales manager user ID.

Connect and create the new user ID using the GRANT statement:

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 privilege to look at the view:
GRANT SELECT ON EmployeeSales TO SalesManager;
You use exactly the same statement to grant privilege on views and on tables.

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 privilege 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 privileges have been granted to the Sales Manager to look at the underlying tables. The following statements produce privilege errors.

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

Security: Procedures and Triggers

Discuss this page in DocCommentXchange.