Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Restrict View data access to some users only.

Re: Restrict View data access to some users only.

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 18 Nov 2006 17:14:21 -0800
Message-ID: <1163898861.225430.317470@m7g2000cwm.googlegroups.com>

On Nov 18, 9:38 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> Beto wrote:
> > Hi All ,
>
> > I need a help from the experts!
>
> > First of all , is it possible to create two view with same name?
>
> > Let's try to explain...
>
> > Today i have one view that store data for a period of 1 year and i
> > have some clients that access these data.
> > I want to restrict this period to 6 months only , but only to one
> > client (for example Client application A).
> > For example , through the Client A today i can check the bills from a
> > periof of one year , but i want to restrict to 6 month , so the Client
> > A will be allowed to check only bills from 6 months.
>
> > So i was thinking to create another view with the same name with
> > condition to store data only for a period of 6 month .
>
> > I don't know how to make that only users from Client Application A
> > access this new view.
>
> > I have a table that stores all users that access the client application
> > A.
>
> > Any other suggestions to restrict the Client A access to period of 6
> > month only without creating new view wil help me a lot!
>
> > TIA!
>
> > rgds,Try FGAC (Fine-Grained Access Control), also known as VPD (Virtual
> Private Database). Using application contexts, on-logon triggers and
> FGAC policies you can control who sees what transparently to querying
> users. That is, different users will see different result sets while
> querying the same table or view. Since you are using views already, you
> can get away without FGAC with only an application context and an
> on-logon trigger to set this context. You then add predicates using
> sys_context() to the view base query, which will limit data visibility
> depending on context values. Things to familiarize yourself with are:
> AFTER LOGON triggers, application contexts (CREATE CONTEXT,
> DBMS_SESSION.SET_CONTEXT/CLEAR_CONTEXT, SYS_CONTEXT()). Oracle
> documentation on these is pretty good and there are numerous examples
> on FGAC and application contexts all over the net.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com- Hide quoted text -- Show quoted text -

Prior to database event logon triggers and Oracle contexts which provide a means to create "parameterized" views you would have to resutl to hardcoding conditions such as performing a decode on the user to control the cut-off date calculation or join to a table with the cut-off value when the number of users grew larger than practical for the decode. The more users the more likely you would need to change the code.

Where only one or two users have a different requirement and the Id's of these users is unlikely to change then these older techniques may still be of use rather than createing a peice of code which fires for every user.

HTH -- Mark D Powell -- Received on Sat Nov 18 2006 - 19:14:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US