Re: Implementing Data-level Security

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

Hi,

Just a short commen: even though you've used nested sub-queries, Oracle may still reorder this into a direct table join. Meaning, that a nested loops merge is done rather than executing the subquery for each record of the main query.

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.

-- 
Barry P. Grove                          BCTEL  (604)432-4468
barry_grove_at_mindlink.bc.ca              Human Resources Consulting
Developer, Oracle DBA, Unix Sysadmin    6-3777 Kingsway, Burnaby, BC