RE: Become User privilege

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 30 Aug 2000 16:14:24 +1000
Message-Id: <10603.115815@fatcity.com>

Hi Matt,

The ALTER SESSION SET CURRENT_SCHEMA command is at last mentioned in the Oracle8i documentation set.
However, it only changes the default schema of reference, not the user identity, and therefore cannot be used to perform grants on behalf of the schema owner.

What you can do however is to temporarily create a procedure in that schema which takes an arbitrary SQL statement as the argument and executes it using DBMS_SQL. You then just call that procedure with the grant statement as the argument, and it will be performed in a recursive session with the identity of the schema owner.

This idea comes from http://www.oracledba.co.uk/tips/grants.htm.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

@

@ Going to OpenWorld?
@ Catch the Ixora performance tuning seminar too!
@ See http://www.ixora.com.au/seminars/ for details.

-----Original Message-----
From: Adams, Matthew (GEA, 088130) [mailto:MATT.ADAMS_at_APPL.GE.COM] Sent: Wednesday, 30 August 2000 8:29
To: Multiple recipients of list ORACLE-L Subject: RE: Become User privilege

Where is this documented? I'm trying to do grants after switching the current schema; it's not working.

---

Matt Adams - GE Appliances - matt.adams_at_appl.ge.com <Discussing an image of a black rectangle silhouetted against the Martian landscape>
"That is the top of the calibration target, that is _not_ in fact a monolith."
- NASA TV commentator, 7/5/97

> -----Original Message-----
> From: Schoen Volker [mailto:v.schoen_at_inplan.de]
> Sent: Tuesday, August 29, 2000 11:45 AM
> To: Multiple recipients of list ORACLE-L
> Subject: AW: Become User privilege
>
>
> You can use the alter session to change your current schema.
> Every object
> you create will be created in current schema.
>
> alter session set current_schema=<schema where to create objects);
>
> Hope this will help
>
> Volker Schön
> INPLAN RUHR
> E-Mail: mailto:v.schoen_at_inplan.de
> http://www.inplan.de
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: Boivin, Patrice J [mailto:BoivinP_at_mar.dfo-mpo.gc.ca]
> Gesendet: Dienstag, 29. August 2000 17:12
> An: Multiple recipients of list ORACLE-L
> Betreff: Become User privilege
>
>
> Is there a way a DBA can log on as other users (without
> saving the encrypted
> password, changing the password of the account, logging in
> using the new
> password, logging out, and restoring the original password)?
>
> I see import uses a privilege called BECOME USER, is there
> any way I can
> invoke this priv from a SQL script?
>
> I have a number of procedures, views, packages etc. that were
> coded without
> a schema specified in front of table and object names, and I
> can't recompile
> them as SYSTEM or using my DBA account, because Oracle
> substitutes my schema
> wherever a schema was not specified.
>
> If I could use the BECOME USER privilege, I could run a
> script that would
> log in as each user, in turn, recompile all that user's
> object, then move to
> the next user, and so on.
>
> Regards,
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)
>
> Systems Admin & Operations | Admin. et Exploit. des systèmes
> Technology Services        | Services technologiques
> Informatics Branch         | Direction de l'informatique
> Maritimes Region, DFO      | Région des Maritimes, MPO
>
> E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>
>
> --
> Author: Boivin, Patrice J
>   INET: BoivinP_at_mar.dfo-mpo.gc.ca
>
> 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).
> --
> Author: Schoen Volker
>   INET: v.schoen_at_inplan.de
>
> 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).
>

--
Author: Adams, Matthew (GEA, 088130)
  INET: MATT.ADAMS_at_APPL.GE.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

Received on Wed Aug 30 2000 - 01:14:24 CDT