"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
news:1152743678.501913.205640_at_35g2000cwc.googlegroups.com...
> fitzjarrell_at_cox.net wrote:
>> newbie wrote:
>> > I have a schema called 'abc'
>> > I do not know the password for the user 'abc'
>> >
>> > I know the password for the user 'sys' and user 'system'
>> >
>> > I tried the following when logged in as user 'sys' as sysdba and user
>> > 'system' and user 'sys' (normal)
>> >
>> > select * from abc.pqr where 'pqr' is a table in schema 'abc'
>> >
>> > I get the result
>> >
>> > However If I create another user 'test' and try the following
>> > select * from abc.pqr it gives me the result
>> >
>> > ORA-00942: table or view does not exist
>> >
>> >
>> > I tried the following when I am logged in as user 'sys' as sysdba or
>> > sysoper or normal or user system
>> >
>> > grant select on abc.pqr to test;
>> >
>> > The following is the error message
>> >
>> > ORA-01031: insufficient privileges
>> >
>> >
>> > How do I resolve this...Please help
>>
>> You use the user 'abc' to grant privileges on the objects OWNED by
>> 'abc'. It's that simple. Possibly you created this 'abc' user to test
>> things; since you DO have access as SYS as SYSDBA you can CHANGE the
>> password for 'abc':
>>
>> 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.
Received on Thu Jul 13 2006 - 14:44:20 CDT
