insufficient priviliges - comp.databases.oracle.server

Message from discussion insufficient priviliges

fitzjarrell@cox.net

View profile

More options 12 July 2006, 18:36

Newsgroups: comp.databases.oracle.server

From: "fitzjarr...@cox.net" <fitzjarr...@cox.net>

Date: 12 Jul 2006 10:36:20 -0700

Local: Wed 12 July 2006 18:36

Subject: Re: insufficient priviliges

  Reply to author
  |
  Forward | Print | View thread
  | Show original
  | Report this message | Find messages by this author
  

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

Reply to author Forward