|
1.
What is the Virtual Private Database Feature ?
When
a user directly or indirectly accesses a table, view, or synonym that is
protected with a VPD policy, the server dynamically modifies the user's
SQL statement. The modification is based on a WHERE
condition (known as a predicate) returned by a function which implements
the security policy.
SELECT
* FROM book WHERE RULE_1;
The RULE_1 must be defined and can be context-sensitive.
The
statement is modified dynamically, transparently to the user, using any
condition which can be expressed in, or returned by a function. VPD
policies can be applied to SELECT, INSERT, UPDATE,
INDEX, and DELETE statements.
2.
What is a Column-level VPD ?
Column-level
VPD enables you to enforce row-level security when a security-relevant
column is referenced in a query. You can apply column-level VPD to
tables and views, but not to synonyms. By specifying the
security-relevant column name with the sec_relevant_cols
parameter of the DBMS_RLS.ADD_POLICY procedure, the
security policy is applied whenever the column is referenced, explicitly
or implicitly, in a query.
3.
Implementing a Column-level VPD
CREATE
OR REPLACE
function
SCOTT.TEST_VPD
(
objowner in
varchar2,
objname in
varchar2 )
return varchar2
is
return_val varchar2(900);
begin
return_val :=
'deptno = 10';
return return_val;
end;
begin
dbms_rls.add_policy (
object_schema =>
'SCOTT',
object_name =>
'EMP',
policy_name =>
'VPD_TEST_POLICY',
function_schema =>
'SCOTT',
policy_function =>
'TEST_VPD',
statement_types =>
'select, insert,
update, delete',
sec_relevant_cols =>
'sal,comm');
end;
Test
the rule:
If
there are any errors during the SELECT, INSERT, UPDATE, DELETE statement
please check the last udump file.
4.
What is the Column-level VPD with Column Masking Behavior ?
If
a query references a security-relevant column, then the default behavior
of column-level VPD restricts the number of rows returned. With column
masking behavior, which can be enabled by using the sec_relevant_cols_opt
parameter of the DBMS_RLS.ADD_POLICY procedure, all rows
display, even those that reference security relevant columns. However,
the sensitive columns display as NULL values.
More information
about this subject (
Virtual Private Database ) you can get from
www.in-oracle.com
What Is Oracle Virtual Private Database?
Oracle Virtual Private Database (VPD) enables you to create security
policies to control database access at the row and column level.
Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause
to a SQL statement that is issued against the table, view, or synonym to
which an Oracle Virtual Private Database security policy was applied.
Oracle Virtual Private Database enforces security, to a fine level of
granularity, directly on database tables, views, or synonyms. Because
you attach security policies directly to these database objects, and the
policies are automatically applied whenever a user accesses data, there
is no way to bypass security.
When a user directly or indirectly accesses a table, view, or synonym
that is protected with an Oracle Virtual Private Database policy, Oracle
Database dynamically modifies the SQL statement of the user. This
modification creates a WHERE condition (called a predicate) returned by
a function implementing the security policy. Oracle Database modifies
the statement dynamically, transparently to the user, using any
condition that can be expressed in or returned by a function. You can
apply Oracle Virtual Private Database policies to SELECT, INSERT,
UPDATE, INDEX, and DELETE statements.
For example, suppose a user performs the following query:
SELECT * FROM OE.ORDERS;
The Oracle Virtual Private Database policy dynamically appends the
statement with a WHERE clause. For example:
SELECT * FROM OE.ORDERS
WHERE SALES_REP_ID = 159;
In this example, the user can only view orders by Sales Representative
159.
If you want to filter the user based on the session information of that
user, such as the ID of the user, you can create the WHERE clause to use
an application context. For example:
SELECT * FROM OE.ORDERS
WHERE SALES_REP_ID = SYS_CONTEXT('USERENV','SESSION_USER');
More information on this subject you can get from
here. |