From: anupcomp@giasbm01.vsnl.net.in
Subject: Implementing Data-level Security
Date: 1997/02/02
Message-ID: <854905700.20170@dejanews.com>#1/1
x-http-user-agent: Mozilla/3.01Gold (Win95; I)
x-originating-ip-addr: 202.54.3.34 ()
organization: Deja News Usenet Posting Service
x-article-creation-date: Sun Feb 02 18:09:01 1997 GMT
x-authenticated-sender: anupcomp@giasbm01.vsnl.net.in
newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server



How do you normally implement security based on the data in the tables?

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@giasbm01.vsnl.net.in
INDIA
----------------------------------------------------------------------

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet