[Geoserver-devel] Managing Oracle connections to different schemas of the same database instance can't be done with the current geoserver

[Walter_Stovall]

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.

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!

Walter Stovall

Byers Engineering Company

···

From: Walter Stovall
Sent: Tuesday, June 14, 2016 5:11 AM
To: Andrea Aime <andrea.aime@…1268…>
Cc: Rob L <Robert.Langford@…4471…>; geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Can geoserver 2.8 manage number of database connections to a given Oracle instance?

My situation is not so clean as that in that my users don’t literally have database accounts. But that doesn’t mean I can’t learn what the database user/schema is from the authenticated user name. I may be able to work with the framework you point to for impersonation and get a solution that works for me.

Thanks for the suggestion!

Walter

From: andrea.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Monday, June 13, 2016 10:08 AM
To: Walter Stovall <walter.stovall@…4309…>
Cc: Rob L <Robert.Langford@…4471…>; geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Can geoserver 2.8 manage number of database connections to a given Oracle instance?

Hi Walter,

I don’t think I’ve ever heard of any connection pooling library able to limit the total amount

of connections across several pools.

A Google search for “limit number of jdbc connections across multiple connection pools” also comes out empty as far as I can tell.

If in your use case there is a correspondence between a GeoServer user and a database user, maybe you can use

a single connection pool, and impersonation to change the current user:

http://docs.geoserver.org/latest/en/user/data/database/sqlsession.html#data-sqlsession

Just thinking out loud here

Cheers

Andrea

On Mon, Jun 13, 2016 at 3:45 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Thanks but I don’t yet see how this would solve my problem? When I look at the tutorial it shows setting up a tomcat pool that connects to oracle under a specific user name and password.

But in my case I want to limit the number of connections to the Oracle instance itself independent of the user id and password. For example let’s say I have 100 geoserver workspaces, each of which manage a pool of connections to a specific Oracle schema (with all such schemas in one oracle instance). Each such pool would login to Oracle under a different user and password. I might want each pool to have as many as 50 active connections, and yet I want to see that regardless, no more than 200 total connections (in all the pools combined) will be created.

In the above scenario of 100 workspaces with 50 connections each you might normally see as many as 5,000 connections. I’m looking for something that will see that regardless, the total connections are kept below a limit of say 200. So three particular pools might create 50 connections and if a 4th pool tries to create a new connection (to this specific oracle instance regardless of schema) that connection will block waiting for the limit to go below 200.

Does JNDI create a way of doing that?

Thanks, Walter

-----Original Message-----
From: Rob L [mailto:Robert.Langford@…4471…]
Sent: Monday, June 13, 2016 8:45 AM
To: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Can geoserver 2.8 manage number of database connections to a given Oracle instance?

Walter,

Have a look at using a JNDI connection.

http://docs.geoserver.org/maintain/en/user/tutorials/tomcat-jndi/tomcat-jndi.html#tomcat-jndi
<http://docs.geoserver.org/maintain/en/user/tutorials/tomcat-jndi/tomcat-jndi.html#tomcat-jndi>

–
View this message in context: http://osgeo-org.1560.x6.nabble.com/Can-geoserver-2-8-manage-number-of-database-connections-to-a-given-Oracle-instance-tp5271268p5271381.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

---

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. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e

---

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

---

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. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e

---

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

==

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.

---

[Andrea_Aime4]

On Wed, Jun 15, 2016 at 12:24 PM, Walter Stovall <walter.stovall@anonymised.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.

-------------------------------------------------------

[Walter_Stovall]

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

···

On Wed, Jun 15, 2016 at 12:24 PM, Walter Stovall <walter.stovall@…4309…> 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.

---

[Walter_Stovall]

Andrea, based on your note about changing SQLDialect to support starting and ending an Oracle proxy session I’m looking for the best place to hook into the geotools code for invoking this.

Looking at the JDBCDataStore.createDataStore() method, I see where it creates a ConnectionLifecycleListener based on Map entries for SQL_ON_BORROW/SQL_ON_CONNECT. It would seem that at this time, I could check for a Map entry that requests a proxy session. When found, I create (potentially another) ConnectionLifecycleListener whose job it is to start/end the proxy session. As you say, actually starting the proxy session would be implemented inside the SQLDialect and in my case, the OracleSQLDialect.

