GeoTools
  1. GeoTools
  2. GEOT-3256

Oracle NG datastore can not deal with different schema owners

    Details

    • Type: Improvement Improvement
    • Status: Open Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: jdbc-oracle plugin
    • Labels:
      None

      Description

      Currently the Oracle NG datastore can not deal correctly with the following situation:

      X schema owners, which are all accessible by a read-only Oracle user. Using this read-only user I want to access tables from all schema owners in one store.

      So the store needs to keep track of the schema owner associated with the tables when doing queries.

        Activity

        Hide
        Paul Joyce added a comment -
        Other GIS software solves this problem by presenting every Oracle table/view as "schema.name". Perhaps GeoTools could do the same if no schema was specified in the connection. If a schema was specified, it could either continue to present just names, or show "specified_schema.name" for all the tables/views (for consistency with the no-schema situation).

        It is not possible to "keep track" of the schema internally, as you may have the same name in multiple schemas. The only was to keep a unique reference is to prefix it with the schema.

        From my testing in GeoServer, if there are two Oracle tables with the same name in different schemas, GeoServer only shows one of them.
        Show
        Paul Joyce added a comment - Other GIS software solves this problem by presenting every Oracle table/view as "schema.name". Perhaps GeoTools could do the same if no schema was specified in the connection. If a schema was specified, it could either continue to present just names, or show "specified_schema.name" for all the tables/views (for consistency with the no-schema situation). It is not possible to "keep track" of the schema internally, as you may have the same name in multiple schemas. The only was to keep a unique reference is to prefix it with the schema. From my testing in GeoServer, if there are two Oracle tables with the same name in different schemas, GeoServer only shows one of them.
        Hide
        Andrea Aime added a comment -
        Using the . is problematic, that would not make for a valid WFS layer name. Using _ would be valid though (we need some way to map that to schema and table name, though, however I guess any separator we choose would present that problem as people can include pretty much anything in table names when escaping the names with double quotes)
        Show
        Andrea Aime added a comment - Using the . is problematic, that would not make for a valid WFS layer name. Using _ would be valid though (we need some way to map that to schema and table name, though, however I guess any separator we choose would present that problem as people can include pretty much anything in table names when escaping the names with double quotes)
        Hide
        Bernhard Kiselka added a comment -
        Can't reproduce this issue: creating a layer with a non-schema user works for me in geoserver 2.2.3!
        It works both with a write-role (granted rights for select, insert, update and delete on certain objects) as well as a read-role (granted rights for select on certain objects)

        But I know there was a problem getting the mdsys-information correctly a long time ago.
        Show
        Bernhard Kiselka added a comment - Can't reproduce this issue: creating a layer with a non-schema user works for me in geoserver 2.2.3! It works both with a write-role (granted rights for select, insert, update and delete on certain objects) as well as a read-role (granted rights for select on certain objects) But I know there was a problem getting the mdsys-information correctly a long time ago.
        Hide
        Andrea Aime added a comment -
        Bernard, the issue happens if you have multiple tables with the same name in different schemas
        Show
        Andrea Aime added a comment - Bernard, the issue happens if you have multiple tables with the same name in different schemas
        Hide
        Bernhard Kiselka added a comment -
        I have a store for each schema. I.e. the schema name is defined in the store. That is why it worked.
        Show
        Bernhard Kiselka added a comment - I have a store for each schema. I.e. the schema name is defined in the store. That is why it worked.
        Hide
        Andrea Aime added a comment -
        Yep, this report is about having a single store without the schema defined, so able to access all schemas (and failing to do so when multiple tables in different schemas have the same name)
        Show
        Andrea Aime added a comment - Yep, this report is about having a single store without the schema defined, so able to access all schemas (and failing to do so when multiple tables in different schemas have the same name)

          People

          • Assignee:
            Andrea Aime
            Reporter:
            Bart van den Eijnden
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: