Check into the RDBMS feature of "row-level security" (a.k.a. PL/SQL supplied package DBMS_RLS, a.k.a. "virtual private database"), which is an integral part of the "multi-org" functionality in Oracle Apps R11i. It's perfectly suited for your requirements...
I've a paper and a quickie little demo on "virtual private databases" on my website at www.evdbt.com/papers.htm, if that would be helpful. Good article about 2.5 years ago in Oracle Magazine also... ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 23, 2002 9:28 AM > Good Morning/Afternoon/Evening! > > I have been charged with implementing a data-archive strategy into an > existing, production system. The only issue I have remaining is that > damagement wants the developers & programs to only see the active data when > they query the transaction table -- and the "power users" to see a > partition-view of active and history data when they query the transaction > table. > > program connects > SELECT * FROM transaction; -- hits the transaction > table > power user connects > SELECT * FROM transaction; -- hits the > "transaction_all" view > > Is there any easy way to do this without reverting to the use of private > synonyms for each "power user"? I've fought the good fight and have been > unable to convince damangement that this is not the best way to do this, so > I'm looking for a technical solution. > > TIA > Kevin Toepke > [EMAIL PROTECTED] > -------------------------------------------------------------------------- -- > -------------------------------------------------------------------------- -- > ------------------------ > The information in this electronic mail message is Trilegiant Confidential > and may be legally privileged. It is intended solely for the addressee(s). > Access to this Internet electronic mail message by anyone else is > unauthorized. If you are not the intended recipient, any disclosure, > copying, distribution or action taken or omitted to be taken in reliance on > it is prohibited and may be unlawful. > -------------------------------------------------------------------------- -- > -------------------------------------------------------------------------- -- > ------------------------ > The sender believes that this E-mail and any attachments were free of any > virus, worm, Trojan horse, and/or malicious code when sent. This message and > its attachments could have been infected during transmission. By reading the > message and opening any attachments, the recipient accepts full > responsibility for taking protective and remedial action about viruses and > other defects. Trilegiant Corporation is not liable for any loss or damage > arising in any way from this message or its attachments. > -------------------------------------------------------------------------- -- > -------------------------------------------------------------------------- -- > ------------------------ > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Toepke, Kevin M > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
