From: Andrea A. <and...@ge...> - 2012-01-17 14:13:02
|
Hi, following up the work about the startup/teardown sql startup scripts we noticed that there might be issues accessing the geometry metadata in said scenario. In particular, in that security setup some tables might not be accessible by the pool user at all, even if the database metadata about them is. That is, the pool user can describe the structure of said tables, but is not able to access them. In most databases this would not be a problem, but Oracle has, as usual, some complication: the geometry metadata view in MD_SYS, necessary to figure out the srid and geometry type of a geometry column, are dynamic, the USER_* ones show only the tables that one owns, the ALL_* ones only tables that one can access. Not being able to know the srid of the geometry column is serious trouble, since using -1 instead of the actual value will make all spatial queries fail. For this case I would like to propose a lookup table similar in spirit to the primary key lookup table, or the geometry_columns table/view in postgis. The table would be called GT_GEOMETRY_COLUMNS, and would have the following structure: - table_schema (string, nullable): schema table - table_name (string): table name - geometry (string): geometry column name - coord_dimensions (int, nullable): coordinate dimensions, 2 or 3 dimensions (not used at the moment, but might be in the future) - srid (int): the geometry native srid - geometry_type (string, nullable): the geometry type, chosen between POINT, MULTIPOINT, POLYGON, MULTIPOLYGON, LINESTRING, MULTILINESTRING, GEOMETRY, GEOMETRY_COLLECTION The table is evidently inspired by the postgis one... so much that I'm tempted to just call it geometry_columns. Actually, this would have two precedents: - the mysql store looks for a similar table, an exact clone of the postgis one, to get the metadata, since they are nowere to be found - OGR does the same for the sql server data source, which has the same problem (and I would actually be tempted to do the same in the GeoTools one) So... what do you think? geometry_columns for all? :-) Cheers Andrea -- ------------------------------------------------------- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 mob: +39 339 8844549 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------- |
From: Justin D. <jde...@op...> - 2012-01-17 14:47:27
|
I can see adding the table to databases that have no notion of a geometry metadata table but adding when one already exists seems like kind of a workaround for this specific problem. But I could be totally wrong and perhaps this is a common issue... would have to delegate to actual oracle users on that one. To help me understand, in your case will the existing metadata tables be kept in sync at all? Or totally ignored? As for a name i prefer geometry_columns over gt_geometry_columns. -Justin On Tue, Jan 17, 2012 at 7:12 AM, Andrea Aime <and...@ge...>wrote: > Hi, > following up the work about the startup/teardown sql startup scripts we > noticed that there might be issues accessing the geometry metadata > in said scenario. > > In particular, in that security setup some tables might not be accessible > by the pool user at all, even if the database metadata about them is. > That is, the pool user can describe the structure of said tables, but > is not able to access them. > > In most databases this would not be a problem, but Oracle has, as usual, > some complication: the geometry metadata view in MD_SYS, necessary > to figure out the srid and geometry type of a geometry column, are dynamic, > the USER_* ones show only the tables that one owns, the ALL_* ones only > tables that one can access. > > Not being able to know the srid of the geometry column is serious trouble, > since using -1 instead of the actual value will make all spatial queries > fail. > > For this case I would like to propose a lookup table similar in spirit > to the primary key lookup table, or the geometry_columns table/view > in postgis. > The table would be called GT_GEOMETRY_COLUMNS, and would have the following > structure: > - table_schema (string, nullable): schema table > - table_name (string): table name > - geometry (string): geometry column name > - coord_dimensions (int, nullable): coordinate dimensions, 2 or 3 > dimensions > (not used at the moment, but might be in the future) > - srid (int): the geometry native srid > - geometry_type (string, nullable): the geometry type, chosen between > POINT, MULTIPOINT, POLYGON, MULTIPOLYGON, LINESTRING, > MULTILINESTRING, GEOMETRY, GEOMETRY_COLLECTION > > The table is evidently inspired by the postgis one... so much that > I'm tempted to just call it geometry_columns. > > Actually, this would have two precedents: > - the mysql store looks for a similar table, an exact clone of the postgis > one, to get the metadata, since they are nowere to be found > - OGR does the same for the sql server data source, which has the > same problem (and I would actually be tempted to do the same in > the GeoTools one) > > So... what do you think? geometry_columns for all? :-) > > Cheers > Andrea > > -- > ------------------------------------------------------- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > mob: +39 339 8844549 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > ------------------------------------------------------- > > > ------------------------------------------------------------------------------ > Keep Your Developer Skills Current with LearnDevNow! > The most comprehensive online learning library for Microsoft developers > is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, > Metro Style Apps, more. Free future releases when you subscribe now! > http://p.sf.net/sfu/learndevnow-d2d > _______________________________________________ > Geotools-devel mailing list > Geo...@li... > https://lists.sourceforge.net/lists/listinfo/geotools-devel > > -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. |
From: Andrea A. <and...@ge...> - 2012-01-17 16:23:10
|
On Tue, Jan 17, 2012 at 3:47 PM, Justin Deoliveira <jde...@op...>wrote: > I can see adding the table to databases that have no notion of a geometry > metadata table but adding when one already exists seems like kind of a > workaround for this specific problem. But I could be totally wrong and > perhaps this is a common issue... would have to delegate to actual oracle > users on that one. > > It's specific to the use case, but the use case is general (it's not customer specific). Let me elaborate again: - the MDSYS views we are using are populated dynamically based on the tables the current user can access, you can only see what you own/can access - we are doing impersonation so that the user can phisically access only the data he's allowed to by the database own security - the pool user cannot access anything under a high security setup, only dictionary tables, as a result during GeoServer startup, while no user is setup, we decide the srid of the various geometries is -1 - then someone tries to use the data, boom, spatial filters fail Long story short, it's not customer specific, it's specific to using impersonation and database level security, which is an interesting use case in some enterprises that are very database focused. This is pretty much the same as the sql scripts to do impersonation, they are general enough that anybody can use them, but only these very db centric setups would have any use for it I guess. It's very high end, and uncommon for us, ugly to look at, but I assure you these folks look at us the same way for our insistence on not being able to work with stuff specific to their db of choice and the set of library functions they built into their install ("the only thing that is reusable is what is implemented as a procedure/check in the db" mindset, which is understandable if you have tens of different products implemented in different languages accessing your central database). It's a matter of deciding wheter we want to open up enough facilities to have this particular user community in, or not (as far as I can see it's a little circle, but interesting from different points of views, in particular attention to security, high data volumes, and ability to sponsor further improvements). > To help me understand, in your case will the existing metadata tables be > kept in sync at all? Or totally ignored? > First check the geometry_columns table if available, then check the mdsys views (pretty much what we do for primary keys as well). Cheers Andrea -- ------------------------------------------------------- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 mob: +39 339 8844549 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------- |
From: Justin D. <jde...@op...> - 2012-01-17 17:06:11
|
Hey Andrea, Thanks for the extended explanation. Makes sense. +1 on the approach, I think having the geometry_columns table for an override even when there is a geometry metadata table available makes sense. -Justin On Tue, Jan 17, 2012 at 9:22 AM, Andrea Aime <and...@ge...>wrote: > On Tue, Jan 17, 2012 at 3:47 PM, Justin Deoliveira <jde...@op...>wrote: > >> I can see adding the table to databases that have no notion of a geometry >> metadata table but adding when one already exists seems like kind of a >> workaround for this specific problem. But I could be totally wrong and >> perhaps this is a common issue... would have to delegate to actual oracle >> users on that one. >> >> > It's specific to the use case, but the use case is general (it's not > customer specific). > Let me elaborate again: > - the MDSYS views we are using are populated dynamically based on the > tables the current user can access, > you can only see what you own/can access > - we are doing impersonation so that the user can phisically access only > the data > he's allowed to by the database own security > - the pool user cannot access anything under a high security setup, only > dictionary tables, > as a result during GeoServer startup, while no user is setup, we decide > the srid of > the various geometries is -1 > - then someone tries to use the data, boom, spatial filters fail > > Long story short, it's not customer specific, it's specific to using > impersonation > and database level security, which is an interesting use case in some > enterprises > that are very database focused. > This is pretty much the same as the sql scripts to do impersonation, they > are general > enough that anybody can use them, but only these very db centric setups > would > have any use for it I guess. > > It's very high end, and uncommon for us, ugly to look at, but I assure you > these > folks look at us the same way for our insistence on not being able to work > with stuff specific to their db of choice and the set of library functions > they > built into their install ("the only thing that is reusable is what is > implemented > as a procedure/check in the db" mindset, which is understandable if you > have tens of different products implemented in different languages > accessing > your central database). > > It's a matter of deciding wheter we want to open up enough facilities to > have > this particular user community in, or not (as far as I can see it's a > little circle, > but interesting from different points of views, in particular attention to > security, > high data volumes, and ability to sponsor further improvements). > > >> To help me understand, in your case will the existing metadata tables be >> kept in sync at all? Or totally ignored? >> > > First check the geometry_columns table if available, then check the mdsys > views (pretty > much what we do for primary keys as well). > > Cheers > Andrea > > -- > ------------------------------------------------------- > Ing. Andrea Aime > GeoSolutions S.A.S. > Tech lead > > Via Poggio alle Viti 1187 > 55054 Massarosa (LU) > Italy > > phone: +39 0584 962313 > fax: +39 0584 962313 > mob: +39 339 8844549 > > http://www.geo-solutions.it > http://geo-solutions.blogspot.com/ > http://www.youtube.com/user/GeoSolutionsIT > http://www.linkedin.com/in/andreaaime > http://twitter.com/geowolf > > ------------------------------------------------------- > -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. |