I could imagine throwing an exception for now from any SQLDialect other than OracleSQLDialect that gets asked to do this proxy session thing.

Am I on the right track?

Thanks, Walter

···

From: Walter Stovall
Sent: Wednesday, June 15, 2016 8:12 AM
To: Andrea Aime <andrea.aime@…1268…>
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

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.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Wednesday, June 15, 2016 6:39 AM
To: Walter Stovall <walter.stovall@…4309…>
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.stovall@…4309…> 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.

---

[Andrea_Aime4]

On Wed, Jun 15, 2016 at 2:11 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

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?

No, you did not. The sponsor had their own impersonation package, they
built it themselves, I don't know how, it was not standard
and indeed I could not find a similar functionality in a out of the box
Oracle

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.

-------------------------------------------------------

[Andrea_Aime4]

On Wed, Jun 15, 2016 at 5:28 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

Andrea, based on your note about changing SQLDialect to support starting
and ending an Oracle proxy session I’m looking for the best place to hook
into the geotools code for invoking this.

Looking at the JDBCDataStore.createDataStore() method, I see where it
creates a ConnectionLifecycleListener based on Map entries for
SQL_ON_BORROW/SQL_ON_CONNECT. It would seem that at this time, I could
check for a Map entry that requests a proxy session. When found, I create
(potentially another) ConnectionLifecycleListener whose job it is to
start/end the proxy session. As you say, actually starting the proxy
session would be implemented inside the SQLDialect and in my case, the
OracleSQLDialect.

Seems like a good approach to me. I'd extend the OracleDataStoreFactory
with one or more extra parameter that enables proxy connection, and that
are given
the name of the env variable(s) you need to pass down, and if those are
setup, then add your own custom listener to the store:
https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleNGDataStoreFactory.java

Maybe following this approach you don't even need to modify the SQLDialect
class

Ah, you'll need to unwrap the connection from the pool via the Unwrappers,
the Oracle store contains code already calling them (the pooled connection
wrap the Oracle one, but you need the native one). Given there are many
connection pools we have an extension point for that.
See:
https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java#L617

Cheers
Andrea

PS: what you add to the main data store factory should also be added to the
JNDI one (and probably to the OCI one, although I'm not sure about that
one).

--

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.

-------------------------------------------------------

[Walter_Stovall]

Thanks. Looking to simplify this yet more. The solution below would still require me to modify the datastore setup page in geoserver so the page would let me request a proxy session be created. And that doesn’t seem like a great thing to add to the core product because it’s only relevant to Oracle – i.e. Geoserver already has support for proxy sessions as long as the database supports the SET SESSION AUTHORIZATION statement, which Oracle does not.

How ‘bad’ would be if I modified the geotools SessionCommandsListener to specifically check for SET SESSION AUTHORIZATION being the statement getting executed? When found, this is a directly executed pass-thru for non-Oracle dialect but for Oracle this uses their proprietary API to do the equivalent.

Then geoserver stays totally like it is.

Thoughts?

Walter

···

On Wed, Jun 15, 2016 at 5:28 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Andrea, based on your note about changing SQLDialect to support starting and ending an Oracle proxy session I’m looking for the best place to hook into the geotools code for invoking this.

Looking at the JDBCDataStore.createDataStore() method, I see where it creates a ConnectionLifecycleListener based on Map entries for SQL_ON_BORROW/SQL_ON_CONNECT. It would seem that at this time, I could check for a Map entry that requests a proxy session. When found, I create (potentially another) ConnectionLifecycleListener whose job it is to start/end the proxy session. As you say, actually starting the proxy session would be implemented inside the SQLDialect and in my case, the OracleSQLDialect.

Seems like a good approach to me. I’d extend the OracleDataStoreFactory with one or more extra parameter that enables proxy connection, and that are given

the name of the env variable(s) you need to pass down, and if those are setup, then add your own custom listener to the store:

https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleNGDataStoreFactory.java

Maybe following this approach you don’t even need to modify the SQLDialect class

Ah, you’ll need to unwrap the connection from the pool via the Unwrappers, the Oracle store contains code already calling them (the pooled connection

wrap the Oracle one, but you need the native one). Given there are many connection pools we have an extension point for that.

See: https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java#L617

Cheers

Andrea

PS: what you add to the main data store factory should also be added to the JNDI one (and probably to the OCI one, although I’m not sure about that one).

