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
