Using FGAC for implementing history

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 Wed May 12 2004 - 16:02:27 CDT