Virtual
private databases
VPDs are also known by several other names,
including row-level security (RLS) and
fine-grained access control (FGAC). Regardless of
the name, VPDs provide a whole new way to control
access to Oracle data.
VPDs involve creation of a security policy. When
users access a table (or view) that has a security
policy:
- Oracle calls the policy function, which
returns a predicate. A predicate is a WHERE
clause that qualifies a particular set of rows
within the table.
- Oracle dynamically rewrites the query by
appending the predicate to users' SQL
statements.
A VPD requires a policy that is defined to control
access to tables and rows, as shown in Figure A.
Figure A |
 |
The policy function may
generate the predicates on whatever session
environment variables are available during
the function call. These variables usually
appear in the form of application contexts. |
Whenever a query is run against the target tables,
Oracle invokes the policy and produces a transient
view with a WHERE clause predicate pasted onto the
end of the query, like so:
SELECT * FROM scott.emp WHERE
P1
There are many benefits to VPDs:
- Dynamic security?No need to maintain complex
roles and grants
- Multiple security?Places more than one
policy on each object, as well as stacks them
upon other base policies
- Web Apps?A single user accesses the
database, hence row-level security can easily
differentiate between users
- No back-doors?Users no longer bypass
security policies embedded in applications
because the security policy is attached to the
data
Security
and design
Regardless of the security and auditing method
that you use, proper design is critical, and many
shops create security maps, as shown in Figure
B, to show data access hierarchies.
Figure B |
 |
Sample design for
role-based Oracle security |
Problems
with mixing security
Once you understand the areas of security and
auditing, it should be clear that you must come up
with a method to ensure that security methods
aren't mixed in an inappropriate way. By
themselves, each of these security mechanisms
provides adequate access protection, but when
these methods are mixed, it can often be difficult
(if not impossible) to identify the access for
individual users.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|