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 <walter.stov...@byers.com> 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
> GeoTools-Devel@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geotools-devel
>
>
--
--
Jody Garnett
------------------------------------------------------------------------------
_______________________________________________
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel
