Re: User dependened views

In article <3A02FF1A.4A61BB6B_at_gmx.de>,
  Carsten Baeumchen <carsten.baeumchen_at_gmx.de> wrote:
> Hi there,
>
> I have got the following problem:
>
> I use some special type of views that are accessible to every user.
> Every user is able to open/read the view.
> Now I have the problem that when different user execute this view-sql
> the content is replaced by the new query conditions.
>
> i.e. User one opens the view with a certain "where" condition, then
 user
> 2 does the same with another condition, then content 1 is replaced
> because of the same
> name of the view.
>
> My question: is there a way to store this view by a user dependet view
> name : "user".ViewAccounts.
>
> The sql statement should be able to integrated the current logged user
> name into the main view name. This must happen automatically.
> Maybe something like that "create or replace view
> 'username'.ViewAccounts ..."
>
> The result should look like
>
> Bert.ViewAccounts
> Peter.ViewAccounts
> DBA.ViewAccounts
> ....
>
> Thanks a lot
> Carsten
>
>

Of course the 'contents of the view' are *not* replaced. A view has no contents, it is a select only.
The customary approach in this (which is widely used in the datadictionary, and in the datadictionary there is no need to have Peter.View and Dave.View), is to store the 'owner' of the record in the table and add 'where owner = user' to the view condition. User is a pseudo function returning the current user. This will return correct results and if you think it doesn't I believe you should *proof* that.

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.