Re: Oracle data security question

gregad_at_deakin.edu.au (Greg White) wrote:
>
>
> Hello,
>
> We are in the early stages of the design phase of a new system (using Oracle
> V7.1.6, Designer 2000, Unix platform).
>
> We want to limit groups of users to restricted sets of data. We have
> contemplated using VIEWS, but given the limitations associated with them (ie.
> unable to INSERT, UPDATE or DELETE where the view's query contains table
> joins, etc), they don't appear to be a satisfactory solution.
 <SNIP>-------
> Any suggestions or comments would be appreciated.
>
> Thanks,
> Greg.
>
> ************************************
> Greg White
> Deakin University
> Victoria, Australia.
> e-mail gregad_at_deakin.edu.au.
> ************************************
>
I have used views on a V6 database where the view was formed of the selected contents of the table joined to the running program. If you're going against a single table it works OK - except for the performance hit which isn't too bad.
On V7 with ROLEs you can assign the user roles, with a default role that only gives select priv on what you want them to see in SQL*PLUS. Then in the applications that you provide you enable/disable other roles that give them insert,update, delete privlages as appropriate. Of course the trick to using ROLEs is that you are code the applications for the user and embedding the appropriate SET ROLE statements.

Did I answer your questions?

Good luck

Jim
<No opinions, recommendations, or claims as to accuracy are made by  my employeer for statements that I make> Received on Fri Aug 11 1995 - 00:00:00 CEST