Quantcast

[gdal-dev] Limiting Oracle layers to the tables of a given schema

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[gdal-dev] Limiting Oracle layers to the tables of a given schema

Vautour, André (INT)

Hi all,

 

I started playing with the Oracle Spatial (OCI) vector driver and was surprised that the default behaviour is to look for all the tables listed in ALL_SDO_GEOM_METADATA.

 

I would have expected it to use all the tables in the schema (user) that was provided, so, essentially, to query USER_SDO_GEOM_METADATA. I think it would make sense to support only listing the tables in a given schema.

 

Assuming that the default behaviour is to remain unchanged, I am considering a couple of options:

1.       Add something like a LIST_ONLY_USER_TABLES dataset open option, which would query USER_SDO_GEOM_METADATA instead of ALL_SDO_GEOM_METADATA.

2.       Add a SCHEMA dataset open option which would query ALL_SDO_GEOM_METADATA WHERE OWNER = '<value_of_option>'

3.       Add wildcard support to the tables list, so that a user can specify something like <a_schema>.*,<another_schema>.*

 

Also, it might be important to note that DeleteLayer and ValidateLayer only currently work off of USER_SDO_GEOM_METADATA.

 

Thoughts?

André

 


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limiting Oracle layers to the tables of a given schema

Ivan Lucena-4
According to the doc:
"If the list of tables is not provided, then all tables appearing in ALL_SDO_GEOM_METADATA will be treated by OGR as layers with the table names as the layer names. Non-spatial tables or spatial tables not listed in the ALL_SDO_GEOM_METADATA table are not accessible unless explicitly listed in the datasource name. Even in databases where all desired layers are in the ALL_SDO_GEOM_METADATA table, it may be desirable to list only the tables to be used as this can substantially reduce initialization time in databases with many tables."

You might avoiding that issue by entering an empty table list when loading, like in "OCI:usr/pwd@db:". Please note the ":" at the end.

On Apr 13, 2017, at 2:54 PM, Vautour, André (INT) <[hidden email]> wrote:

Hi all,

 

I started playing with the Oracle Spatial (OCI) vector driver and was surprised that the default behaviour is to look for all the tables listed in ALL_SDO_GEOM_METADATA.

 

I would have expected it to use all the tables in the schema (user) that was provided, so, essentially, to query USER_SDO_GEOM_METADATA. I think it would make sense to support only listing the tables in a given schema.

 

Assuming that the default behaviour is to remain unchanged, I am considering a couple of options:

1.       Add something like a LIST_ONLY_USER_TABLES dataset open option, which would query USER_SDO_GEOM_METADATA instead of ALL_SDO_GEOM_METADATA.

2.       Add a SCHEMA dataset open option which would query ALL_SDO_GEOM_METADATA WHERE OWNER = '<value_of_option>'

3.       Add wildcard support to the tables list, so that a user can specify something like <a_schema>.*,<another_schema>.*

 

Also, it might be important to note that DeleteLayer and ValidateLayer only currently work off of USER_SDO_GEOM_METADATA.

 

Thoughts?

André

 

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limiting Oracle layers to the tables of a given schema

Even Rouault-2
In reply to this post by Vautour, André (INT)

On jeudi 13 avril 2017 18:52:58 CEST Vautour, André (INT) wrote:

> Hi all,

>

> I started playing with the Oracle Spatial (OCI) vector driver and was

> surprised that the default behaviour is to look for all the tables listed

> in ALL_SDO_GEOM_METADATA.

>

> I would have expected it to use all the tables in the schema (user) that was

> provided, so, essentially, to query USER_SDO_GEOM_METADATA. I think it

> would make sense to support only listing the tables in a given schema.

>

> Assuming that the default behaviour is to remain unchanged, I am considering

> a couple of options:

>

> 1. Add something like a LIST_ONLY_USER_TABLES dataset open option,

> which would query USER_SDO_GEOM_METADATA instead of ALL_SDO_GEOM_METADATA.

>

> 2. Add a SCHEMA dataset open option which would query

> ALL_SDO_GEOM_METADATA WHERE OWNER = '<value_of_option>'

>

> 3. Add wildcard support to the tables list, so that a user can specify

> something like <a_schema>.*,<another_schema>.*

 

André,

 

In the PostgreSQL driver, we have

 

<Option name="SCHEMAS" type="string" description="Restricted sets of schemas to explore (comma separated)" />

 

Sothat would be something close to your 3.

 

Even

 

--

Spatialys - Geospatial professional services

http://www.spatialys.com


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [gdal-dev] Limiting Oracle layers to the tables of a given schema

jratike80
In reply to this post by Vautour, André (INT)
Vautour, André (INT) wrote
Hi all,

I started playing with the Oracle Spatial (OCI) vector driver and was surprised that the default behaviour is to look for all the tables listed in ALL_SDO_GEOM_METADATA.

I would have expected it to use all the tables in the schema (user) that was provided, so, essentially, to query USER_SDO_GEOM_METADATA. I think it would make sense to support only listing the tables in a given schema.
Hi,

There are for sure different opinions but I consider that it does make more sense to use ALL_SDO_GEOM_METADATA. In our environment the database admins create tables and grant minimal rights for the normal user accounts so USER_SDO_GEOM_METADATA is usually empty for our GIS users.

A reference about the difference taken from http://gerardnico.com/wiki/oracle_spatial/metadata

    USER_SDO_GEOM_METADATA contains metadata information for all spatial tables owned by the user (schema). This is the only view that you can update, and it is the one in which Spatial users must insert metadata related to spatial tables.
    ALL_SDO_GEOM_METADATA contains metadata information for all spatial tables on which the user has SELECT permission.


If user has select rights for a table then why not to make it visible also through GDAL? If GDAL sees some table that you think it should not, remove the SELECT permission from the user and it disappears.

-Jukka Rahkonen-
Loading...