Database Reference
In-Depth Information
Public View of Employees
The most basic method for granting limited access to a data table is to create a view. A view is like a filter
that we put on the data table that can show only certain data, reorganize the data, format the data and
provide data from multiple tables or other views. We will create a view named
v_employees_public
that
only includes the non-sensitive columns of data:
CREATE OR REPLACE VIEW hr.v_employees_public
AS SELECT
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
manager_id,
department_id
FROM hr.employees;
Test the new view to assure it returns just the data we've requested. We should notice that the
SALARY
and
COMMISSION_PCT
columns are missing:
SELECT * FROM hr.v_employees_public;
Because we have omitted the sensitive columns (
SALARY
and
COMMISSION_PCT
) from this view, we can
grant access to this view to the entire company without exposing sensitive data. We could grant it to
PUBLIC
; however, we still feel a bit protective of our data, and we want to be sure access is controlled, so
we grant access to the view to our secure application role,
hrview_role
rather than to
PUBLIC
.
HR
user has
the privilege to grant access to structures in his own schema:
GRANT SELECT ON hr.v_employees_public TO hrview_role;
With this grant we are permitting
hrview_role
to
SELECT
data from the view. Other common
privileges one might grant to a view are
INSERT
,
UPDATE
and
DELETE
. We are not granting any of those
privileges at this time. The
hrview_role
can see but not modify the non-sensitive data through the
v_employees_public
view.
Sensitive View of EMPLOYEES
Now that we have our non-sensitive view configured, it should be obvious how to create a sensitive view
of all columns of the
EMPLOYEES
table. As
HR
, we create a view:
CREATE OR REPLACE VIEW hr.v_employees_sensitive
AS SELECT *
FROM hr.employees;
The asterisk (*) represents all columns, and this view selects all the data from all the columns. Test
that this view returns all the data from the
EMPLOYEES
table:
SELECT * FROM hr.v_employees_sensitive;
This view might be useful at some point in the application—for instance, when we want to let
certain financial analysts count how many people earn a certain salary in preparation for annual raises.
We might also want to use this view for a financial application that we want to grant
UPDATE
access for
