Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Implementing Data-level Security
"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 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.inINDIA