Databases Reference
In-Depth Information
For example, in an HR application, users from the HR department may have full access
to the employee base table, which contains basic information such as employee names,
work addresses, and work phone numbers, as well as more restricted information such
as Social Security numbers, home addresses, and home telephone numbers. For other
users in the company, you'll want to hide more personal information by providing a
view that shows only the basic information.
Creating a virtual private database or leveraging the Label Security Option, described
in subsequent sections of this chapter, provide a more secure means of restricting access
to certain data.
Fine-grained access control
Implementing security is a critical but time-consuming process, especially if you want
to base security on an attribute with a wide range of values. A good example of this type
of situation in the HR scenario previously described would be the need to limit the data
an HR representative can see to only the rows relating to employees that he supports.
Here you're faced with a situation in which you might have to define a view for every
HR representative, which might mean many, many different views, views that would
have to change every time an HR representative left or joined the company. And if you
want to grant write access for a representative's own employees and read access for other
employees, the situation gets even more complex. The smaller the scope, or
grain
, of
the access control you desire, the more work is involved in creating and maintaining
the security privileges.
Oracle offers a type of security that you can use to grant this type of
fine-grained access
control
(FGAC).
Security policies
implemented as PL/SQL functions can be associated
with tables or views enabling creation of a virtual private database (VPD). A security
policy returns a condition that's dynamically associated with a particular SQL statement,
which transparently limits the data that's returned. In the HR example, suppose that
each representative supports employees with a last name in a particular alphabetic range,
such as A through G.
The security policy would return part of a WHERE clause, based on a particular rep‐
resentative's responsibilities, that limits the rows returned. You can keep the range for
each representative in a separate table that is dynamically queried as part of the security
policy function. This simplifies management of allowable access if roles and responsi‐
bilities change frequently.
You can associate a security policy with a particular view or table by using the built-in
PL/SQL package DBMS_RLS, which also allows you to refresh, enable, or disable a
security policy.
Oracle Database 10
g
and newer database releases feature a VPD that is even more fine-
grained, enabling enforced rewrites when a query references a specific column. Perfor‐
mance of queries in VPD implementations was also improved since Oracle Database