–

==

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.

---

[Andrea_Aime4]

On Wed, Jun 15, 2016 at 6:25 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

Thanks. Looking to simplify this yet more. The solution below would
still require me to modify the datastore setup page in geoserver so the
page would let me request a proxy session be created.

Nope, it's auto generated from the connection params advertised by the
store. So no problems here, the extras would only show up when a Oracle
store is configured.

How ‘bad’ would be if I modified the geotools SessionCommandsListener to
specifically check for SET SESSION AUTHORIZATION being the statement
getting executed? When found, this is a directly executed pass-thru for
non-Oracle dialect but for Oracle this uses their proprietary API to do the
equivalent.

That class is generic and unaware that Oracle even exists, I would not go
there.

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.

-------------------------------------------------------

[Walter_Stovall]

Oh, that’s nice – thanks.

There’s hopefully just one last part of this problem I need to address (thank you for all your help).

As mentioned my authenticated-user (the GSUser) is not in fact the name of the user I want to impersonate. I need to figure out how to get the name of the impersonated user into the environment.

The impersonated user is based on the geoserver workspace and remains constant for the life of the workspace. My dream solution would be that I could store the name of the impersonated user into the workspace itself and somehow leverage code that gets the name/value into the environment when a request is dispatched. Poking around, I see that the WorkspaceInfoImpl has set/getMetadataMap(). If I could put my own name/value pair for the impersonated user into the MetadataMap for the workspace and then the MetadataMap were found to be part of the environment that would be the magic answer.

Of course maybe it’s not that easy but does this trigger any suggestion you might have? I know the impersonated user name when I create the workspace. I just don’t know how to get that into the environment when a request against the workspace is dispatched.

Do I need a dispatcher callback? If so can I make the callback installed by my service code run before other services like WMS/WFS attempt accessing features so this is all setup right?

Thank You - Walter

···

On Wed, Jun 15, 2016 at 6:25 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Thanks. Looking to simplify this yet more. The solution below would still require me to modify the datastore setup page in geoserver so the page would let me request a proxy session be created.

Nope, it’s auto generated from the connection params advertised by the store. So no problems here, the extras would only show up when a Oracle store is configured.

How ‘bad’ would be if I modified the geotools SessionCommandsListener to specifically check for SET SESSION AUTHORIZATION being the statement getting executed? When found, this is a directly executed pass-thru for non-Oracle dialect but for Oracle this uses their proprietary API to do the equivalent.

That class is generic and unaware that Oracle even exists, I would not go there.

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.

---

[Andrea_Aime4]

On Wed, Jun 15, 2016 at 6:57 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

Oh, that’s nice – thanks.

There’s hopefully just one last part of this problem I need to address
(thank you for all your help).

As mentioned my authenticated-user (the GSUser) is not in fact the name of
the user I want to impersonate. I need to figure out how to get the name
of the impersonated user into the environment.

The impersonated user is based on the geoserver workspace and remains
constant for the life of the workspace. My dream solution would be that I
could store the name of the impersonated user into the workspace itself and
somehow leverage code that gets the name/value into the environment when a
request is dispatched. Poking around, I see that the WorkspaceInfoImpl has
set/getMetadataMap(). If I could put my own name/value pair for the
impersonated user into the MetadataMap for the workspace and then the
MetadataMap were found to be part of the environment that would be the
magic answer.

Of course maybe it’s not that easy but does this trigger any suggestion
you might have? I know the impersonated user name when I create the
workspace. I just don’t know how to get that into the environment when a
request against the workspace is dispatched.

Do I need a dispatcher callback? If so can I make the callback installed
by my service code run before other services like WMS/WFS attempt accessing
features so this is all setup right?

Hmm... this is getting complicated enough that I believe you are going to
need some custom code regardless, in one or more points.
If you are using workspace specific services, you can get the current
workspace from the LocalWorkspace class, that is holding to a thread local.
If you are using global services instead I don't see an easy way out, as a
request might contain data from multiple workspaces.

