The fine grained access control code is running in a different "space" then your code. It does not have access to your package states and such.

You need to set the username in a context before using the FGAC. The application context is the way to get the state to the FGAC routines. Consider this small example:



ppr_test@8i> create or replace package demo_pkg
2 as
3 procedure set_remote_user;
4
5 function security_function( p_schema in varchar2,
6 p_object in varchar2 )
7 return varchar2;
8
9 end;
10 /

Package created.

ppr_test@8i> create or replace context my_context
2 using demo_pkg
3 /

Context created.


Thats my security policy and a function to set up the application context (and the create context). Now I can implement the package body. My policy will be really simply -- I will return a predicate that is basically "where owner = REMOTE_USERNAME"

ppr_test@8i> create or replace package body demo_pkg
2 as
3
4
5 procedure set_remote_user
6 is
7 begin
8 dbms_session.set_context( 'my_context', 'remote_user',
9 upper(owa_util.get_cgi_env('remote_user')) );
10 end;
11
12
13 function security_function( p_schema in varchar2,
14 p_object in varchar2 )
15 return varchar2
16 is
17 begin
18 return 'owner = '||
19 'sys_context( ''my_context'', ' ||
20 ' ''remote_user'' )';
21 end;
22
23
24 end;
25 /

Package body created.

Notice how the procedure set_remote_user stuffs the cgi_env into the application context (and remember - the only way to set the context is via a procedure/function in the demo_pkg, we've BOUND that package to that context -- there will be no "spoofing"). The security policy on the other hand does not refer to the cgi-env but rather the application context

Continuing on:

ppr_test@8i> create table my_table
2 ( data varchar2(30),
3 OWNER varchar2(30) default USER
4 )
5 /

Table created.

ppr_test@8i>
ppr_test@8i> begin
2 dbms_rls.add_policy
3 ( object_schema => user,
4 object_name => 'MY_TABLE',
5 policy_name => 'MY_POLICY',
6 function_schema => user,
7 policy_function => 'demo_pkg.security_function',
8 statement_types => 'select',
9 update_check => FALSE );
10 end;
11 /

PL/SQL procedure successfully completed.

Created a table and attached the policy to it. Now lets put some data in there (my username on the web is WEB$TKYTE)..

ppr_test@8i> insert into my_table values ( 'abc', 'WEB$TKYTE' );
ppr_test@8i> insert into my_table values ( 'abc', 'WEB$TKYTE' );
ppr_test@8i> insert into my_table values ( 'abc', 'WEB$TKYTE' );
ppr_test@8i> insert into my_table values ( 'abc', 'NOT ME' );
ppr_test@8i> insert into my_table values ( 'abc', 'NOT ME' );
ppr_test@8i> insert into my_table values ( 'abc', 'NOT ME' );

and now I create a small package for the web:



ppr_test@8i> create or replace package web_demo_pkg
2 as
3 procedure show_some_data;
4 end;
5 /

Package created.

ppr_test@8i>
ppr_test@8i>
ppr_test@8i> create or replace package body web_demo_pkg
2 as
3
4 procedure show_some_data
5 is
6 begin
7 htp.header( 1, 'testing' );
8 for x in ( select * from my_table )
9 loop
10 htp.bold( x.data || ',' || x.owner || '<br>' );
11 end loop;
12 end;
13
14
15 BEGIN
16 demo_pkg.set_remote_user;
17 end;
18 /

Package body created.

ppr_test@8i> grant execute on web_demo_pkg to public
2 /


Notice how I used the elaboration code in the package body to automagically set the application context. Every package body may have such code -- its a snippet of code that will be executed as the package is loaded for the first time -- its initialization code. It does all of the work of setting the username for us. Now when I run this, I get:




testing

abc,WEB$TKYTE
abc,WEB$TKYTE
abc,WEB$TKYTE

as expected....