Re: equivalent of su in oracle?

"Richard Chen" <qchen_at_snet.net> wrote in message news:397D8CAA.9A6281B6_at_snet.net...
> The method you outlined was mentioned in my first email.
> This does not work in many situations. In my situation the many
> schemas are used by live web site in production!

Well, my alternative suggestion in that case was to get on the phone to your schema owners, and ask them nicely to give you full permissions on their objects with the grant option. You can immediately and subsequently administer to your heart's content that way.

>You cannot reset
> the passwords while the web site is up and running. Since these schemas
> are for the web sites, no particular dba/programmer is concerned
> about politeness. But one does have to remember A LOT of different
> passwords if you've get a lot of schemas :-) Especially if you are new
> to a long established legacy system.
>
> Being the dba with system account, I think it is proper to be able to do
 this
> without resetting passwords.
>

Ah, the old classic "Opinion is divided on the subject: everyone else says it is, and I say it isn't"! The fact is, Oracle doesn't think it proper (otherwise the functionality would be there by now), and most Oracle DBAs of my acquaintence don't think it entirely kosher, either! But whatever: as you've gathered by now, what you specifically have asked is not possible in Oracle, and by design is this the case. You've been offered a number of workarounds, and hopefully you'll accept (however reluctantly) that one of these 'bullets' must be bitten at some point.

> I am surprised that this seems impossible to do, given the response in
 this
> group.
>
> Thanks anyway.
>

No problems. Hope you find a workable solution you can live with.

Regards
HJR
> Richard
>
> "Howard J. Rogers" wrote:
>
> > You can achieve what you want to do by surreptitiously log on as the
 user
> > concerned.
> >
> > First, select username, password from dba_users.
> > Copy out the hexadecimal version of the password for the relevant user
 into
> > a text file somewhere.
> > Now, issue the command 'alter user mildred identified by
> > newpassword' -you'll now be able to connect as Mildred, because you've
 just
> > reset her password.
> >
> > Grant all the privileges you like on whatever objects she owns (you have
> > become her, after all!)
> >
> > Finally, 'alter user mildred identified by values 'old password
 hexadecimal
> > string';
> >
> > When Mildred next tries to log on, she will not notice a thing -the
 password
> > will be as it always was for her.
> >
> > I have to say, however, that logging on as other people, and granting
> > permissions to others on objects in their schemas, is usually considered
 a
> > bit dodgy -and impolite to boot. There are good reasons why object
 owners
> > are the only people authorised to grant permissions on their objects.
> >
> > Incidentally, you can avoid all this hassle by simply contacting Mildred
 (or
> > whoever) in the first place, and asking her nicely whether she will
 grant
> > permissions on her objects to you, the DBA, "with grant option". That
 makes
> > you an authorised person for the subsequent granting of those
 permissions to
> > anyone else you fancy. And it's all above board and legititmate.
> >
> > Regards
> > HJR
> >
> > "Richard Chen" <qchen_at_snet.net> wrote in message
> > news:397AF5DB.7DDEC6BF_at_snet.net...
> > > Maybe I did not understand you correctly. But this does not do what
> > > I wanted to do. I would like to become a particular user and then
 grant
> > > priviledges for tables belonging to that user to other users.
> > > But I tried this and it does not work:
> > >
> > > connect system/password
> > > alter session set current_schema = SCOTT
> > > grant select on scott.table_name to another_user
> > >
> > > The error message is:
> > > ERROR at line 1:
> > > ORA-01031: insufficient privileges
> > >
> > > So if my understanding of your info is correct, this is far from being
 the
> > > equivalent of su in oracle.
> > >
> > > The reason I am asking this question in the first place is that I
 frequently
> > >
> > > need to create views which require explicit grants of "select" to
 other
> > > schema's tables. Even though I have system account and dba role,
> > > I still sometimes have to find other people to tell me what the
 passwords
> > > are
> > > for particular schemas. This is very annoying.
> > >
> > > Thanks for any further info.
> > >
> > > Richard
> > >
> > > Erwin Dondorp wrote:
> > >
> > > > Richard Chen wrote:
> > > > > Does anyone know a way in oracle to let the system user become any
 user
> > > > > without knowing the users' password?
> > > >
> > > > Any Oracle user that has the "BECOME USER" right can do this.
> > > > "BECOME USER" is part of the role "IMP_FULL_DATABASE", which
> > > > again is included in the "DBA" role.
> > > >
> > > > IMP_FULL_DATABASE has this right because you need this functionality
> > > > when you restore a database from an export file, the file contains
> > > > objects of many users, yet you don't need to type the passwords
> > > > for all these users.
> > > >
> > > > The BECOME_USER right gives you the ability to execute the
> > > > ALTER SESSION SET CURRENT_SCHEMA = <schemaname>
> > > >
> > > > Beware 1: your login(username) wil not change, only your schema.
> > > > The data dictionary views (e.g. USER_TABLES) reflect the
> > > > status for the user that is logged in.
> > > >
> > > > Beware 2: This command is undocumented and supposed to be used only
> > > > by the IMP command.
> > > >
> > > > Erwin
> > > > --
> > > > Erwin Dondorp
> > > > <http://www.dondorp.com/>
> > >
>
Received on Wed Jul 26 2000 - 00:00:00 CDT