Using Granular Access Control - Implementing Database Security and Auditing - page 188

SELECT * FROM EMP WHERE DEPTNO=20;

This is all done transparently and without my knowledge, so effectively
I truly have my own (virtually) private database.
In order for VPD to work, it needs to get the predicate from the security
policy; this is where FGAC comes in. FGAC allows you to attach a security
policy to tables, views, and synonyms. First, you need to create a PL/SQL
function that returns the predicate (as a string) that will be used to restrict
the queries:

create or replace function get_dept_id
(

p_schema_name in varchar2,
p_table_name in varchar2

)
return varchar2
is

l_deptno number;

begin

select deptno
into l_deptno
from scott.emp
where empno = sys_context('app_ctx', 'app_userid');
return 'deptno = ' || l_deptno;

end;

What this function does is the following:

1. It gets an application user ID from an application context (this

context must already be defined as described in the previous sec-
tion). In this case the application user ID is precisely the
employee ID maintained in table EMP.

2. It selects the department number of this employee/application

user. Assume in my case that this is department 20.

3. It returns the string .

deptno = 20

4. This predicate is then added to the select statement by the VPD

runtime as discussed.