Is this a valid use of Fine Grained Access Control (row level security)?

Using Oracle 91 R2 on Sun StarFire Unix server running Sun OS 5

Question: Can we use FGAC to limit what users see on a table in order to avoid having to change where clause throughout an application

We have an oracle database with 30 tables. It hold data about eletrical components and their ratings and loads.

the component table ELECT has 8000 components with a sequience number as the primary key. It holds data about components such as voltage, insulation, etc.

Ratings and loads are year based. that is they have the primary key from ELECT as a forgien key and also the year as part of the key too. thus each rating or load row is identified with both the key from the component and the year its for. when we join the ELECT table with the load table we give it a year to see just that years load.

Now it has been determined (directive from legal dept don't ask, I know all about design issues and such, we can not make major design or structure changes at this point we have to make do with what we have), that the ELECT table also needs to be year based. So we have to add a year column to the table and create compoents for each year. Users will then select the year from the ELECT table and ratings tables to see component and rating data for a specific year. Key will be the old key plus year

Problem:
Users access this app via a WEB interface, consisting of over 100 asp pages. Upon login they have to select a specific year to work with, they can see only 1 year at a time. The ELECT table is referenced over 450 times and its often joined to data from other tables that are not year based (but we need to make sure that ELECT is using only selected year.

We have 2 options:
1 - edit 100 pages and add where clause each time ELECT table is referenced passing year selected on login. (we estimate 11 days to complete)

2 - use fine grained access control. We create a policy on ELECT table that limits on year. We create an app context and procedure. when users login and select a year we change the predicate so that ELECT table now just has year selected (hiding all other years). Since we kept old key and just added year to create compond key, relationships with other tables is maintained.(we have code altready).

Is this valid? Am i asking for trouble. Is it an invalid or wierd use of FGAC? Received on Mon Jul 28 2003 - 09:13:07 CDT