Virtual Private Database (VPD)
Oracle's latest attempt into Oracle security management is a product with several names. Oracle has two official names for this product, virtual private databases, or VPD, which as also known as fine-grained access control.
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. 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.
Consider an HR clerk who is only allowed to see employee records in the Retail Division. When the user initiates the query
SQL> SELECT * FROM emp;
the function implementing the security policy returns the predicate division = 'RETAIL', and the database transparently rewrites the query. The query actually executed becomes:
SQL> SELECT * FROM emp WHERE division = 'RETAIL';
About Oracle Virtual Private Database Policies
After we create a function that defines the actions of the Oracle Virtual Private Database WHERE clause, we must associate this function with the database table to which the VPD action applies.
Column-Level VPD Policy
SQL> CONN sys/password@db10g AS SYSDBA
SQL> GRANT EXECUTE ON dbms_rls TO scott;
SQL> CONN scott/tiger@db10g
--Create the policy function to restrict access to SAL and COMM columns if the employee is not part of the department 20.
SQL> CREATE OR REPLACE FUNCTION pf_job (owner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
con := 'deptno = 20';
RETURN (con);
END pf_job;
/
-- Apply the policy function to the table.
SQL> Execute
DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'sp_job',
function_schema => 'scott',
policy_function => 'pf_job',
sec_relevant_cols => 'sal,comm');
-- We see all records if SAL and COMM are not referenced
SQL> SELECT empno, ename, job FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
.
.
.
.
.
.
.
.
7934 MILLER CLERK
-- Rows are restricted if SAL or COMM are referenced.
SQL> SELECT empno, ename, job, sal, comm FROM emp;
EMPNO ENAME JOB SAL COMM
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 10000
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
-- Remove the policy function from the table.
SQL> BEGIN
DBMS_RLS.DROP_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'sp_job');
END;
/
Column-level VPD with Column Masking Behavior
Column masking behaviour is implemented by using the "sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS" parameter. This allows you to display all rows but mask the values of the specified columns for the restricted rows:
-- Using the same policy function as before.
SQL>EXECUTE
DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'sp_job',
function_schema => 'scott',
policy_function => 'pf_job',
sec_relevant_cols => 'sal,comm',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
-- All rows are returned but the SAL and COMM values are only shown for employees in department 20.
SQL> SELECT empno, ename, job, sal, comm FROM emp;
EMPNO ENAME JOB SAL COMM
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 10000
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK 1100
7900 JAMES CLERK
7902 FORD ANALYST 3000
7934 MILLER CLERK
No comments:
Post a Comment