Thanks Jody. I had not considered doing this with the Transaction object. At this point I have the approach I outlined implemented and working. I do see at least a slight advantage to what I’ve implemented in the sense that it works regardless of whether a Transaction is in scope. Since the impersonation may be important in various contexts that are not under my direct control (such as WFS/WMS) that would seem to be possibly relevant (but I’m stretching here because in all likelihood the impersonation is never important except when creating new Oracle SDO tables where I found this problem).
I admit to being biased since what I’ve done is working and has a pretty simple copy/paste impact in only a few source files. I could certainly see how doing it with a Transaction hint would have advantages if the existing OracleDialect class were somehow involved in initiating a transaction. Then the impact would be limited to that one class. But neither OracleDialect nor its abstract base, SQLDialect currently import Transaction. So it would seem that when a transaction starts, there would still have to be GeoTools changes outside the scope of OracleDialect necessary for getting OracleDialect participating in the transaction. Do I see that accurately? So my take would be that this problem can’t be isolated to the OracleDialect class. Since my change is limited to a couple connection-map entries and applies whenever a connection is obtained from JDBCDataStore and impacts minimal code outside of OracleDialect, and the Transaction approach limits impersonation to that specific scope and still requires changes outside of OracleDialect, I lean towards what I’ve done instead of doing it thru Transaction. What are your thoughts? Thank you for your consideration. Walter Stovall Byers Engineering Company. From: Jody Garnett [mailto:[email protected]] Sent: Saturday, August 20, 2016 12:37 AM To: Walter Stovall <[email protected]> Cc: [email protected] Subject: Re: [Geotools-devel] I would like to contribute a bug fix to GeoTools OracleDialect when using connections from a jndi pool Thanks for the well written explanation. I have another way you could consider doing impersonate user. The Transaction object acts like a session for the datastore api, and has the ability to store extra hints for use by the SQL Dialect if appropriate. You could store the user here as a hint for the datastore and have the sql dialect pick it up if provided? -- Jody Garnett On 13 August 2016 at 13:30, Walter Stovall <[email protected]<mailto:[email protected]>> wrote: I’ve encountered a problem in GeoTools attempting to create new feature types in an OracleNGDataStore. The problem occurs when the username for the jdbc connection does not have a default schema that matches the schema name where the feature type is being created. GeoTools generally allows me to operate on a specific database schema with the “schema” connection parameter map entry. But if this schema does not match the Oracle user’s default schema, this will not work when you’re creating new Oracle SDO database tables because the OracleDialect class fails to properly insert metadata into user_sdo_geom_metadata. When an insert is done to user_sdo_geom_metadata this populates the mdsys.all_sdo_geom_metadata table and the schema name recorded there by Oracle is based on the default schema for the user. If that is not the schema where the feature table is being created this results in improper behavior and a failure to create the spatial index. For example this is a snippet of some code I have that imports a shape file and creates a new Oracle table to hold the content. shp = new ShapefileDataStore(new File(path).toURI().toURL()); shp.setCharset(Charset.forName("ISO-8859-7")); destination.createSchema(shp.getSchema()); In this case the destination is a JDBCDataStore (OracleNGDataStore) that was created with map entries including jndiReferenceName and schema. The jndi connections are all made in terms of an ‘admin’ user and the schema name is that of my GeoServer workspace. The above createSchema successfully creates the Oracle SDO table, then inserts into user_sdo_geom_metadata (for the wrong Oracle user) and then fails to create a spatial index (because the expected all_sdo_geom_metadata row was not found). This is a significant problem for me because I have a GeoServer service running against hundreds of workspaces that are based on OracleNGDataStore. In order to effectively manage database connections I’m using a jndi connection pool that obtains connections under a specific user account whose schema name of course never matches that of the workspace. On the surface it would seem possible to insert directly into all_sdo_geom_metadata to properly populate the schema name there. While this was possible with some earlier Oracle SDO releases it is not possible anymore because Oracle disallows direct inserts into that table. So to solve this problem I have modified my GeoTools sourcecode in a way that I hope the community will find acceptable (I got most of this from my read of an earlier thread with Andrea a few months back but didn’t need these changes until now when I want my DataStore to create new features and it can’t). I’ve addressed this problem by changing the SQLDialect to support an impersonateUser() method. This method allows an existing connection to behave as though created by the specified user. The default implementation throws an exception that the operation is not supported in that dialect. The OracleDialect implements this method in terms of Oracle proxy user methods http://docs.oracle.com/cd/B28359_01/java.111/b31224/proxya.htm. Then to access the OracleDialect.impersonateUser() method I have a small change to JDBCDataStoreFactory that recognizes new parameter map entries for user impersonation. The full scope of my sourcecode changes include: 1) Modified JDBCDataStoreFactory. I added code to recognize new IMPERSONATE_USER and IMPERSONATE_PW map entries for the user to be impersonated on the connection and code to setup the connection for impersonation when these entries are present. 2) New class, ImpersonateUserListener. This is a ConnectionLifecycleListener to turn on and turn off user impersonation. This listener is put on connections if the above impersonation map entries are found. 3) SQLDialect. Modified this base class with a new impersonateUser() method. The default implementation throws an exception that says the dialect does not support this. This would be the behavior if you put impersonation entries into your map for a non-Oracle database. 4) OracleDialect. Modified the class to support impersonation by using proprietary Oracle API mentioned at above link. It would be possible for other SQLDialect classes to override impersonateUser() for that database and maybe implement it in terms of SET SESSION AUTHORIZATION SQL (which Oracle unfortunately does not support) but I have not done this in my coding as I’m not well equipped to test that. Suffice to say that the new map entries only work for an Oracle database at this time but cause no problem when not used. At this point I have not modified junit tests to expose the bug or confirm the fix – I’ve only confirmed that my application works by successfully creating new Oracle SDO tables after having seen to the user impersonation issue. fwiw I’m also using the new impersonation map entries on my GeoServer DataStores and experience no problems. I’m hoping to get some buy-in on this as a reasonable GeoTools change and any comments on making a proper pull request for it and what I would do to the junit tests. Does this need to be an online test that accesses a real Oracle database or can it be a test that confirms basic flow of control? If it needs to be online, it must include setting up another Oracle user and granting the user privilege to connect thru another user’s account. Any comments that guide me in the right direction there are appreciated. Thanks for your consideration – Walter Stovall ------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports. http://sdm.link/zohodev2dev _______________________________________________ GeoTools-Devel mailing list [email protected]<mailto:[email protected]> https://lists.sourceforge.net/lists/listinfo/geotools-devel -- -- Jody Garnett
------------------------------------------------------------------------------
_______________________________________________ GeoTools-Devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel
