[mapserver-dev] Switch db role after connecting to db for row level security

[Daniel Degasperi]

Hi Martin,

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.

http://www.gdal.org/drv_vrt.html
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.

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).

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?

Best regards,
Daniel

2017-08-10 15:53 GMT+02:00 Martin Icking <martin.icking at bentley.com>:

> Hi Daniel,
> I'm facing almost the same challenge with Oracle - see here:
> http://osgeo-org.1560.x6.nabble.com/Oracle-Layer-how-
> to-set-context-prior-to-fetching-the-data-tt5258155.html
> I tried some of the options without modification of mapserver - as of now
> I'm not really happy with these.
> Maybe we can use your approach in a more generic way by extending Mapserver
> to have the option to execute a user-defined SQL command  after login.
> Naturally in Oracle there is no "SET ROLE rolename" command, the syntax is
> different.
> Extending your suggestion the sql command to set the database context/role
> could be defined by
>
> PROCESSING "SQL_CONTEXT_STMT='any sql statement'"
>
> What do you think?
> Best regards
> Martin
>
>
>
> --
> View this message in context: http://osgeo-org.1560.x6.
> nabble.com/Switch-db-role-after-connecting-to-db-for-row-level-security-
> tp5330790p5331114.html
> Sent from the Mapserver - Dev mailing list archive at Nabble.com.
> _______________________________________________
> mapserver-dev mailing list
> mapserver-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-dev

-- 
*ATTENZIONE!* 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.

*ACHTUNG!* 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.

*WARNING!* 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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20170811/5b71b447/attachment.html>