From: Kirt Thomas <kremovethisspamthingthomas@gfsiinc.com>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: Can you  trigger an automatic WHERE-clause on any statement on a table?
Organization: Evil Order of the Dark Duck
Reply-To: kremovethisspamthingthomas@gfsiinc.com
Message-ID: <2fi5usg3pe7l74lfkbl1lkdb3rbln970ok@4ax.com>
References: <0y4E5.18745$Ly1.252129@news5.giganews.com> <8rugu2$fm7$1@sun-cc204.lboro.ac.uk>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 52
Date: Tue, 10 Oct 2000 02:49:56 -0500
NNTP-Posting-Host: 209.184.101.39
X-Complaints-To: abuse@sky.net
X-Trace: alpha.sky.net 971164041 209.184.101.39 (Tue, 10 Oct 2000 02:47:21 CDT)
NNTP-Posting-Date: Tue, 10 Oct 2000 02:47:21 CDT


Wouldn't this be an ideal way to use the new virtual db 'thing' in
8.2, errr, 9i ? - I had thought this was also going to be available in
8.1.7.

On Tue, 10 Oct 2000 08:36:40 +0100, "Andrew Hardy"
<Andrew.Hardy@BGTechnology.com> wrote:

>It is not possible to set a WHERE clause for a table in such a manner.
>
>The nearest thing that I can think of is to use a VIEW with a where clause
>that includes the USER, rename the table and name the view to the name that
>the table did have.
>
>Andy
>Jacob <jacmads@hotmail.com> wrote in message
>news:0y4E5.18745$Ly1.252129@news5.giganews.com...
>> Hi all
>>
>> We are currently in the process of migrating a previous single-user
>> application to a multi-user application running on the web using Forms
>> Server 6i on Oracle8i 8.1.6 on Windows NT. The multi-user application is
>> supposed to be based on user rights specified in a user table, which
>> contains information about, which modules of the application, the user is
>> allowed to use.
>>
>> The existing database schema will be changed to include a "user_id" tuple
 in
>> each table, which contains user data. This tuple will reference the user
>> table's primary key "user_id". This means, that either we have to change a
>> lot of SQL statement throughout the program to include "WHERE <user
>> data-table>.user_id = users.user_id" (the "users.user_id" is at login
>> compared with "SELECT USER FROM DUAL", and if no rows returned, the user
 is
>> denied application login).
>>
>> Does anyone know, if it is possible to create a trigger or some other
>> database mechanism, which you could use on the user data tables, which
>> automatically appends the mentioned WHERE-clause, or do we have to re-code
>> all the SQL statements in the application?
>>
>> Any advice on this subject would be greatly appreciated!
>>
>> Thanks in advance
>> Jacob
>>
>> jacmads@hotmail.com
>>
>>
>>
>>
>