Implementing Data-level Security

From: Anup Jalan <anupcomp_at_giasbm01.vsnl.net.in>
Date: 1997/02/02
Message-ID: <1.5.4.32.19970202183517.00665ea0@giasbm01.vsnl.net.in>#1/1

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

Received on Sun Feb 02 1997 - 00:00:00 CST