Re: security question

From: Bob Swisshelm <swisshelm_at_lilly.com>
Date: 17 Feb 95 10:26:41 EST
Message-ID: <1995Feb17.102642.4006_at_inet.d48.lilly.com>

In article <3hunt0$e9q_at_newsbf02.news.aol.com> DMNortunen, dmnortunen_at_aol.com writes:
>I want to add new users to my Oracle7 database. I want to limit the
>information that they will be able to see. I would like to create a
 view
>the limits the records that they can see in the customer table. The
>criteria for determining which records they can access is based on the
>customer table and also on a distributor table that has a foreign key
>relationship with the customer table. The problem is I can't create a
>view that joins these two tables and also perform inserts, updates, and
>deletes through this view. I really don't want to duplicate the table
>either. There is already a large forms3.0 application accessing these
>tables and I don't want to have to modify a lot of code. Any ideas on
 how
>this security can be provided through roles, views, etc? Any help
 would
>be appreciated. please email at DMNortunen.aol.com. Thanks in
advance!

Do the check through a where clause, not through a join.

Suppose you can a customer table

create table customer

( custno	number,
  distr	number,

  ...
);

and a security table

create table security

( username	varchar2(30),
  distr	number

);

Populate the security table with the distributors that you will allow each user to see.

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 distr in (select distr from security 
					where username = user)

with check option;

Bob Swisshelm | swisshelm_at_Lilly.com | 317 276 5472 Eli Lilly and Company | Lilly Corporate Center | Indianapolis, IN 46285 Received on Fri Feb 17 1995 - 16:26:41 CET