Re: Security - keeping data separate

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)

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 - 06:17:40 CEST