A few ideas come to mind (mind, I cannot guarantee any of them is good):
1) Create a subclass of the OracleNGDataStoreFactory, register it in SPI
(check META-INF/services), and when createDataStoreInternal is called,
attach a connection listener that looks for LocalWorkspace and your custom
map. This code will reside in GeoServer
2) Create a FeatureTypeInitializer and register it in the GeoServer app
context, while it's meant to initialize a particular feature type, it has
access to the store too, and attach the listener there. Of course you'll
have to care about not attaching the same listener multiple times
3) Modify the code a bit deeper and create a new DataAccessInitializer
interface that would allow custom setup of stores, to be plugged in
ResourcePool like FeatureTypeInitializer is, and do the same as above
4) Add store init parameters like we discussed before, but make them OGC
Expression objects, or Strings that are supposed to be parsed as ECQL, and
then roll your own custom filter functions to extract the values you need
from the surrounding environment (e.g., LocalWorkspace)

I'd go for 3, but it's just me.

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.

-------------------------------------------------------

[Walter_Stovall]

Thanks for that detail. I don’t yet grasp what the complexity is – I suspect it doesn’t apply to my geoserver use-case but maybe you could illuminate that for me?

I’m using global workspaces (but could consider changing that). However an end user will always be referencing only one workspace in a request. By parsing the request parameters the workspace can be known up front before any database access.

So if I have a bunch of workspaces setup that all use the same datastore, my assumption is that when a database connection is acquired from the datastore, the relevant workspace can be known at that time, enabling the code to tell the database connection to impersonate the user associated with the workspace.

The datastore can connect to the database under the ‘midtier/admin’ user account. Then, as long as geoserver does not attempt to access a feature in the workspace before pointing the connection to the impersonated user for that workspace, the problem is solved.

If the workspace is known from the request parameters and a request never references more than one workspace does that simplify my problem?

Thank You - Walter

···

On Wed, Jun 15, 2016 at 6:57 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Oh, that’s nice – thanks.

There’s hopefully just one last part of this problem I need to address (thank you for all your help).

As mentioned my authenticated-user (the GSUser) is not in fact the name of the user I want to impersonate. I need to figure out how to get the name of the impersonated user into the environment.

The impersonated user is based on the geoserver workspace and remains constant for the life of the workspace. My dream solution would be that I could store the name of the impersonated user into the workspace itself and somehow leverage code that gets the name/value into the environment when a request is dispatched. Poking around, I see that the WorkspaceInfoImpl has set/getMetadataMap(). If I could put my own name/value pair for the impersonated user into the MetadataMap for the workspace and then the MetadataMap were found to be part of the environment that would be the magic answer.

Of course maybe it’s not that easy but does this trigger any suggestion you might have? I know the impersonated user name when I create the workspace. I just don’t know how to get that into the environment when a request against the workspace is dispatched.

Do I need a dispatcher callback? If so can I make the callback installed by my service code run before other services like WMS/WFS attempt accessing features so this is all setup right?

Hmm… this is getting complicated enough that I believe you are going to need some custom code regardless, in one or more points.

If you are using workspace specific services, you can get the current workspace from the LocalWorkspace class, that is holding to a thread local. If you are using global services instead I don’t see an easy way out, as a request might contain data from multiple workspaces.

A few ideas come to mind (mind, I cannot guarantee any of them is good):

1. Create a subclass of the OracleNGDataStoreFactory, register it in SPI (check META-INF/services), and when createDataStoreInternal is called, attach a connection listener that looks for LocalWorkspace and your custom map. This code will reside in GeoServer

2. Create a FeatureTypeInitializer and register it in the GeoServer app context, while it’s meant to initialize a particular feature type, it has access to the store too, and attach the listener there. Of course you’ll have to care about not attaching the same listener multiple times

3. Modify the code a bit deeper and create a new DataAccessInitializer interface that would allow custom setup of stores, to be plugged in ResourcePool like FeatureTypeInitializer is, and do the same as above

4. Add store init parameters like we discussed before, but make them OGC Expression objects, or Strings that are supposed to be parsed as ECQL, and then roll your own custom filter functions to extract the values you need from the surrounding environment (e.g., LocalWorkspace)

I’d go for 3, but it’s just me.

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.

---

[Walter_Stovall]

Please ignore my response – I was reading your list of options as ‘all of the above’ and I understand better now that those are all separate ideas. I need to study this more…thanks.

···

From: Walter Stovall
Sent: Thursday, June 16, 2016 4:48 AM
To: ‘Andrea Aime’ <andrea.aime@…1268…>
Cc: geoserver-devel@lists.sourceforge.net
Subject: RE: [Geoserver-devel] FW: Managing Oracle connections to different schemas of the same database instance can’t be done with the current geoserver

