Re: insufficient priviliges

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 Received on Wed Jul 12 2006 - 12:36:20 CDT