To implement Oracle Virtual Private Database, you must create a function to generate the dynamic WHERE clause, and a policy to attach this function to the objects that you want to protect.
- Creating a Function to Generate the Dynamic WHERE Clause
- Creating a Policy to Attach the Function to the Objects You Want to Protect
Creating a Function to Generate the Dynamic WHERE Clause
To generate the dynamic WHERE clause (predicate), you must create a function (not a procedure) that defines the restrictions that you want to enforce. Usually, the security administrator creates this function in his or her own schema. For more complex behavior, such as including calls to other functions or adding checks to track failed logon attempts, create these functions within a package.
The function must have the following behavior:
- It must take as arguments a schema name and an object (table, view, or synonym) name as inputs. Define input parameters to hold this information, but do not specify the schema and object name themselves within the function. The policy that you create with the DBMS_RLS package provides the names of the schema, and object to which the policy will apply. You must create the parameter for the schema first, followed by the parameter for the object.
- It must provide a return value for the WHERE clause predicate that will be generated. The return value for the WHERE clause is always a VARCHAR2 data type.
- It must generate a valid WHERE clause.
- It must not select from a table within the associated policy function. Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.
Creating a Policy to Attach the Function to the Objects You Want to Protect
After you create the function, you need to create an Oracle Virtual Private Database policy that associates the function with a table, view, or synonym. You create the policy by using the DBMS_RLS package. If you are not SYS, then you must be granted EXECUTE privileges to use the DBMS_RLS package. This package contains procedures that enable you to manage the policy and set fine-grained access control. For example, to attach the policy to a table, you use the DBMS_RLS.ADD_POLICY procedure. Within this setting, you set fine-grained access control, such as setting the policy to go into effect when a user issues a SELECT or UPDATE statement on the table or view.
The combination of creating the function and then applying it to a table or view is referred to as creating the Oracle Virtual Private Database policy.
— Create the policy function to restrict access to SAL and COMM columns
— if the employee is not part of the department 20.
CREATE OR REPLACE FUNCTION pf_job (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
con := ‘deptno = 20’;
— Apply the policy function to the table.
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
SELECT empno, ename, job FROM emp;
EMPNO ENAME JOB
———- ———- ———
7369 SMITH CLERK
7934 MILLER CLERK
14 rows selected.
— Rows are restricted if SAL or COMM are referenced.
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
5 rows selected.