Re: insufficient priviliges


"Charles Hooper" <hooperc2000@xxxxxxxxx> wrote in message
news:1152743678.501913.205640@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
fitzjarrell@xxxxxxx 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.



.

Relevant Pages

  • Re: insufficient priviliges
    ... I am not going to change the password for user abc ... I know the password for the user 'sys' and user 'system' ... You use the user 'abc' to grant privileges on the objects OWNED by ... SQL> alter user abc identified by somepasswordicanremember; ...
    (comp.databases.oracle.server)
  • Re: insufficient priviliges
    ... I am not going to change the password for user abc ... I know the password for the user 'sys' and user 'system' ... You use the user 'abc' to grant privileges on the objects OWNED by ... SQL> alter user abc identified by somepasswordicanremember; ...
    (comp.databases.oracle.server)
  • Re: insufficient priviliges
    ... I do not know the password for the user 'abc' ... I tried the following when logged in as user 'sys' as sysdba and user ... You use the user 'abc' to grant privileges on the objects OWNED by ... SQL> alter user abc identified by somepasswordicanremember; ...
    (comp.databases.oracle.server)
  • Re: insufficient priviliges
    ... I do not know the password for the user 'abc' ... I tried the following when logged in as user 'sys' as sysdba and user ... You use the user 'abc' to grant privileges on the objects OWNED by ... SQL> alter user abc identified by somepasswordicanremember; ...
    (comp.databases.oracle.server)
  • Re: insufficient priviliges
    ... to grant permission to user test select permission to objects of schema ... I do not know the password for the user 'abc' ... I know the password for the user 'sys' and user 'system' ... SQL> alter user abc identified by somepasswordicanremember; ...
    (comp.databases.oracle.server)