Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mark.

Asked: November 21, 2002 - 3:26 pm UTC

Last updated: May 01, 2018 - 1:10 am UTC

Version: 9i rel 2

Viewed 1000+ times

You Asked

Tom,

I have a simple implementation of VPD which inserts a record into a log table when a select is performed on the table. I am seeing the log record inserted twice.

As you can see from my example below my sql predicate does not limit any data in the table, it just logs and returns a null predicate.

An interesting thing happens if I make my predicate function return a clause which will make the statement fail, such as return 'invalidcolumnname = 200'; Only 1 log record gets inserted.

Why?

drop table footab;
create table footab
(test varchar2(30),
flag char)
tablespace users
storage(initial 4k next 4k pctincrease 0)
/

insert into footab values('test1', null);
insert into footab values('test2', 'y');
commit;

drop table logtab;
create table logtab
(createdate date)
tablespace users
storage (initial 4k next 2k pctincrease 0)
/

create or replace package tstctx as

procedure setid;

end tstctx;
/

show errors

drop context tstcontext;
create context tstcontext using tstctx;

create or replace package body tstctx as

procedure setid is

begin
dbms_session.set_context('tstcontext', 'MYID', uid);
end;

end tstctx;
/

show errors

create or replace package tstsp as

function sel(p_schema in varchar2,
p_object in varchar2)
return varchar2;

end tstsp;
/

show errors

create or replace package body tstsp as

function sel(p_schema in varchar2,
p_object in varchar2)
return varchar2 as
begin
insert
into logtab
values (sysdate);
commit;

return null;
end;

end tstsp;
/

show errors

begin
begin
dbms_rls.drop_policy('msw', 'footab', 'tstpolicy');

exception
when others then null;
end;
dbms_rls.add_policy('msw',
'footab',
'tstpolicy',
'msw',
'tstsp.sel',
'select',
true,
true,
false);

end;
/

show errors

rem set context
exec tstctx.setid

rem select from the table which has a policy applied
select * from footab
/

*** 2 records are returned

rem check the log table
column dt format a20
select to_char(createdate, 'MM/DD/YYYY HH24:MI:SS') dt
from logtab
/

****OUTPUT****

11/20/2002 23:21:20
11/20/2002 23:21:20

and Tom said...

but -- you cannot rely on a VPD function being called AT ALL during a select (the cursor can and will be CACHED -- subsequent executes of an already parsed statement will simply reuse the cursor and NOT call your function)

The capability you are looking for is FGA -- fine grained auditing. See
</code> http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_fga.htm#998101 <code>

So, use fine grained auditing -- it is the appropriate tool for this job (and you'll get to capture the QUERY as well - very nice)





Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not sure FG Auditing will do what we want

Mark Wooldridge, November 21, 2002 - 7:58 pm UTC

Tom,

Thanks for the quick reply. FG Auditing will not execute for each row returned. We are using a tip from your book to have functions defined with views utilizing the functions with the view to trap what data was returned from a select.

We found a work around and there seems to be a bug with VPD. When VPD gets a select statement it must execute the predicate function and then dynamically modify the query. This modified query contains the table that has the predicate function applied to it but VPD must know not to reapply the predicate or an endless loop would occur. It seems to know not to apply the predicate but it does execute the predicate function twice. We solved the problem by putting a boolean value in the body of the package and if false do the insert and set it to true else set it back to false. we have tested it in sql*plus and seems to work great. We can capture the action from the predicate and the data that was selected.

We are planning on using open cursor for loops which you talked about in your book to make sure we do not have the cached cursor problem.

Thanks

Tom Kyte
November 22, 2002 - 7:14 am UTC

Neither does VPD -- it executes only during PARSE time -- not for each row. It allows you to control the rows returned to the end user.

What you describe is what I call the chicken and egg -- it in fact does NOT know to execute the predicate policy again and can easily get into an infinite loop. See
</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

and search for chicken. I describe it there (and how to avoid it)


As for that last paragraph -- you might as well do the auditing RIGHT IN THE STORED procedure then. You see -- VPD works as it works, you will have to change your code in order to get it to behave the way you want. Meaning -- it is not a good general purpose solution for you at all. All it will take is one application that behaves differently to subvert your security, thats a really bad thing. Also -- the open cursor for '.....' is a performance inhibitor.

I would rethink your approach some more before you go down this path.


Diff.

Nikunj, November 21, 2002 - 11:31 pm UTC

What is diff. between VPD & FGAC ?

Tom Kyte
November 22, 2002 - 7:19 am UTC

Marketing came up with one name -- VPD (sounds really cool)
Development came up with another -- FGAC (sounds really technical)

We are close...

Mark Wooldridge, November 22, 2002 - 10:08 am UTC

Tom,

I really appreciate your insight on this issue. We wanted to utilized a single point of control to apply the security policy and log this action. We could use the FG auditing but that would require setting up another policy on the tables and keeping that sin sync with our security mechanism. By putting the auditing in the predicate functions we can let the security policy drive the auditing.

I agree with VPD not firing for each row, that is why we are using an excellant tip from your book using functions and views.

I am not sure I understand or agree with '...All it will take is one
application that behaves differently to subvert your security.'. The whole point to VPD is that the application is not in control of security anymore and cannot bypass security, unless it does not take into consideration how VPD works (and you said '...VPD works as it works...').

The key is that we need VPD, however it works. We must understand the tool and work with it to accomplish our goals. If cursor caching is an issue, we ensure we code around this or utilize the new 8.1.7 and up feature and include a context change whenever we make changes to underlying tables which support our predicate generation.

There is STILL and interesting fact that the predicate function fires twice for each execution of the statement! (IMHO - there is a BUG in VPD :) ).

