Thank You Andrea. Your note on the original sponsor would seem to imply that
in absence of SET SESSION AUTHORIZATION support in Oracle, that the sponsor
called a stored procedure to accomplish switching the connection from the proxy
to the real user. That would appear to be an easy solution, where the Session
Start-Up and Session Close-Up SQL could call procedures to switch the
connection and then return it to the proxy when done.
But I can’t find any pl/sql equivalent to the Oracle JDBC Driver’s
openProxySession. Do I misunderstand your note?
Thanks, Walter
From: andrea.a...@gmail.com [mailto:andrea.a...@gmail.com] On Behalf Of Andrea
Aime
Sent: Wednesday, June 15, 2016 6:39 AM
To: Walter Stovall <walter.stov...@byers.com>
Cc: geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] Managing Oracle connections to different schemas
of the same database instance can't be done with the current geoserver
On Wed, Jun 15, 2016 at 12:24 PM, Walter Stovall
<walter.stov...@byers.com<mailto:walter.stov...@byers.com>> wrote:
My geoserver application needs to connect to potentially hundreds of different
Oracle schemas. There is a workspace associated with each schema. My goal is
to have a single connection pool that is shared by all of these workspaces.
Andrea shared this URL which tends to point me in the right direction.
http://docs.geoserver.org/latest/en/user/data/database/sqlsession.html#data-sqlsession
The above solution allows for one DataStore to be shared by all of my
workspaces, which is exactly what I need. But this won’t work with the Oracle
database. Unfortunately Oracle does not support the SET SESSION AUTHORIZATION
sql and apparently has no SQL-based equivalent.
Hi Walter,
the funny thing is, the impersonation was implemented exactly for Oracle, and
the example for postgresql was added later for documentation and generality
purposes.
The sponsor had a Oracle package (set of stored produces I believe?) that
allowed to setup the impersonation by SQL,
but I don't think it was anything standard.
However, Oracle does have exactly what I need. The problem is that it can’t be
accomplished by executing a SQL statement. Instead it requires a proprietary
call to the JDBC driver.
With Oracle, ‘impersonating’ a user is accomplished by creating a proxy
connection. The basics of this are at
http://docs.oracle.com/cd/B28359_01/java.111/b31224/proxya.htm#BABEJEIA. The
idea is that basically you setup the connection pool based on a database user
with minimal privilege and no meaningful default schema. Then you can borrow
one of the connections in the pool and switch it so it now behaves as a
connection to the user account you want to behave-as.
Doing the equivalent of SET SESSION AUTHORIZATION is accomplished with a call
to the Oracle driver oracle.jdbc.OracleConnection.openProxySession. And then
when the connection is returned to the pool, an overload of the
OracleConnection.close() method closes the proxy session while keeping the
connection otherwise open.
I’m looking for any comments you might have on how to extend the geoserver code
to support this. Rather than hack the code for my own purposes I’d like to
hope I might contribute a solution that gets rolled into the core product.
Thanks in advance for any thoughts on how to implement this in geoserver.
References to specific geoserver/geotools interfaces are appreciated!
I don't believe we have anything ready to be used, and guess some custom
changes to the Oracle store down in GeoTools is pretty much the
only approach. A new store parameter referring to the env variable that's going
to be used to pass down the user is likely a good approach, I guess you can use
The code in question is in these two modules:
https://github.com/geotools/geotools/tree/master/modules/library/jdbc
https://github.com/geotools/geotools/tree/master/modules/plugin/jdbc/jdbc-oracle
You'll probably need to roll a new method in the SQLDialect interface to allow
creating a new connection from an existing one, too.
Cheers
Andrea
--
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i
file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo
è consentito esclusivamente al destinatario del messaggio, per le finalità
indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne
il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di
procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro
sistema. Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse,
costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for the
attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act (Legislative
Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in
accord with its purpose, any disclosure, reproduction, copying, distribution,
or either dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the intended
recipient, please contact immediately the sender by telephone, fax or e-mail
and delete the information in this message that has been received in error. The
sender does not give any warranty or accept liability as the content, accuracy
or completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.
-------------------------------------------------------
------------------------------------------------------------------------------
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://pubads.g.doubleclick.net/gampad/clk?id=1444514421&iu=/41014381
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
