Re: help:login as another schema

First off, that A374682684fdhsi3 is not "encrypted", it's hashed. Subtle difference, I suppose, in one way -but it makes all the difference between being able to do what you want or not.

Now what follows is generally regarded as extremely bad DBAing, on the grounds that it is considered a bit rude (at least!) to impersonate someone within a database without their knowledge, but if you have to, I suppose you have to.

First, select username, password from DBA_USERS. When you find the hashed password for the User you need to log on as, copy it somewhere safe. Then simply issue the 'alter user fred identified by newpassword' command, so you can now do a 'connect fred/newpassword'. Do what you have to do as fred, and when you're done, re-connect as someone like system. Now comes the fiendish part:

alter user fred identified by values 'A375439DC97';

The use of the "values" keyword means that what you then supply is not treated as a new password (God forbid I ever need to use a password like that!), but is instead recognised as being an already hashed version of a password. The hash value is then placed back in the data dictionary, so that when fred next logs on, he'll supply as a password what he's always supplied, and the hash value calculated will match the re-placed hash value in the data dictionary.

Voilą! Unless Fred tries to log on at precisely the time you're poking around inside the database as his alter ego, he won't ever know his account was hacked.

Having said all of that, it's totally unnecessary to go to all this trouble just to look at some explain plans (though it's kinda fun to do -just don't get caught, and if the cops ask, I never told you!). Have a look at "execute dbms_system.set_sql_trace_in_session(X,Y,TRUE)" (where X and Y are the SID and Serial# of the session you are interested in, as determined from a quick look in v$session). That'll produce trace files (ie, O/S text files) which you can look at (having tkprof'd them first), and which contain the relevant explain plans, with no illegalities or unethical behaviour required! (You'll need to set_sql_trace_in_session(X,Y,false) before the trace file is closed properly).

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Mike F" <dbadba62_at_hotmail.com> wrote in message
news:h4Rd8.888$Vs2.14_at_nwrddc01.gnilink.net...


> Sometimes, I need to login as another schema in order to explain plan for

> some statements.  When I looked at dba_users, the password is something

like


> A375439DC97, etc.  Is there any way we can use this encrypted password to

> login as another schema?  Or I have to get the real password to do that,

> which changes frequently?

>

> Or is there anything like 'alter session set current_user = another

scchema'


> so that I can run explain plan?

>

> Thanks for your help.

>

>