Controlling access to data with views

A view provides a means of controlling access or extending privileges to a table.

Using a view allows the following kinds of control over access to a table:

Access only to designated columns of the table.
For users and application programs that require access only to specific columns of a table, an authorized user can create a view to limit the columns addressed only to those required.
Access only to a subset of the rows of the table.
By specifying a WHERE clause in the subquery of a view definition, an authorized user can limit the rows addressed through a view.
Access only to a subset of the rows or columns in data source tables or views. If you are accessing data sources through nicknames, you can create local Db2® database views that reference nicknames. These views can reference nicknames from one or many data sources.
Note: Because you can create a view that contains nickname references for more than one data source, your users can access data in multiple data sources from one view. These views are called multi-location views. Such views are useful when joining information in columns of sensitive tables across a distributed environment or when individual users lack the privileges needed at data sources for specific objects.

To create a view, a user must have DATAACCESS authority, or CONTROL or SELECT privilege for each table, view, or nickname referenced in the view definition. The user must also be able to create an object in the schema specified for the view. That is, DBADM authority, CREATEIN privilege for an existing schema, or IMPLICIT_SCHEMA authority on the database if the schema does not already exist.

If you are creating views that reference nicknames, you do not need additional authority on the data source objects (tables and views) referenced by nicknames in the view; however, users of the view must have SELECT authority or the equivalent authorization level for the underlying data source objects when they access the view.

If your users do not have the proper authority at the data source for underlying objects (tables and views), you can:

Create a data source view over those columns in the data source table that are OK for the user to access
Grant the SELECT privilege on this view to users
Create a nickname to reference the view

Users can then access the columns by issuing a SELECT statement that references the new nickname.

The following scenario provides a more detailed example of how views can be used to restrict access to information.

Many people might require access to information in the STAFF table, for different reasons. For example:

The personnel department needs to be able to update and look at the entire table.
This requirement can be easily met by granting SELECT and UPDATE privileges on the STAFF table to the group PERSONNL:
```
   GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL
```
Individual department managers need to look at the salary information for their employees.
This requirement can be met by creating a view for each department manager. For example, the following view can be created for the manager of department number 51:
```
   CREATE VIEW EMP051 AS
      SELECT NAME,SALARY,JOB FROM STAFF
      WHERE DEPT=51
   GRANT SELECT ON TABLE EMP051 TO JANE
```
The manager with the authorization name JANE would query the EMP051 view just like the STAFF table. When accessing the EMP051 view of the STAFF table, this manager views the following information:

NAME SALARY JOB

Fraye 45150.0 Mgr

Williams 37156.5 Sales

Smith 35654.5 Sales

Lundquist 26369.8 Clerk

Wheeler 22460.0 Clerk

NAME	SALARY	JOB
Fraye	45150.0	Mgr
Williams	37156.5	Sales
Smith	35654.5	Sales
Lundquist	26369.8	Clerk
Wheeler	22460.0	Clerk

All users need to be able to locate other employees. This requirement can be met by creating a view on the NAME column of the STAFF table and the LOCATION column of the ORG table, and by joining the two tables on their corresponding DEPT and DEPTNUMB columns:

   CREATE VIEW EMPLOCS AS
      SELECT NAME, LOCATION FROM STAFF, ORG
      WHERE STAFF.DEPT=ORG.DEPTNUMB
   GRANT SELECT ON TABLE EMPLOCS TO PUBLIC

Users who access the employee location view will see the following information:

NAME	LOCATION
Molinare	New York
Lu	New York
Daniels	New York
Jones	New York
Hanes	Boston
Rothman	Boston
Ngan	Boston
Kermisch	Boston
Sanders	Washington
Pernal	Washington
James	Washington
Sneider	Washington
Marenghi	Atlanta
O'Brien	Atlanta
Quigley	Atlanta
Naughton	Atlanta
Abrahams	Atlanta
Koonitz	Chicago
Plotz	Chicago
Yamaguchi	Chicago
Scoutten	Chicago
Fraye	Dallas
Williams	Dallas
Smith	Dallas
Lundquist	Dallas
Wheeler	Dallas
Lea	San Francisco
Wilson	San Francisco
Graham	San Francisco
Gonzales	San Francisco
Burke	San Francisco
Quill	Denver
Davis	Denver
Edwards	Denver
Gafney	Denver