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 -> Implementing Data-level Security

Implementing Data-level Security

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

"Barry P. Grove" <barry_grove_at_mindlink.bc.ca> wrote:

>Anup Jalan 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.
>>
>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
Bombay 400 026                   email : anupcomp_at_giasbm01.vsnl.net.in
INDIA
Received on Mon Feb 10 1997 - 00:00:00 CST

Original text of this message

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