Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Implementing Data-level Security

Re: Implementing Data-level Security

From: Jared Hecker <jared_at_hwai.com>
Date: 1997/02/06
Message-ID: <32f9df14.2035427@news.planet.net>#1/1

There is no good reason a view must correspond to a table; this is a security decsision.

My suggestion would be to make denormalized view showing all the information you want - that way, if the app hits against the view yo uwill get everything you need in one query; app security should be able to take care of only letting the salesman see what you want to see - e.g., have the salesman put in his ID as part of sign-in, rather than as part of the query, then just give him a list of queries (which would be automatically parameterized for the ID he entered at signon).

hth -

Regards,
jh

Anup Jalan <anupcomp_at_giasbm01.vsnl.net.in> wrote:

>How do you normally implement data-level security?
>
>For example, if I have 5 salesmen who service 250 customers, I want each
>salesman to see information for only the customers he services, and orders
>booked only by him. I have the following tables:
>
>Table EMP: emp_id, emp_name
>Table CUST: cust_id, cust_name, emp_id
>Table ORD: ord_id, cust_id
>
>I can create the following views:
>
>View CUST_VW: select * from CUST where emp_id = (select emp_id from EMP
>where emp_name = user)
>
>View ORD_VW: select * from ORD where cust_id in (select cust_id from CUST_VW)
>
>By giving appropriate grants on the views, I can now achieve the desired
>security.
>
>However, each view requires a nested select, and the number of nested
>selects increase with each level of heirarchy. Thus, CUST_VW has 1 level of
>nesting, ORD_VW has 2, LINE_ITEM_VW would have 3, and so on. This will mean
>a big performance hit. The problem is further complicated if the security is
>based on more than one table.
>
>This should be a standard problem for most systems. Is there a more elegant
>(and effecient) way of handling things? How do you achieve this in your
>organisation?
>
>Anup.
>----------------------------------------------------------------------
>Anup Jalan
>
>Anup Computers Tel : 91-22-364 7992
>33, Shankar Sagar Fax : 91-22-363 6176
>Sophia College Road
>Bombay 400 026 email : anupcomp_at_giasbm01.vsnl.net.in
>INDIA
>----------------------------------------------------------------------
>
>

Jared Hecker, CODBA       | Oracle  and Sybase Architect and DBA
jared_at_planet.net          | - consulting in the 
76276.740_at_compuserve.com  |   NYC/NJ region
Received on Thu Feb 06 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US