Views Provide Tailored Security

Use views to give users access to only one portion of a table.

You can define a portion in terms of rows or columns. For example, you may want to disallow a group of users from seeing the Salary column of an Employees table, or you may want to limit a user to see only the rows of a table that he or she 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.

Create a user ID for the sales manager, create views that provide the information needed, and grant the appropriate privileges to the sales manager user ID.

As a user with the MANAGE ANY USER system privilege, create the new user ID using the GRANT statement:
```
CONNECT "DBA" 
IDENTIFIED by sql;
GRANT CONNECT 
TO SalesManager 
IDENTIFIED BY sales
```
Enclose DBA in quotation marks because it is a SQL keyword.
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.
Give SalesManager privilege to look at the view:
```
GRANT SELECT 
ON emp_sales 
TO SalesManager 
```
Use the same command to grant privilege on a view as to grant privilege on a table.

Example 2

This 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 order_summary AS
SELECT OrderDate, Region, SalesRepresentative
FROM "GROUPO".SalesOrders
	KEY JOIN "GROUPO".Customers

Grant privilege for SalesManager to examine this view.
```
GRANT SELECT
ON order_summary
TO SalesManager
```
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 privileges have been granted to SalesManager to look at the underlying tables. These commands produce privilege errors:

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

These examples show how to use views to tailor SELECT privileges. You can grant INSERT, DELETE, and UPDATE privileges on views in the same way.