A view provides a means of controlling access or extending privileges to a 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.
By specifying a WHERE clause in the subquery of a view definition, an authorized user can limit the rows addressed through a view.
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.
The following scenario provides a more detailed example of how views can be used to restrict access to information.
GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL
CREATE VIEW EMP051 AS
SELECT NAME,SALARY,JOB FROM STAFF
WHERE DEPT=51
GRANT SELECT ON TABLE EMP051 TO JANE
NAME | SALARY | JOB |
---|---|---|
Fraye | 45150.0 | Mgr |
Williams | 37156.5 | Sales |
Smith | 35654.5 | Sales |
Lundquist | 26369.8 | Clerk |
Wheeler | 22460.0 | Clerk |
CREATE VIEW EMPLOCS AS
SELECT NAME, LOCATION FROM STAFF, ORG
WHERE STAFF.DEPT=ORG.DEPTNUMB
GRANT SELECT ON TABLE EMPLOCS TO PUBLIC
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 |