Oracle-L: Re: grant access to another user's objects?

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Mon, 10 Jun 2002 14:53:36 -0800
Message-ID: <F001.00479A9F.20020610145336@fatcity.com>

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
rthomas_at_hypercom.com
"The problem with some people is that when they aren't drunk, they're sober." --William Butler Yeats.

                                                                                                                                           
                      wisernet100_at_yahoo                                                                                                    
                      .com                     To:       ORACLE-L_at_fatcity.com                                                              
                      Sent by:                 cc:                                                                                         
                      root_at_fatcity.com         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 <sfaroult_at_oriole.com> 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: sfaroult_at_oriole.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
> (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: wisernet100_at_yahoo.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
(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: rthomas_at_hypercom.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
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jun 10 2002 - 17:53:36 CDT