Thanks for that detail. I don’t yet grasp what the complexity is – I suspect it doesn’t apply to my geoserver use-case but maybe you could illuminate that for me?

I’m using global workspaces (but could consider changing that). However an end user will always be referencing only one workspace in a request. By parsing the request parameters the workspace can be known up front before any database access.

So if I have a bunch of workspaces setup that all use the same datastore, my assumption is that when a database connection is acquired from the datastore, the relevant workspace can be known at that time, enabling the code to tell the database connection to impersonate the user associated with the workspace.

The datastore can connect to the database under the ‘midtier/admin’ user account. Then, as long as geoserver does not attempt to access a feature in the workspace before pointing the connection to the impersonated user for that workspace, the problem is solved.

If the workspace is known from the request parameters and a request never references more than one workspace does that simplify my problem?

Thank You - Walter

From: andrea.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Thursday, June 16, 2016 3:51 AM
To: Walter Stovall <walter.stovall@…4309…>
Cc: geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] FW: 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 6:57 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Oh, that’s nice – thanks.

There’s hopefully just one last part of this problem I need to address (thank you for all your help).

As mentioned my authenticated-user (the GSUser) is not in fact the name of the user I want to impersonate. I need to figure out how to get the name of the impersonated user into the environment.

The impersonated user is based on the geoserver workspace and remains constant for the life of the workspace. My dream solution would be that I could store the name of the impersonated user into the workspace itself and somehow leverage code that gets the name/value into the environment when a request is dispatched. Poking around, I see that the WorkspaceInfoImpl has set/getMetadataMap(). If I could put my own name/value pair for the impersonated user into the MetadataMap for the workspace and then the MetadataMap were found to be part of the environment that would be the magic answer.

Of course maybe it’s not that easy but does this trigger any suggestion you might have? I know the impersonated user name when I create the workspace. I just don’t know how to get that into the environment when a request against the workspace is dispatched.

Do I need a dispatcher callback? If so can I make the callback installed by my service code run before other services like WMS/WFS attempt accessing features so this is all setup right?

Hmm… this is getting complicated enough that I believe you are going to need some custom code regardless, in one or more points.

If you are using workspace specific services, you can get the current workspace from the LocalWorkspace class, that is holding to a thread local. If you are using global services instead I don’t see an easy way out, as a request might contain data from multiple workspaces.

A few ideas come to mind (mind, I cannot guarantee any of them is good):

1. Create a subclass of the OracleNGDataStoreFactory, register it in SPI (check META-INF/services), and when createDataStoreInternal is called, attach a connection listener that looks for LocalWorkspace and your custom map. This code will reside in GeoServer

2. Create a FeatureTypeInitializer and register it in the GeoServer app context, while it’s meant to initialize a particular feature type, it has access to the store too, and attach the listener there. Of course you’ll have to care about not attaching the same listener multiple times

3. Modify the code a bit deeper and create a new DataAccessInitializer interface that would allow custom setup of stores, to be plugged in ResourcePool like FeatureTypeInitializer is, and do the same as above

4. Add store init parameters like we discussed before, but make them OGC Expression objects, or Strings that are supposed to be parsed as ECQL, and then roll your own custom filter functions to extract the values you need from the surrounding environment (e.g., LocalWorkspace)

I’d go for 3, but it’s just me.

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.

---

[Walter_Stovall]

I’m working with the FeatureTypeInitializer you mentioned (which I assume is really FeatureTypeCallback).

I feel like I’m almost home free if I can just figure out the workspace at the time my FeatureTypeCallback is accessed. Then my ConnectionLifecycleListener can be seeded with the workspace and I’ve got all the info I need to impersonate the Oracle user when onBorrow() gets called.

When FeatureTypeCallback.initialize gets called is there any way I can figure out which workspace matches that feature? The FeatureTypeCallback gets called while workspaces are getting initialized during geoserver startup so a workspace reference must be accessible somewhere down the callstack but there’s a fair amount of reflection & bean initialization going on that makes it hard to figure out how to reference that.

Any ideas?

Thanks! Walter

···

On Wed, Jun 15, 2016 at 6:57 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Oh, that’s nice – thanks.

There’s hopefully just one last part of this problem I need to address (thank you for all your help).

