Using Views to Restrict Data Access
Go Up to Planning Security
In addition to using
REVOKE to control access to database tables, you can use views to restrict data access. A view is usually created as a subset of columns and rows from one or more underlying tables. Because it is only a subset of its underlying tables, a view already provides a measure of access security.
For example, suppose an
EMPLOYEES table contains the columns,
PHONE. This table contains much information that is useful to all employees. It also contains employee information that should remain confidential to almost everyone:
SALARY. Rather than allow all employees access to the
EMPLOYEES table, a view can be created which allows access to other columns in the
EMPLOYEES table, but which excludes
CREATE VIEW EMPDATA AS SELECT LAST_NAME, FIRST_NAME, DEPARTMENT, JOB, PHONE FROM EMPLOYEES;
Access to the
EMPLOYEES table can now be restricted, while
SELECT access to the view,
EMPDATA, can be granted to everyone.
- Note: Be careful when creating a view from base tables that contain sensitive information. Depending on the data included in a view, it may be possible for users to recreate or infer the missing data.