RE: Become User privilege

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.

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).
>