Re: insufficient priviliges

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Jul 2006 04:41:43 -0700
Message-ID: <1152877302.989724.86240@75g2000cwc.googlegroups.com>

Matthias Hoys wrote:
> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
> news:1152743678.501913.205640_at_35g2000cwc.googlegroups.com...
> > fitzjarrell_at_cox.net wrote:
> >> SQL> alter user abc identified by somepasswordicanremember;
> >>
> >> And, then, by the magic of Oracle you can connect as 'abc':
> >>
> >> SQL> connect abc/somepasswordicanremember
> >> Connected.
> >> SQL> select * from pqr;
> >>
> >> [all sorts of results here]
> >>
> >> SQL> grant select on pqr to test;
> >>
> >> Grant succeeded.
> >>
> >> SQL> connect test/somepasswordidontknow
> >> Connected.
> >> SQL> select * from abc.pqr;
> >>
> >> [all sorts of results here]
> >>
> >> If this is some 'real world' problem which you've oversimplified in
> >> order to get an answer then the above instructions won't do you any
> >> good as you cannot change the application user password without
> >> breaking the application. In that case I'd go to whoever created the
> >> application user and ask, pretty please, for the password. Of course,
> >> if you have SYS as SYSDBA privileges without authorization you should
> >> update your resume as you won't be long for this job.
> >>
> >>
> >> David Fitzjarrell
> >
> > Just adding to the excellent tip above, before changing the user's
> > password, execute the following query:
> > SELECT
> > USERNAME,
> > PASSWORD
> > FROM
> > DBA_USERS
> > WHERE
> > USERNAME='ABC';
> >
> > When finished, update the user with the original encrypted password:
> > ALTER USER ABC IDENTIFIED BY VALUES 'ENCRYPTEDPASSWORD';
> >
> > For obvious reasons, this should be used as a last resort.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
> >

> And of course, when you use this workaround and change the password, the
> whole application stops working because db connections will still use the
> old password ... Unless this user isn't used at all for db connections, and
> then I see no problems using this solution.

Explore the @ command in SQLPlus. Throw the command to be executed into a text file:

* Change password
* Log off
* Connect as the user
* Perform grant
* Log off
* Connect as SYS
* Reset user password by values

Unless it takes five minutes to log into the database, there is little chance that any one would notice if performed in off hours.

I like the other solution that was proposed earlier.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jul 14 2006 - 06:41:43 CDT