Re: security question

In article <1995Feb17.102642.4006_at_inet.d48.lilly.com>

           swisshelm_at_lilly.com "Bob Swisshelm" writes:

:
: Here is the view on customer that limits what the users can see. Since
: the view is only on customer, they can do updates through it.
:
: create view secure_customer
: as select * from customer
: where ...

A late addition on this thread:

If you extend the use of views to reports as well as input forms, then be careful about joining the views. In the first place, where all the join as 'proper' joins, you will probably find that

    join secure_view_a to secure_view_b
is resolved to

    joint table_a to table_b then check security_a then security_b. If each individual is allowed to see only a small fraction (say 10% of each table) then the overhead of the join will be (relatively) large compared to the overhead of a single table.

In the second case, if you have any OUTER joins on views, then you may find that the performance is catastrophic as the outer joined view has to come last in the order. The effect of the security subqeury may result in this being resolved by Oracle doing an internal view generation (temporary table) by running a full tablescan on the table, and checking the view, then joining the result to the final state of all the previous joins.

Generic solution to problem 1:

   Use a view which is a join to the security table, but    do updates by rowid to the explicitly named base table.    This can ameliorate problem 2.

Specific solution to most cases of problem 2:

    change

	     select 
	       from viewb, viewa
	     where
	      viewb.col2(+) = viewa.col
    into something like:
	   select
           from viewb, viewa
	   where
	      viewb.col2 = viewa.col
           union 
           select
           from viewa
           where not exists (select from viewb)

-- 
Jonathan Lewis