Data Warehouse Dynamic Views

Could anyone help me with the following.

I'm fairly new to Oracle (Ver 8.05) & my first project as a DBA is to setup a data warehouse, where users from various depts can query the data using a nice front-end tool. My problem is controlling data row security.

The main table consists of global financial transactions & the security aim is to control rows retrieved so that each user will only see their respective costcentre data.

The problem is compounded in that some users will need to view multiple costcentres & other specific multiple fields.

The front-end browser application has security options, but I am aware that access using say SQL+ would ignore those settings.

I dont want the admin overhead of setting up numerous views on the same table for different Users.

I saw from other threads one possible solution is to use a single dynamic view for each sensitive table. These are created at run time & are built by the sql view referring to a table containing a matrix of what "fields", "options" (=,like etc), & "TEXT" a user would have access to view.

The basic step one model would look as follows;

CREATE VIEW USER_CUSTOMERS AS
SELECT * FROM CUSTOMERS
WHERE CUSTOMERS.SALES_REP = USER; This could be taken further with step two;

create view transactions_view as
select * from transactions, security
where transactions.costcentre = security.costcentre and security.UserID = user;

This would restrict the user to seeing only rows with same costcentre as setup in security.costcentre.

But is it possible to take this idea further ?

My goal is to have a single security user matrix table where all view sql syntax is stored.

Security eg:
user tableref viewref field option data

Bill         trans        vtrans      cost         =         admin
Frank    trans        vtrans      acct        like      %cash%
Sue       trans        vtrans      salary      <          20000
Sue       trans        vtrans      cost         =          admin

I've managed some limited control with above step two method but I'm not sure if its possible to take this concept any further.

I've done some tests & seem to be heading into PLSQL & cursor waters (heavy seas!).

Can anyone help with some pointers, thoughts or utility suggestions ?

Thanks,

                Jamie Grant.
           JamieG_at_22nra.freeserve.co.uk