Implementing Data-level Security

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

In my original post, I did not quite state my requirements clearly. I need to control SELECT, INSERT, UPDATE and DELETE based on the user.

I received a number of replies suggesting row-level trigger control or views based on joins. However, triggers cannot control SELECTS, whereas views with joins cannot be used to INSERT/UPDATE/DELETE (I am still on 7.1).

A combination of the above methods will result in INSERT/UPDATE/DELETE from the table, but SELECT from the view. This will complicate life for the users as well as programmers, and keeping security checks will be even more difficult.

So, is there a way out other than views with nested loops?

The original message ------

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 Mon Feb 10 1997 - 00:00:00 CST