Re: [Geoserver-users] Grants in oracle for publishing layer [SEC=Unclassified]

[Miles J. <Mil...@aa...>]

Are you specifying a schema for the datastore? If you do, then you can use a different user. If you have the scenario where you want to use one user to connect to multiple schemas, you should set up a JNDI connection and then set up multiple geoserver datastores (again, specifying schemas for them) using that. Then you will be able to share the database connections across multiple datastores/schemas.

Regards,

Miles


Rahkonen Jukka wrote:
> Hi,
>
> If is has something to do with finding the primary key then it might
> be handled trough the geoserver_metadata table system. It has been
> discussed on this mailing list couple of times not so long time ago,
> try to find the thread. I was one of the writers.
>
> -Jukka Rahkonen-
>
>
> -----Alkuperäinen viesti-----
> Lähettäjä: Primoz Kogovsek [mailto:pri...@gm...]
> Lähetetty: to 30.9.2010 14:10
> Vastaanottaja: Ivan Price
> Kopio: Rahkonen Jukka; geo...@li...
> Aihe: Re: [Geoserver-users] Grants in oracle for publishing layer
>
> So far i am only able to publish the tables with user that is owner of
> the schema. With different users it is no go. Any ideas how to fix this?
>
> regards
> Primoz
>
> 2010/9/29 Ivan Price <iva...@ac...>
>
>> i can also confirm that we can do this with users with only create
>> session and select on the target table privledges.   in your datastore
>> config have you explicitly set the SCHEMA that the data is meant to be
>> in, which is perhaps different to the 'publishing (connecting) user' ?
>>
>> can you connect using sqlplus as the publishing user and perform the
>> same select query that geoserver is trying to perform ? (the exact sql
>> is in the geoserver log if you turn verbose logging on)
>>
>> -i
>>
>>
>>
>> 2010/9/29 Rahkonen Jukka <Juk...@mm...>
>>
>>>   Hi,
>>> I have here Geoserver 2.0.1 and Oracle 10g (10.2.0.4.0) and it seems
>>> to be enough in our environment to grant SELECT for the data.  I have
>>> not tried with 2.0.2.
>>>
>>>
>>> -Jukka Rahkonen-
>>>
>>>  ------------------------------
>>> Primoz Kogovsek  wrote:
>>>
>>> Hello!
>>>
>>> I have problem with grants in oracle database for publishing layer.
>>>
>>> I have connected oracle database with geoserver. I added oracel data
>>> store to geoserver stores and I can see resources contained in the
>>> store. But the problem is when I try to publish a layer from this data
>>> store. When I publish data with user that has dba rights it works
>>> fine. But when I try to publish data with user we have specially for
>>> publishing data I get error. ... at
>>>
>>> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
>>> :857)
>>>  at
>>> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
>>> ss(Http11Protocol.java:588)
>>>  at
>>> org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:48
>>> 9)  at java.lang.Thread.run(Thread.java:619) Caused by:
>>> java.io.IOException: Error looking up primary key  at
>>>
> org.geotools.jdbc.JDBCDataStore.getPrimaryKey(JDBCDataStore.java:825)
>>>  at
> org.geotools.jdbc.JDBCFeatureSource.<init>(JDBCFeatureSource.java:76)
>>>  at
>>> org.geotools.jdbc.JDBCDataStore.createFeatureSource(JDBCDataStore.jav
>>> a:709)
>>>  at
>>> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentData
>>> Store.java:383)
>>>  at
>>> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentData
>>> Store.java:348)
>>>  at
>>> org.geotools.data.store.ContentDataStore.getFeatureSource(ContentData
>>> Store.java:647)
>>>  at
>>> org.geoserver.catalog.CatalogBuilder.buildFeatureType(CatalogBuilder.
>>> java:324)
>>>  at
>>> org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage
>>> .java:198)
>>>  ... 70 more
>>> Caused by: java.sql.SQLException: ORA-00942: table or view does not
>>> exist
>>>
>>>  at
>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
>>> :111)  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
>>>  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
>>>  at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
>>> ....... Publishing user has SELECT grant over the data table and
>>> SELECT grant over MDSYS SDO* tables. I think I must grant some more
>>> tables for this user but I have no idea which?
>>>
>>> Geoserver 2.0.2
>>> Oracle 11g Rac (JNDI)
>>> Apache Tomcat Version 6.0.29
>>> Windows Server 2008 32-bit
>>>
>>> Thanks and regards
>>> Primoz
>>>
>>>
>>>
>>> --
>>> Primoz Kogovsek
>>>
>>> Geografske informacijske resitve
>>> Primoz Kogovsek s.p.
>>> Koroska cesta 16
>>> 4000 Kranj
>>>
>>>
>>>
>>> -------------------------------------------------------------------



___________________________________________________________________________

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