Re: insufficient priviliges

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. Received on Wed Jul 12 2006 - 17:34:38 CDT