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
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 ?
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.
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
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.
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 .
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. )
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.
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.
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.
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.
June 18, 2013 - 2:38 pm UTC
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
May 01, 2018 - 1:10 am UTC
Can we see the policy definition please (ie, the plsql block you used to define it)