Re: Using FGAC for implementing history

I would follow your idea, that one with a composite pk id, as_of_date and partitioning by "as_of_date". "as_of_date" could be a NUMBER format, e.g. in YYYYDD format mask. But intead of using FGAC or WM (it is too complex for such easy requirement by IMHO) I would create a view as

SELECT * FROM T
  WHERE as_of_date=My_Pkg.Get_Date

(or SELECT * FROM T
  WHERE as_of_date=(SELECT My_Pkg.Get_Date from dual)

for better performance).

I would have a package with Get_Date and Set_Date to get/set a package variable,
drfault will be 300012.

I would revoke all the grants from users on the table T and put it on the view.

I you want to get data from 2002-12 then just

EXEC My_Pkg.Set_Date(200212);
SELECT * FROM V; Jan

Vikas Agnihotri <usenet_at_vikas.mailshell.com> wrote in message news:<2gfhphF286akU1_at_uni-berlin.de>...
> 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
Received on Thu May 13 2004 - 06:55:27 CDT