access another user's data in a stored procedure - BECOME USER pr

From: Magaliff, Bill <Bill.Magaliff_at_lendware.com>
Date: Wed, 24 Apr 2002 09:07:35 -0800
Message-ID: <F001.0044E1A4.20020424090735@fatcity.com>

I'm writing a stored procedure to be run by the dba. I want to provide the ability to manipulate data from a schema specified at runtime. I have the schema owner as an input variable, but I see three possible way to do this:

1. have the dba verify that all necessary DML privs on that schema are granted to the user running the proc, and that synonyms are properly defined - therefore there's no need to put a schema qualifier in front of every object name in the proc
2. verify all DML privs as above, put DO put a schema qualifier in front of every object name in the proc - no need, therefore, to have the synonyms (although they won't hurt)
3. grant BECOME USER to the running user, and issue ALTER SESSION SET CURRENT_SCHEMA before running the proc.

Option 3 seems the easiest to code, but I'm not sure about this particular priv - some of the info on MetaLink seems to indicate this priv is only valid for importing, but not sure.

anyone have any experience with this priv, or willing to provide general feedback on how to proceed? I'm relatively new to this and am also in search of guidelines to follow when writing this sort of stuff.

many thanks.

bill
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Magaliff, Bill
  INET: Bill.Magaliff_at_lendware.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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). Received on Wed Apr 24 2002 - 12:07:35 CDT