[Geoserver-devel] [Geoserver-users] view - window SRID does not match layer SRID [SEC=Unclassified]

Miles_Jordan · November 12, 2009, 7:47am

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:eija@…2402…]
>> Lähetetty: 11. marraskuuta 2009 14:43
>> Vastaanottaja: geoserver-users@lists.sourceforge.net
>> 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/
___________________________________________________________________________

Julian · November 12, 2009, 11:19pm

I don't see any problems with the way its set up.
I had trouble with the older GeoServer versions that I had errors like that
coming up when I tried to use a non schema owner to connect to some Views or
Tables.
However with GeoServer 2.0 these issues got resolved and the primary key
error message only pops up if there really wasn't any primary key applied or
the schema you are trying to connect as doesn't have enough privs.
Maybe it is just me that I did something along the way different in the way
I connect to the databases and everything.
Can you try to explain what the issue exactly is that you are looking at
here?
Right now you should only be able to apply one Schema per datastore, which
can be seen either as a bad or good solution, depending on the way you look
at it. If you chose that datastore to add a new layer, you get a list which
shows all the views and tables this Schema can access. So there shouldn't be
any issues here. Then when it can't find an entry in the
USER_SDO_GEOM_METADATA it looks in the ALL_SDO_GEOM_METADATA and should find
it in there. If it doesn't it will throw the primary key and insufficient
privileges error from my experience. At least thats how the old version of
GeoServer handled it for me.

Please correct me if I'm wrong in any of these assumptions.

Julian

Miles Jordan wrote:

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:eija@anonymised.com]
>> Lähetetty: 11. marraskuuta 2009 14:43
>> Vastaanottaja: geoserver-users@lists.sourceforge.net
>> 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
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--
View this message in context: http://old.nabble.com/Re%3A--Geoserver-users--view---window-SRID-does-not-match-layer-SRID--SEC%3DUnclassified--tp26314707p26328313.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.

Andrea_Aime3 · November 13, 2009, 10:40am

Miles Jordan ha scritto:

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).

Correct. This is something that has been bugging me as well.
I've opened a jira issue about this:
http://jira.codehaus.org/browse/GEOT-2833

The change required is not going to be small thought, it's going
to change the core classes as well as all of the dialects.
Anyone interested in giving it a crack?

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.

Sounds like a viable short term workaround, thought I don't like
the idea very much.
Care to open a request?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

Miles_Jordan · November 18, 2009, 4:08am

Andrea Aime wrote:

Miles Jordan ha scritto:
> 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).

Correct. This is something that has been bugging me as well.
I've opened a jira issue about this:
http://jira.codehaus.org/browse/GEOT-2833

The change required is not going to be small thought, it's going
to change the core classes as well as all of the dialects.
Anyone interested in giving it a crack?

> 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.

Sounds like a viable short term workaround, thought I don't like
the idea very much.
Care to open a request?

Done: http://jira.codehaus.org/browse/GEOT-2834

Also submitted a small patch for it. Although it should probably be changed back if the main issue gets fixed. I'll post a note on there too.

-Miles

___________________________________________________________________________

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/
___________________________________________________________________________

Miles_Jordan · November 18, 2009, 4:30am

Hi Julian,

jberti wrote:

I don't see any problems with the way its set up.
I had trouble with the older GeoServer versions that I had errors like
that
coming up when I tried to use a non schema owner to connect to some
Views or
Tables.
However with GeoServer 2.0 these issues got resolved and the primary
key
error message only pops up if there really wasn't any primary key
applied or
the schema you are trying to connect as doesn't have enough privs.
Maybe it is just me that I did something along the way different in the
way
I connect to the databases and everything.

Yep. You are specifying a schema.

Can you try to explain what the issue exactly is that you are looking
at here?

The issue is that users do try to use the oracle datastore without specifying a schema, which results in an exception if they try to use a table outside of their default schema.

The reason it isn't affecting you is because you are specifying a schema.

Right now you should only be able to apply one Schema per datastore

Incorrect. As it stands you can choose not to specify a schema when creating the Oracle datastore.

which
can be seen either as a bad or good solution, depending on the way you
look
at it. If you chose that datastore to add a new layer, you get a list
which shows all the views and tables this Schema can access.
So there shouldn't be
any issues here. Then when it can't find an entry in the
USER_SDO_GEOM_METADATA it looks in the ALL_SDO_GEOM_METADATA and should
find
it in there. If it doesn't it will throw the primary key and
insufficient
privileges error from my experience. At least thats how the old version
of
GeoServer handled it for me.

Please correct me if I'm wrong in any of these assumptions.

I hope this clarifies the issue for you

-Miles

___________________________________________________________________________

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/
___________________________________________________________________________