Pulled this off the "other" list a long time ago. It was posted by Raj Mithal.
-------------------------------------------------------------------------------
Try this:-
Becoming a User with Password Expiry enabled
In Oracle 7.x you could become a user by storing the encrypted password,
changing the password and then reinserting the old password,
e.g.: SELECT username, password FROM dba_users;
(this shows the encrypted password)
username Password (encrypted)
GP 09HJKLK
Temporarily modify the password:
ALTER USER GP IDENTIFIED BY GP;
connect GP/GP
-- Restore the original password
ALTER USER GP IDENTIFIED BY VALUES '09HJKLK';
>From Oracle8.x onwards, if you are using new password expiry features, as
soon as you change the password (alter user ... identified by ...), the
expiry date of the user is shifted further as defined by the DBA.
So a work around is :
Oracle creates the encrypted password based on username and supplied
password.
Create another user by same name but make sure oracle stores in lower case.
CREATE USER "gp" IDENTIFIED BY abc123;
SELECT username, password FROM dba_users;
username password
GP 09HJKLK
gp I0989J
-- This creates the encrypted version of the "abc123"
-- password for user GP.
Fill in the encrypted password corresponding to lowercase username,"gp" into
user GP:
ALTER USER GP IDENTIFIED BY VALUES 'I0989J';
CONNECT GP/abc123;
Afterwards drop user "gp"; (make sure u enclose in ", to force oracle to
drop the lower case user)
Restore the original password for GP:
ALTER USER GP IDENTIFIED BY VALUES '09HJKLK';
That is it, u have become the other user.
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
"The problem with some people is that when they aren't drunk, they're sober."
--William Butler
Yeats.
wisernet100@yahoo
.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: grant access to another
user's objects?
06/10/02 02:55 PM
Please respond to
ORACLE-L
Kevin Loney wrote about it as "become_another_user.sql" starting in the
7.3 DBA Handbook.
the problem is, if you use and enforce password history, you can't make
the changes because the saved off password won't be allowed to be
reused unless you cycle through a bunch of passwords (as many as you
keep history for)
Rachel
--- Stephane Faroult <[EMAIL PROTECTED]> wrote:
> Jack Silvey wrote:
> >
> > All,
> >
> > We have a new info security group that is going to do
> > all the object grants on our warehouse. I don't want
> > them to login as schema owners to do this.
> >
> > Until this time, I have been granting access to other
> > user's object by logging in as a dba, creating a
> > procedure in the owner's schema with the EXECUTE
> > immediate statement, and passing it the 'grant select
> > on table a' statement for execution. That way, the
> > grant actually executes as the object owner, but can
> > be issued from a DBA account.
> >
> > Short of having a custom SP in each user's schema for
> > this type of grant, can anyone think of another way?
> >
> > thx,
> >
> > jack silvey
> >
>
> Yup. Save SCOTT's encrypted password (DBA_USERS), change it to
> yaddayadda, CONNECT SCOTT/YADDAYADDA, ALTER USER SCOTT IDENTIFIED BY
> VALUES 'encrypted thing' and nobody has noticed anything; then, being
> SCOTT, you grant whatever you want. There is a become.sql on the
> Oriole
> site which does it, and I have seen very similar versions elsewhere.
> In
> fact this is the old way to do it ...
> --
> Regards,
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Thomas
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
