Re: Using FGAC for implementing history

Vikas Agnihotri wrote:

> The intended use of FGAC seems to be for securing data and providing
> different view of the data (at a row-level) depending on who is looking
> at it, etc.
>
> Would it be a appropriate use of FGAC to use it for history-enabling
> tables?
>
> For example, I have a CUST table with primary key of cust_no. There is a
> requirement to capture month-end history snapshots of the table. I was
> thinking of adding as_of_date to the primary key of the table. The
> "current" data would have a as_of_date of, say, 12/12/3000 or something
> (cant be null because it is part of the PK). Of course, depending on
> data volume, I would create this as a partitioned table with as_of_date
> as a partition key.
>
> Then, I would attach a FGAC policy to the table with a default predicate
> of "as_of_date=12/12/3000", so current users of the table wouldnt be
> affected. Then I would provide a function/procedure to change the
> as_of_date in the app. context. For example
>
> select * from cust; -- would return current data
>
> exec set_ctx('4/30/2004') -- would add a "as_of_date=4/30/2004" predicate
>
> select * from cust; -- would return 4/30/2004 data
>
> Is this a good use of FGAC?
>
> If not, why not?
>
> Tom Kyte refered me to Workspace Manager for this, but I found WM to be
> a very immature offering with many bugs and restrictions. See my thread
> on this at
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4632007035731#18350492565844
>
>
> Oracle Support refuses to discuss this question, saying that it is a
> case for Oracle Consulting Services.
>
> If anyone here has any comments, experiences, I would greatly appreciate
> it.
>
> Thanks

Are you asking if you can use DBMS_WM with DBMS_RLS? Are or you asking if you can reinvent the wheel?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)