<div dir="ltr"><div><div>Hi Martin,</div><div><br></div><div>I've read through your conversation in the mapserver-users mailing list and think that the approach, to set the role or in your case the context after a successful connection, is better then the usage of virtual spatial layer.</div><div><br></div><div><a href="http://www.gdal.org/drv_vrt.html">http://www.gdal.org/drv_vrt.html</a></div><div>The documentation say "If SrcLayer isn't provided, then SrcSQL element MUST be provided"; I don't have tested it yet, but it seems to me, that only one of this elements can be set and the main problem, that the context/role will be set after fetching the data, persists.</div><div><br></div><div>I'm in favor to implement a more generic solution, that will work for PostgreSQL and Oracle, but we should limit the input to solve our needs. Allowing to put in any sql statement seems to me to dangerous (Think about a "drop table" statement).</div><div><br></div><div>The "SET ROLE role_name" is the build-in mechanism in PostgreSQL to change the ACL behavior and to use the RLS policies. I'm not a Oracle user, is the set_context procedure the equivalent function to "SET ROLE" and a build-in procedure of Oracle?</div><div><br></div><div>Best regards,</div><div>Daniel</div></div><div class="gmail_extra"><br clear="all"><div><div class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><blockquote type="cite"><div bgcolor="#FFFFFF"><div></div></div></blockquote></div></div></div></div></div></div></div><div class="gmail_quote">2017-08-10 15:53 GMT+02:00 Martin Icking <span dir="ltr"><<a href="mailto:martin.icking@bentley.com" target="_blank">martin.icking@bentley.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Daniel,<br>
I'm facing almost the same challenge with Oracle - see here:<br>
<a href="http://osgeo-org.1560.x6.nabble.com/Oracle-Layer-how-to-set-context-prior-to-fetching-the-data-tt5258155.html" rel="noreferrer" target="_blank">http://osgeo-org.1560.x6.<wbr>nabble.com/Oracle-Layer-how-<wbr>to-set-context-prior-to-<wbr>fetching-the-data-tt5258155.<wbr>html</a><br>
I tried some of the options without modification of mapserver - as of now<br>
I'm not really happy with these.<br>
Maybe we can use your approach in a more generic way by extending Mapserver<br>
to have the option to execute a user-defined SQL command after login.<br>
Naturally in Oracle there is no "SET ROLE rolename" command, the syntax is<br>
different.<br>
Extending your suggestion the sql command to set the database context/role<br>
could be defined by<br>
<br>
PROCESSING "SQL_CONTEXT_STMT='any sql statement'"<br>
<br>
What do you think?<br>
Best regards<br>
Martin<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://osgeo-org.1560.x6.nabble.com/Switch-db-role-after-connecting-to-db-for-row-level-security-tp5330790p5331114.html" rel="noreferrer" target="_blank">http://osgeo-org.1560.x6.<wbr>nabble.com/Switch-db-role-<wbr>after-connecting-to-db-for-<wbr>row-level-security-<wbr>tp5330790p5331114.html</a><br>
Sent from the Mapserver - Dev mailing list archive at Nabble.com.<br>
______________________________<wbr>_________________<br>
mapserver-dev mailing list<br>
<a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/mapserver-dev</a></blockquote></div><br></div></div>
<br>
<font color="#888888" face="DejaVu
Sans,Verdana,Arial,Helvetica,sans-serif" size="1"><strong>ATTENZIONE!</strong> Le informazioni contenute
nella presente e-mail e nei documenti eventualmente
allegati sono confidenziali. La loro diffusione,
distribuzione e/o riproduzione da parte di terzi,
senza autorizzazione del mittente è vietata e può
violare il D. Lgs. 196/2003. In caso di ricezione per
errore, Vogliate immediatamente informare il mittente
del messaggio e distruggere la e-mail.<br>
<br>
<strong>ACHTUNG!</strong> Die in dieser Nachricht oder
in den beigelegten Dokumenten beinhalteten
Informationen sind streng vertraulich. Ihre
Verbreitung und/oder ihre Wiedergabe durch Dritte ist
ohne Erlaubnis des Absenders verboten und verstößt
gegen das Legislativdekret 196/2003. Sollten Sie diese
Mitteilung irrtümlicherweise erhalten haben, bitten
wir Sie uns umgehend zu informieren und anschließend
die Mitteilung zu vernichten.<br>
<br>
<strong>WARNING!</strong> This e-mail may contain
confidential and/or privileged information. If you are
not the intended recipient (or have received this
e-mail in error) please notify the sender immediately
and destroy this e-mail. Any unauthorised copying,
disclousure or distribution of the material in this
e-mail is strictly forbidden and could be against the
law (D. Lgs. 196/2003)<br></font>