Defining a Dynamic Policy
In the previous sections, I talked about a policy that returns a predicate string that is constant—for example, SAL <= 1500. In real life, such a scenario is not very common, except in some specialized applications such as goods warehouses. In most cases, you will need to build a filter based on the user issuing the query. For instance, the HR application may require that users see only their own records, not all records in a table. This is a dynamic requirement, as it needs to be evaluated for each employee who logs in. The policy function can be rewritten as follows.
1 CREATE OR REPLACE FUNCTION authorized_emps ( 2 p_schema_name IN VARCHAR2, 3 p_object_name IN VARCHAR2 4 ) 5 RETURN VARCHAR2 6 IS 7 l_return_val VARCHAR2 (2000); 8 BEGIN 9 l_return_val := 'ENAME = USER'; 10 RETURN l_return_val; 11 END; 12 /
In line 9, the predicate will compare the ENAME column with the USER—that is, the name of the currently logged-in user. If the user Martin (remember that Martin is the name of an employee in the table EMP) logs in and selects from the table, he sees only one row—his own.
CONN martin/martinConnected. SQL>
SELECT * FROM hr.emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ------ --------- ------ ------ ------ 7654 MARTIN SALESMAN 7698 28-SEP-81 1,250 1,400 30
Now let’s expand this model to let Martin show more records—not just his own, but his entire department’s records. The policy function now becomes the following.