As mentioned my authenticated-user (the GSUser) is not in fact the name of the user I want to impersonate. I need to figure out how to get the name of the impersonated user into the environment.

The impersonated user is based on the geoserver workspace and remains constant for the life of the workspace. My dream solution would be that I could store the name of the impersonated user into the workspace itself and somehow leverage code that gets the name/value into the environment when a request is dispatched. Poking around, I see that the WorkspaceInfoImpl has set/getMetadataMap(). If I could put my own name/value pair for the impersonated user into the MetadataMap for the workspace and then the MetadataMap were found to be part of the environment that would be the magic answer.

Of course maybe it’s not that easy but does this trigger any suggestion you might have? I know the impersonated user name when I create the workspace. I just don’t know how to get that into the environment when a request against the workspace is dispatched.

Do I need a dispatcher callback? If so can I make the callback installed by my service code run before other services like WMS/WFS attempt accessing features so this is all setup right?

Hmm… this is getting complicated enough that I believe you are going to need some custom code regardless, in one or more points.

If you are using workspace specific services, you can get the current workspace from the LocalWorkspace class, that is holding to a thread local. If you are using global services instead I don’t see an easy way out, as a request might contain data from multiple workspaces.

A few ideas come to mind (mind, I cannot guarantee any of them is good):

1. Create a subclass of the OracleNGDataStoreFactory, register it in SPI (check META-INF/services), and when createDataStoreInternal is called, attach a connection listener that looks for LocalWorkspace and your custom map. This code will reside in GeoServer

2. Create a FeatureTypeInitializer and register it in the GeoServer app context, while it’s meant to initialize a particular feature type, it has access to the store too, and attach the listener there. Of course you’ll have to care about not attaching the same listener multiple times

3. Modify the code a bit deeper and create a new DataAccessInitializer interface that would allow custom setup of stores, to be plugged in ResourcePool like FeatureTypeInitializer is, and do the same as above

4. Add store init parameters like we discussed before, but make them OGC Expression objects, or Strings that are supposed to be parsed as ECQL, and then roll your own custom filter functions to extract the values you need from the surrounding environment (e.g., LocalWorkspace)

I’d go for 3, but it’s just me.

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.

---

[Walter_Stovall]

I’m more confused than I thought. I was thinking I’d have multiple workspaces sharing a single datastore. That must be impossible since a datastore references a specific workspace.

···

From: Walter Stovall
Sent: Thursday, June 16, 2016 7:37 AM
To: ‘Andrea Aime’ <andrea.aime@…1268…>
Cc: geoserver-devel@lists.sourceforge.net
Subject: RE: [Geoserver-devel] FW: Managing Oracle connections to different schemas of the same database instance can’t be done with the current geoserver

I’m working with the FeatureTypeInitializer you mentioned (which I assume is really FeatureTypeCallback).

I feel like I’m almost home free if I can just figure out the workspace at the time my FeatureTypeCallback is accessed. Then my ConnectionLifecycleListener can be seeded with the workspace and I’ve got all the info I need to impersonate the Oracle user when onBorrow() gets called.

When FeatureTypeCallback.initialize gets called is there any way I can figure out which workspace matches that feature? The FeatureTypeCallback gets called while workspaces are getting initialized during geoserver startup so a workspace reference must be accessible somewhere down the callstack but there’s a fair amount of reflection & bean initialization going on that makes it hard to figure out how to reference that.

Any ideas?

Thanks! Walter

From: andrea.aime@…403… [mailto:andrea.aime@…403…] On Behalf Of Andrea Aime
Sent: Thursday, June 16, 2016 3:51 AM
To: Walter Stovall <walter.stovall@…4309…>
Cc: geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] FW: 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 6:57 PM, Walter Stovall <walter.stovall@…4309…> wrote:

Oh, that’s nice – thanks.

There’s hopefully just one last part of this problem I need to address (thank you for all your help).

As mentioned my authenticated-user (the GSUser) is not in fact the name of the user I want to impersonate. I need to figure out how to get the name of the impersonated user into the environment.

The impersonated user is based on the geoserver workspace and remains constant for the life of the workspace. My dream solution would be that I could store the name of the impersonated user into the workspace itself and somehow leverage code that gets the name/value into the environment when a request is dispatched. Poking around, I see that the WorkspaceInfoImpl has set/getMetadataMap(). If I could put my own name/value pair for the impersonated user into the MetadataMap for the workspace and then the MetadataMap were found to be part of the environment that would be the magic answer.

