Very Computer

by **Anup Jala** » Tue, 11 Feb 1997 04:00:00

>> 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.

>Hi,
>I've implemented this same scheme using the USER pseudovariable about 9
>years ago, but using the direct table join in my views, and it's worked
>successfully. An advantage is being able to prevent an employee from
>accessing his views (because of security, termination, etc.) immediately
>by removing his record from the EMP table, rather than trying to revoke
>grant privileges which won't work if anybody is accessing the table.

My original post did not give my requirements clearly. I want the users to
SELECT as well as INSERT/UPDATE/DELETE from the views. Clearly, direct joins
in views are out.

Anup.
----------------------------------------------------------------------
Anup Jalan

Anup Computers Tel : 91-22-364 7992
33, Shankar Sagar Fax : 91-22-363 6176
Sophia College Road

INDIA
----------------------------------------------------------------------

by **Anup Jala** » Tue, 11 Feb 1997 04:00:00

>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).

My original post did not give my requirements clearly. I want the users to
SELECT as well as INSERT/UPDATE/DELETE from the views. Therefore, this
approach too may not work.

Anup.
----------------------------------------------------------------------
Anup Jalan

Anup Computers Tel : 91-22-364 7992
33, Shankar Sagar Fax : 91-22-363 6176
Sophia College Road

INDIA
----------------------------------------------------------------------

by **Anup Jala** » Tue, 11 Feb 1997 04:00:00

>Someone else will have to give you the specifics, but you can implement
>a trigger that provides row-level access control, so that user JJONES
>can only access rows that contain a column Salesman with a value
>"JJONES" (or presumably a column Region with a value "East" if there's
>another table that relates JJONES to the East region).

My original post did not give my requirements clearly. I want the users to
SELECT as well as INSERT/UPDATE/DELETE from the views. Therefore, triggers
are obviously not the way out.

Anup.
----------------------------------------------------------------------
Anup Jalan

Anup Computers Tel : 91-22-364 7992
33, Shankar Sagar Fax : 91-22-363 6176
Sophia College Road

INDIA
----------------------------------------------------------------------

by **Anup Jala** » Tue, 11 Feb 1997 04:00:00

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

INDIA
----------------------------------------------------------------------

Very Computer

Implementing Data-level Security

Implementing Data-level Security

Implementing Data-level Security

Implementing Data-level Security

Implementing Data-level Security