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.
SQL>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.
1 ...