P.S. Anyhow, without this excellant site and your incredible book, understanding ORACLE and it's behavior would have been extremely difficult. From your book about 5.1.5c, my first project was on an xt8088 with 5.1, 5.1a, forms 2.3, BI files, sql*report and a bunch of floppies. I share your passion for Oracle, wish I had the same depth and breadth of knowledge regarding Oracle.

ALL - BUY TOM's BOOK and read it!

P.S. Tell Wendy hi for me, saw her a few weeks ago.

Tom Kyte
November 22, 2002 - 10:27 am UTC

You yourself said "we will use the open for trick to make it parse every time"

Now what? All it takes is ONE application to come in and not parse every time (that would be the good performing application by the way) and your security goals have been subverted in a trivial fashion.

Fine Grained auditing kicks in each time.

They never said they would call you once, twice or N times and you'll find it varies from release to release. There shouldn't be SIDE effects from your policiy being called more then once.

I am concerned that you are building a solution that relies on undocumented behaviours -- it is a slippery slope. I still think that FGA is what you want.

Interesting....

Mark Wooldridge, November 22, 2002 - 11:09 am UTC

Tom,

I Give :). Once again your insights prove to be the best.

I never took into account that VPD is not supposed to execute the predicate function just once. Your concern about undocumented features is valid. We will have to look into this. The good thing is we already are planning coding PL/SQL to log, we just need to move the calls from VPD to FGA. It would have been nice to marry VPD policy with FGA policy and have a central point to drive security and logging. Although your comment about side effects of policy affecting logging is interesting.

The FG Audit stuff does what we want also. We were just trying to marry the security and auditing in one place and thought we had a really cool idea utilizing an existing technology.

If you really want logging to behave, it should not be tied to anything, it should be completely unbiased to anything other piece of the system.

Maybe this is why FG Auditing was built?

Thanks, we should be on the right track now with FGA.

What about???

Mark Wooldridge, November 22, 2002 - 5:14 pm UTC

Tom,

inserts, updates and deletes. Does FGA support logging these actions? We need FGA for all statements, (sel, ins, upd, del)!

Mark

Tom Kyte
November 22, 2002 - 7:23 pm UTC

You'll use regular old fashioned auditing for ins/upd/del (or triggers)

Ok here we go yet another issue with FGAC

bharath, June 02, 2003 - 6:20 pm UTC

>>They never said they would call you once, twice or N >>times and you'll find it varies from release to release. >>There shouldn't be SIDE effects from your
>>policiy being called more then once.

we are calling a procedure inside function policy to find out current role for the logged user.Based on your reply it looks like this procedure is going to execute multiple times .hmmmmmm!

Please clarify this.



Tom Kyte
June 02, 2003 - 8:11 pm UTC

inside PLSQL, the roles will be disabled.

so you must mean "the default roles assigned to the user"

which is something you should read ONCE, store in package global variables and save for subsequent calls.

Ok here we go yet another issue with FGAC

