Re: Data Warehouse Dynamic Views

One possible way to simplify this would be to use a static view instead of a dynamic view
and have the where clause of the static view access the privilege matrix . Then you just update the matrix
for each user.

Jamie Grant wrote:

> 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
Received on Wed Feb 10 1999 - 15:20:27 CST