Re: view not found

On 18 Jan, 17:49, burrell.j..._at_yahoo.com wrote:
> On 18 Jan, 15:58, gazzag <gar..._at_jamms.org> wrote:
>
>
>
> > On 18 Jan, 15:53, burrell.j..._at_yahoo.com wrote:
>
> > > Hello,
> > > I have created, and run, as sysdba this script to create an user and a
> > > view.
> > > drop view ADNAM;
> > > create view ADNAM as select Serial from reporter.status;
>
> > > drop user ADNAM;
> > > create user ADNAM identified by ADNAM;
> > > GRANT CONNECT TO ADNAM;
> > > GRANT RESOURCE TO ADNAM;
> > > GRANT SELECT ON ADNAM to  ADNAM;
> > > GRANT UNLIMITED TABLESPACE TO ADNAM;
>
> > > But when I log in a ADNAM/ADNAM
>
> > > SQL> connect ADNAM/ADNAM
> > > Connected.
> > > SQL> select * from ADNAM;
> > > select * from ADNAM
> > >               *
> > > ERROR at line 1:
> > > ORA-00942: table or view does not exist
>
> > > Clearly view adnam does exist - but user ADNAM is not referencing it
> > > correctly?
> > > Please help me out!
>
> > > J
>
> > Why are you doing this as SYS?
>
> > I would perform the following:
>
> > 1.  Log in as SYSTEM
>
> > 2.  Create your user:
>
> > drop user ADNAM;
> > create user ADNAM identified by ADNAM;
> > GRANT CONNECT TO ADNAM;
> > GRANT RESOURCE TO ADNAM;
> > GRANT SELECT ON ADNAM to  ADNAM;
> > GRANT UNLIMITED TABLESPACE TO ADNAM;
>
> > 3.  Create your view as follows:
>
> > drop view ADNAM.ADNAM;
> > create view ADNAM.ADNAM as select Serial from reporter.status;
>
> > The convention <schema_name>.<object_name> needs to be used to create
> > objects in a schema other than your own.  Of course, you're not making
> > life too easy for yourself by naming objects and schemas as the same
> > thing!
>
> > HTH
>
> > -g- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for that!
> Not quite there yet. Still some problems.
> I created the reporter tablespaces as sysdba  - not quite sure why!
> If I invoke as sysdba
> I get this:
> SQL> @adduser
> User dropped.
> User created.
> Grant succeeded.
> Grant succeeded.
> Grant succeeded.
> drop view ADNAM.AVIEW
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> View dropped.
> View created.
> create view ADNAM.AVIEW as select Serial from reporter.status
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> If i invoke as system I get this:
> SQL> @adduser
> User dropped.
> User created.
> Grant succeeded.
> Grant succeeded.
> Grant succeeded.
> drop view ADNAM.AVIEW
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> drop view AVIEW
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> create view AVIEW as select Serial from reporter.status
> ERROR at line 1:
> ORA-01031: insufficient privileges
> create view ADNAM.AVIEW as select Serial from reporter.status
>                                                        *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> But I can "describe"  reporter.status
> $ cat adduser.sql
> drop user ADNAM;
> create user ADNAM identified by ADNAM;
> GRANT CONNECT TO ADNAM;
> GRANT RESOURCE TO ADNAM;
> GRANT UNLIMITED TABLESPACE TO ADNAM;
> drop view ADNAM.AVIEW;
> drop view AVIEW;
> create view AVIEW as select Serial from reporter.status;
> create view ADNAM.AVIEW as select Serial from reporter.status;
>
> Can you spot the problem?- Hide quoted text -
>
> - Show quoted text -

User ADNAM needs select privileges on the underlying objects owned by REPORTER. So, before you create your view and after you've created the ADNAM user, run the following as SYSTEM:

SQL> GRANT SELECT ON REPORTER.STATUS TO ADNAM; HTH -g Received on Mon Jan 21 2008 - 05:27:26 CST