Bharath, June 03, 2003 - 1:09 pm UTC

>>inside PLSQL, the roles will be disabled.
>>so you must mean "the default roles assigned to the user"
>>which is something you should read ONCE, store in package >>global variables and save for subsequent calls.

Roles is not oracle role ,role is something which has been set in application(e.g Sales Analyst,Accountant) and can be changed by common security module outside the application.The procedure which i am using inside the function policy will determine this.SO if the function policy executes multiple times this procedure will be called multiple times .

Tom Kyte
June 03, 2003 - 1:24 pm UTC

so the role should be cached in an application is what you mean.

(don't know what you mean by your subject there??)

put the role into the context, that is what they are there for.

Reason for the subject

Bharath, June 03, 2003 - 1:43 pm UTC

>>Ok here we go yet another issue with FGAC
Reason:
Issues with FGAC:
1,Cursor cache
2,Function which is used in policy executing multiple times. i am refering to this one
>>(They never said they would call you once, twice or N >>times and you'll find it
>>varies from release to release. There shouldn't be SIDE >>effects from your policiy being called more then >>once. )



Tom Kyte
June 03, 2003 - 1:57 pm UTC

what is the issue with the cursor cache? It is not any different then if you did the predicate rewrite yourself outside the database (well it is, it is far superior then doing it outside the database).

it is as efficient with the cursor cache as can be possible.

Reason

Bharath, June 03, 2003 - 2:34 pm UTC

>>They never said they would call you once, twice or N >>times and you'll find it varies from release to release. >>There shouldn't be SIDE effects from your
>>policiy being called more then once.

What do you meant by that.
Reason i am asking is ,if i refer procedure inside the policy i have to make sure that procedure get executed only once ,when oracle executes a sql and table refered has got a policy.


for(e.g)

Select count(*) from loan_source;

loan_source has got policy something
if user is accountant then he can see all the data

if user is salesaccountant then he can see only see cmb_loan = 'Y'

so we have created a procedure which will give me back the identification of the user.we are calling that procedure inside function which is refered in the policy definition.

so if i execute this
Select count(*) from loan_source;

policy function gets excuted multiple times and procedure gets executed multiple times ,is that true.if it's true how we can avoid that.



Tom Kyte
June 03, 2003 - 2:40 pm UTC

it is outside of your control. That is why a predicate policy function should just do minimal work (no lookups, no queries -- just return the predicate). Use the application context to cache that data which is expensive to retrieve.

SYS_CONTEXT is slower than package variables

Marc Blum, June 04, 2003 - 4:43 am UTC

Tom Kyte wrote: Use the application context to cache that data which is expensive to retrieve.

We designed our VPD with regard of minimized additional SQL. Only the logon trigger does some lookups. The predicate functions avoids any SQL. But in some heavy data processing and computing procedures we found out, that using SYS_CONTEXT to retrieve the session static values, we got a real slowdown. By switching to package global variables to cache that values we gained 30% performance.

In other applications we made the same experience, that SYS_CONTEXT is significantly slower than packaged variables.



Tom Kyte
June 04, 2003 - 8:11 am UTC

use the right tools in the right places -- sys_context involves a call into the kernel. globals don't. in your vpd functions, use the context, in normal plsql -- use globals.

Clarification

Marc Blum, June 04, 2003 - 10:25 am UTC

sorry for not being specific enough.

We have a Package, with does awful much work. The data is retrieved from tables which are completely FGACed. Inside the security functions we evaluated SYS_CONTEXTed values. By switching to packaged variables, we gained 30% performance.

What is the data dictionary view for VPD

A reader, June 19, 2003 - 12:36 am UTC

Tom,

What is the data dictionary view to identify what policies exist for an object? V$VPD gives only the current one in libraru cache.

Tom Kyte
June 19, 2003 - 7:59 am UTC

*_POLICIES

* = all, dba, user

policy function executing multiple times

afatkulin, January 25, 2006 - 9:10 pm UTC

"Function which is used in policy   executing multiple times."

Dynamic policy? There is there you'll be parsing each time. Consider:

SQL> create or replace function fgac_test(
  2   p_owner  in varchar2,
  3   p_object in varchar2
  4  ) return varchar2 is
  5  begin
  6   return null;
  7  end;
  8  /

Function created.

SQL> create table test ( n number );

Table created.

SQL> create table test2 ( n number );

Table created.

SQL> begin
  2   dbms_rls.add_policy(
  3    object_name => 'test',
  4    policy_name => 'test_policy',
  5    policy_function => 'fgac_test',
  6    statement_types => 'select',
  7    static_policy => false
  8   );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> begin
  2   dbms_rls.add_policy(
  3    object_name => 'test2',
  4    policy_name => 'test2_policy',
  5    policy_function => 'fgac_test',
  6    statement_types => 'select',
  7    static_policy => true
  8   );
  9  end;
 10  /

So we have dynamic and static policy, let's see what happens:

SQL> declare
  2   l_cnt number;
  3  begin
  4   runstats.rs_run1;
  5   for i in 1 .. 10000
  6   loop
  7    select count(*) into l_cnt from test;
  8   end loop;
  9   runstats.rs_run2;
 10   for i in 1 .. 10000
 11   loop
 12    select count(*) into l_cnt from test2;
 13   end loop;
 14   runstats.rs_stop(10000);
 15  end;
 16  /
Run1 run in 491 hsecs
Run2 run in 145 hsecs
Run1 run in 338.62% of run 2
*
STATISTIC                                 RUN 1       RUN 2        DIFF
----------------------------------- ----------- ----------- -----------
STAT...session uga memory max           261,964      65,560    -196,404
LATCH..library cache pin                100,083      20,073     -80,010
STAT...execute count                     20,006      10,005     -10,001
LATCH..row cache objects                 90,242         233     -90,009
LATCH..PL/SQL warning settings           10,001           0     -10,001
STAT...session uga memory                65,560       7,584     -57,976
STAT...session pga memory               114,044      65,536     -48,508
STAT...opened cursors cumulative         10,007           6     -10,001
STAT...recursive calls                   20,033      10,032     -10,001
STAT...parse count (total)               10,007           6     -10,001
STAT...session pga memory max           262,144     114,044    -148,100
LATCH..library cache                    110,196      20,176     -90,020
*
LATCH totals                              RUN 1       RUN 2         PCT
----------------------------------- ----------- ----------- -----------
Latch totals versus runs                371,433     101,133     367.27%

PL/SQL procedure successfully completed.

Look at:

STAT...parse count (total)               10,007           6   

intrestingly enough, tkprof doesn't revealed any additional parsing:

SELECT COUNT(*) 
FROM
 TEST


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      1.95       2.20          0          0          0           0
Fetch    10000      0.67       0.58          0      30000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      2.62       2.79          0      30000          0       10000

SELECT COUNT(*) 
FROM
 TEST2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.43       0.35          0          0          0           0
Fetch    10000      0.37       0.37          0      30000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.81       0.72          0      30000          0       10000

but there is clearly difference in elapsed times and CPU usage.


So beware dynamic policies if you really don't need them. 

See the predicates added by a VPD

Maya, June 10, 2013 - 12:47 pm UTC

What is the quick way to see the predicates applied by a VPD to a given query ? The trace file does not show the VPD predicates so want to know the correct method of seeing these conditions.

VPD not returning proper results

Rahul, April 27, 2018 - 1:30 pm UTC

Hi

We had an issue in production where the predicate returns from the policy function is wrong which is causing the query to not select any data.

Here is some background. We are using Oracle 12.1.0.2.0 version and the application has its VPD context procedure to set the staff for the session. The oracle procedure is called from different java DB session after setting the staff IDs for each session.Application gets the DB connection from the JDBC connection pool. The same procedure is invoked for two separate staffs but in some events (for some threads) the data is not getting fetched correctly.

procedure details :

create or replace PROCEDURE INIT(IP_PROCESS_ID IN NUMBER,IP_THREAD_ID IN NUMBER,IP_BATCH_SIZE IN NUMBER,
IP_STAFF_ID IN VARCHAR2 DEFAULT NULL,
IP_OFF_CODE IN VARCHAR2 DEFAULT NULL,
IP_VISIBILITY IN VARCHAR2 DEFAULT NULL) AS

CURSOR TEMP_CUR IS

SELECT CSEC_CUST_NO,
CSEC_ACT_ID,
CSEC_ACT_CUR,
CSEC_DB_ACT,
CSEC_CR_ACT,
CSEC_ACT_NO,
CSEC_PF_CENTRE,
CSEC_REC_ID,
CSEC_PRODUCT_ID,
CSEC_FROM_DATE,
CSEC_TO_DATE,
CSEC_POSTING_DATE,
CSEC_CAP_ID,
CSEC_PARENT_CAP_ID,
CSEC_TYPE,
CSEC_PM_REF7,
NULL,
CSEC_PRINCIPAL_ACC,
CSEC_USER2,
CSEC_USER4

FROM CSTM_STMT_EXTRACT_CONTEXT
WHERE CSEC_THREAD_ID = IP_THREAD_ID
AND
CSEC_PID = IP_PROCESS_ID
ORDER BY CSEC_REC_ID,
CSEC_POSTING_DATE;

TYPE TEMP_ARR IS TABLE OF CSTM_STMT_EXTRACT_TEMP%ROWTYPE INDEX BY BINARY_INTEGER;

TEMP_TAB TEMP_ARR;

M_STAFF_ID VARCHAR2(100);
M_OFFICE VARCHAR2(100);

BEGIN
G_THREAD_ID := IP_THREAD_ID;

M_STAFF_ID := GET_STAFF;
M_OFFICE := GET_OFFICE_CODE;


G_TBMS_DATA_LABEL := SECURITY.GET_CUR_TAG;

G_ROWS := IP_BATCH_SIZE;
G_PROCESS_ID:= IP_PROCESS_ID;


OPEN TEMP_CUR;
LOOP

TEMP_TAB.DELETE;

FETCH TEMP_CUR BULK COLLECT INTO TEMP_TAB LIMIT G_ROWS;
EXIT WHEN TEMP_TAB.COUNT = 0;

DEBUG_LOG('init temp_tab.count -> '||TEMP_TAB.COUNT);

FORALL I IN 1..TEMP_TAB.COUNT
INSERT INTO CSTM_STMT_EXTRACT_TEMP(CSET_CUST_NO,CSET_ACT_ID,CSET_ACT_CUR,CSET_DB_ACT,CSET_CR_ACT,CSET_ACT_NO,CSET_PF_CENTRE,CSET_REC_ID,CSET_PRODUCT_ID,CSET_FROM_DATE,CSET_TO_DATE,CSET_POSTING_DATE,CSET_CAP_ID,CSET_PARENT_CAP_ID,CSET_TYPE,CSET_PM_REF7,CSET_PDT_TYPE,CSET_PRINCIPAL_ACC,CSET_USER2,CSET_USER4) VALUES (TEMP_TAB(I).CSET_CUST_NO,TEMP_TAB(I).CSET_ACT_ID,TEMP_TAB(I).CSET_ACT_CUR,TEMP_TAB(I).CSET_DB_ACT,TEMP_TAB(I).CSET_CR_ACT,TEMP_TAB(I).CSET_ACT_NO,TEMP_TAB(I).CSET_PF_CENTRE,TEMP_TAB(I).CSET_REC_ID,TEMP_TAB(I).CSET_PRODUCT_ID,TEMP_TAB(I).CSET_FROM_DATE,TEMP_TAB(I).CSET_TO_DATE,TEMP_TAB(I).CSET_POSTING_DATE,TEMP_TAB(I).CSET_CAP_ID,TEMP_TAB(I).CSET_PARENT_CAP_ID,TEMP_TAB(I).CSET_TYPE,TEMP_TAB(I).CSET_PM_REF7,NULL,TEMP_TAB(I).CSET_PRINCIPAL_ACC,TEMP_TAB(I).CSET_USER2,TEMP_TAB(I).CSET_USER4);

COMMIT;

END LOOP;

IF TEMP_CUR%ISOPEN THEN CLOSE TEMP_CUR;END IF;

EXCEPTION
WHEN OTHERS THEN
IF TEMP_CUR%ISOPEN THEN CLOSE TEMP_CUR;END IF;
STBMS_ERR.DISP_ERR( 10000002, SQLERRM );

END INIT;

*****************************************

The issue is not frequent and it happens only in production. we are not able to reproduce it at our test environments. The table CSTM_STMT_EXTRACT_CONTEXT is set with SHARED_CONTEXT_SENSITVE policy and changing it to DYNAMIC resolves our issue but adds performance degradation.

It would be really great if you can provide your input on what is done wrong here. The



Connor McDonald
May 01, 2018 - 1:10 am UTC

Can we see the policy definition please (ie, the plsql block you used to define it)

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.