Of course maybe it’s not that easy but does this trigger any suggestion you might have? I know the impersonated user name when I create the workspace. I just don’t know how to get that into the environment when a request against the workspace is dispatched.

Do I need a dispatcher callback? If so can I make the callback installed by my service code run before other services like WMS/WFS attempt accessing features so this is all setup right?

Hmm… this is getting complicated enough that I believe you are going to need some custom code regardless, in one or more points.

If you are using workspace specific services, you can get the current workspace from the LocalWorkspace class, that is holding to a thread local. If you are using global services instead I don’t see an easy way out, as a request might contain data from multiple workspaces.

A few ideas come to mind (mind, I cannot guarantee any of them is good):

1. Create a subclass of the OracleNGDataStoreFactory, register it in SPI (check META-INF/services), and when createDataStoreInternal is called, attach a connection listener that looks for LocalWorkspace and your custom map. This code will reside in GeoServer

2. Create a FeatureTypeInitializer and register it in the GeoServer app context, while it’s meant to initialize a particular feature type, it has access to the store too, and attach the listener there. Of course you’ll have to care about not attaching the same listener multiple times

3. Modify the code a bit deeper and create a new DataAccessInitializer interface that would allow custom setup of stores, to be plugged in ResourcePool like FeatureTypeInitializer is, and do the same as above

4. Add store init parameters like we discussed before, but make them OGC Expression objects, or Strings that are supposed to be parsed as ECQL, and then roll your own custom filter functions to extract the values you need from the surrounding environment (e.g., LocalWorkspace)

I’d go for 3, but it’s just me.

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.

---

[Andrea_Aime4]

On Thu, Jun 16, 2016 at 1:37 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

I’m working with the FeatureTypeInitializer you mentioned (which I assume
is really FeatureTypeCallback).

I feel like I’m almost home free if I can just figure out the workspace at
the time my FeatureTypeCallback is accessed. Then my
ConnectionLifecycleListener can be seeded with the workspace and I’ve got
all the info I need to impersonate the Oracle user when onBorrow() gets
called.

When FeatureTypeCallback.initialize gets called is there any way I can
figure out which workspace matches that feature? The FeatureTypeCallback
gets called while workspaces are getting initialized during geoserver
startup so a workspace reference must be accessible somewhere down the
callstack but there’s a fair amount of reflection & bean initialization
going on that makes it hard to figure out how to reference that.

Any ideas?

The datastore namespace is the same as the workspace one, so you can make
the link that way, at least for most
stores, and for sure, for the JDBC ones. You callback just needs a
reference to the catalog, which you can
inject in the spring context)

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.

-------------------------------------------------------

[Andrea_Aime4]

On Thu, Jun 16, 2016 at 1:59 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

I’m more confused than I thought. I was thinking I’d have multiple
workspaces sharing a single datastore. That must be impossible since a
datastore references a specific workspace.

You just need to setup the store to have a JNDI provided connection pool.
Many stores, one pool

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.

-------------------------------------------------------

[Walter_Stovall]

Thanks – I had lost sight of that part of this…

I’m doing the namespace-reference you mention to figure out the workspace – works nicely. I now have a context where I can do the impersonation at ConnectionLifecycleListener.onBorrow() and it looks like I might just pull this off with zero change to geotools OR geoserver except for one “small” problem. I need to unwrap the connection to an OracleConnection so I can call its openProxySession() to do the impersonation. I can get the dialect from JDBCDataStore.getSQLDialect and downcast it to OracleDialect but the OracleDialect.unwrap() is package-private.

Any thoughts on how I can unwrap without changing code? Is this going to call for modifying the SQLDialect to impersonate a user like we discussed before? Do I have the option of subclassing OracleDialect and somehow seeing that my dialect class get used? Maybe that way I could contain the impersonation problem in my code and not disturb geotools at all?

Thanks for your help - Walter

···

On Thu, Jun 16, 2016 at 1:59 PM, Walter Stovall <walter.stovall@…4309…> wrote:

I’m more confused than I thought. I was thinking I’d have multiple workspaces sharing a single datastore. That must be impossible since a datastore references a specific workspace.

You just need to setup the store to have a JNDI provided connection pool. Many stores, one pool

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.

---