Re: Data Warehouse Dynamic Views

I too have looked at this problem some time ago.

My conclusions were as follows:

1. Creating a set of views for each security group was efficient but the admin overheads may be excessive.
2. Using a security table has it's own difficulties. If users can create the own queries then your forced to denormalise the cost centre to all tables that need protecting. Each view built for these tables will require a join to the security table. This in turn means that when the user joins views the security table is being used multiple times and there is far less chance for the optimizer to make the correct choice. A three table join can become a six table join.

The solution I came up with was to add the security code (in your case cost centre) into the user id. The view could then be coded as

create view USER_CUSTOMER
select *
from CUSTOMER
where
CUSTOMERS.SALES_REP = substr(USER,8,4).

Even better would be to use the USERENV function. You can retrieve a CLIENT_INFO field that can be set by the dbms_application_info package. By using a small procedure that sets this value using the security table you can improve on example 2. The advantage of this method is that it has a very small performance overhead and frees the format of the user name.

The above view would look something like

create view USER_CUSTOMER
select *
from CUSTOMER
where
CUSTOMERS.SALES_REP = USERENV('CLIENT_INFO'). This could be duplicated in many views without having any significant overhead.

Also look at the LABEL datatype offered with Trusted Oracle. This seems to offer the best technical solution but its difficult to try it out as you need Trusted Oracle 7. Who uses that?

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 Thu Feb 11 1999 - 03:39:21 CST