Re: Security - keeping data separate

Thanks, that's useful advice.

David.

"Burt Peltier" <burtpelt_at_bellsouth.net> wrote in message news:<sa8W8.61334$wj4.5215775_at_e3500-atl2.usenetserver.com>...
> My first choice would be VPD .
>
> But, if that is not an option for you, then you could always do the old 'row
> level security using security views' technique. We used this techique about
> 10 years ago in Oracle 6 .
>
> This involves setting up a view on each table that checks a security table
> and a column of data in your data table and will either allow access to a
> row or not. For example, see Sql below.
>
> Notes:
> 1) The 'with check option' prevents inserting rows outside of your 'range'
> of rows.
> 2) Do NOT grant any access to the tables underlying the view.
> 3) The 'user' column below is an Oracle pseudo column which returns the
> current logon id.
> 4) The grant to public is safe because only users listed in your security
> table have access.
>
> create or replace view my_important_data_vw
> as
> select * from my_important_data_tb a
> where exists (select 1 from security_tb b
> where a.company_code = b.company_code
> and b.oracle_userid = user)
> with check option ;
>
> grant select,insert,update,delete on my_important_data_vw to public ;
>
>
>
>
>
> "David Smith" <davidsmith633_at_hotmail.com> wrote in message
> news:d54a9dfe.0207070611.6dce8b3c_at_posting.google.com...
> > Mark Townsend <markbtownsend_at_attbi.com> wrote in message
 news:<B94BA038.38A79%markbtownsend_at_attbi.com>...
> >
> > > Look into Virtual Private Database - VPD - see
> > > http://otn.oracle.com/deploy/security/oracle8i/pdf/vpd_wp6.pdf
> >
> > Thanks for that. I see that Orcale 9i also supports VPD.
> >
> > Unfortunately this feature is only available in the Enterprise edition
> > (as with nearly all the cool stuff!!!)
> >
> > For a company which cannot afford the high cost of these licences, can
> > anyone recommend a more simplistic approach that will provide some
> > degree of data separation using the Standard Edition?
> >
> > (I should add that the solution I proposed earlier, with separate
> > tables for each company on whom data is held, would make it harder to
> > do queries across multiple companies for the administrator. This would
> > perhaps involve a view, which I don't think can be optimized as well
> > as queries on tables.)
> >
> > Thanks,
> > David.
Received on Mon Jul 08 2002 - 11:42:34 CEST