Unfortunately in my experience you can only use one schema per oracle
datastore. However you should be able to use any user that has access to the
table to connect to it IF and only if you have set the schema in the datastore
configuration.
That is really suboptimal if you are serving up layers from different schemas,
because you can't use connection pooling unless you use JNDI.
I think the reason you can't use one user to access multiple schemas with one
datastore is because when you choose a layer from the layer chooser I don't
think it keeps a record of which schema the layer comes from (just guessing).
So if it can't find a reference to the layer you choose in
USER_SDO_GEOM_METADATA, it looks in ALL_SDO_GEOM_METADATA, but unless the
schema is set in the datastore config, it _still_ doesn't know which schema the
layer really comes from. That results in the JDBC datastore issuing a query
like "SELECT MY_PK FROM MY_TABLE WHERE 0=1", which results in "ORA-00942: table
or view does not exist" if the table does not belong to the user. And here is
the exception:
java.lang.RuntimeException: Error occurred while building the resources for the
configuration page
at
org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:192)
...
Caused by: java.io.IOException: Error looking up primary key
at org.geotools.jdbc.JDBCDataStore.getPrimaryKey(JDBCDataStore.java:811)
at org.geotools.jdbc.JDBCFeatureSource.<init>(JDBCFeatureSource.java:76)
at
org.geotools.jdbc.JDBCDataStore.createFeatureSource(JDBCDataStore.java:650)
... 73 more
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist
...
at
org.geotools.jdbc.JDBCDataStore.createPrimaryKey(JDBCDataStore.java:885)
at org.geotools.jdbc.JDBCDataStore.getPrimaryKey(JDBCDataStore.java:778)
... 80 more
So I think the best thing for Geoserver is either to enforce the use of a
schema name in the Oracle datastore configuration or to only allow selection of
layers from tables that the user owns. Probably the first solution would be
best, I think.
Sorry for the long reply, but what do others think?
-Miles
Eija wrote:
> we tried giving user geoserver dba rights, it still didn't work. Only
> schema owner can connect to tables.
>
> I found the mistake I had done when creating the view - it had no
> primary key. Now the views work as well (or badly) as original tables with
> owner.
>
> Sorry to bother:)
>
> Eija
>
>
> Rahkonen Jukka wrote:
> >
> > Hi,
> >
> > Can't the table owner make a public synonym and grand some rights for
> > the Geoserver user?
> >
> > -Jukka Rahkonen-
> >
> >
> >
> >> -----Alkuperäinen viesti-----
> >> Lähettäjä: Eija [mailto:[email protected]]
> >> Lähetetty: 11. marraskuuta 2009 14:43
> >> Vastaanottaja: [email protected]
> >> Aihe: [Geoserver-users] view - window SRID does not match layer SRID
> >>
> >>
> >> Hi,
> >>
> >> I can only get tables to layers on Geoserver 2.0 when
> >> connected as schema owner on Oracle 10g.
___________________________________________________________________________
Australian Antarctic Division - Commonwealth of Australia
IMPORTANT: This transmission is intended for the addressee only. If you are not
the
intended recipient, you are notified that use or dissemination of this
communication is
strictly prohibited by Commonwealth law. If you have received this transmission
in error,
please notify the sender immediately by e-mail or by telephoning +61 3 6232
3209 and
DELETE the message.
Visit our web site at http://www.antarctica.gov.au/
___________________________________________________________________________
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
