From: "Andrew Hardy" 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? Date: Tue, 10 Oct 2000 08:36:40 +0100 Organization: Loughborough University, UK Lines: 45 Message-ID: <8rugu2$fm7$1@sun-cc204.lboro.ac.uk> References: <0y4E5.18745$Ly1.252129@news5.giganews.com> NNTP-Posting-Host: heat.bggrc.co.uk X-Trace: sun-cc204.lboro.ac.uk 971163394 16071 194.35.186.4 (10 Oct 2000 07:36:34 GMT) X-Complaints-To: usenet@lboro.ac.uk NNTP-Posting-Date: 10 Oct 2000 07:36:34 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 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 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 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 > > > >