Allow OGR to access Oracle Databases that use OS Authentication

[cgsbob]

The summary pretty much says it all. Below is an IRC discussion about this subject. Sorry for adding an extra lines. Without them this ticket would be all garbled up:

<cgs_bob> Hello all. Is there a way to use ogr with an Oracle database that uses OS validation?

<mloskot> cgs_bob: I would expect it's something transparent, not required to implement by proxy-client as OGR OCI driver is

<mloskot> cgs_bob: ​http://www.oracle-base.com/articles/misc/OsAuthentication.php

<sigq> Title: ORACLE-BASE - OS Authentication (at www.oracle-base.com)

<cgs_bob> mloskot: I'm afraid I do not understand :) are you saying that I would not need to supply a login and password? in all of the ogr examples I've seen, you need to supply the login and password.

<mloskot> cgs_bob: now, what I'm saying is chances are you can supply all necessary credentials in connection string and OGR OCI driver will forward it to oracle correctly

<mloskot> AFAIK, OGR never interfers in things like authentication, but it just transparently passes this responsibility to another tier

<cgs_bob> mloskot: gotcha. I'll take a look at the link above and experiment more. thanks for the info

<mloskot> cgs_bob: actually, it's very easy to check what OGR OCI does:

<mloskot> http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/oci/ogrocidatasource.cpp#L169

<sigq> Title: /trunk/gdal/ogr/ogrsf_frmts/oci/ogrocidatasource.cpp - GDAL - Trac (at trac.osgeo.org)

<mloskot> connection string -> tokenize to retrieve user/pass, etc. -> pass to OCI library

<mloskot> http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/oci/ogrocisession.cpp#L107

<sigq> Title: /trunk/gdal/ogr/ogrsf_frmts/oci/ogrocisession.cpp - GDAL - Trac (at trac.osgeo.org)

<mloskot> and this

<mloskot> http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/oci/ogrocisession.cpp#L137

<sigq> Title: /trunk/gdal/ogr/ogrsf_frmts/oci/ogrocisession.cpp - GDAL - Trac (at trac.osgeo.org)

<cgs_bob> so how does OS validation fits into that scheme? I thought there is no password. in any case, I'll take a look at these links...thanks

<FrankW> cgs_bob: I don't know how os validation works either, but OGR does nothing special with regard to it.

<mloskot> cgs_bob: learn about Oracle and its API and having what OGR does, you will know

<FrankW> And then consider adding some wisdom at: http://trac.osgeo.org/gdal/wiki/Oracle

<sigq> Title: Oracle - GDAL - Trac (at trac.osgeo.org)

<cgs_bob> mloskot and FrankW, thanks for the help. I see I have lots of studying to do :) if I get any ifo worthy of the wiki I'll add it.

<mloskot> OK, I think I found it

<mloskot> The external authentication in Oracle can be achieved by call of OCISessionBegin function (from OCI) with proper flag OCI_CRED_EXT

<mloskot> ​http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci15r13.htm

<sigq> Title: OCI Relational Functions, 13 of 38 (at download.oracle.com)

<mloskot> As I see, unfortunately, OGR OCI does not use this mode to establish connection/session

<FrankW> So we would need to do something special in the driver?

<FrankW> cgs_bob: if you are really keen on doing this, file a ticket, and we might be able to incorporate it for testing in trunk.

<mloskot> after 5 minutes research, yes

<mloskot> we would need to use different API calls, different more advanced session construction

<mloskot> but as I said, deeper investigation is needed.

<FrankW> Perhaps I can talk Ivan into it!

<mloskot> What I'm sure about is the flag OCI_CRED_EXT, it is used for so called OS Authentication even in PHP

<FrankW> He is keen on all things oracle.

<cgs_bob> FrankW: it is very important for use to use OS authentication, so I'll file a ticket

<mloskot> We use OCILogon

<mloskot> ​http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci15re9.htm

<FrankW> cgs_bob: a mandate from the IT police?

<sigq> Title: OCI Relational Functions, 9 of 38 (at download.oracle.com)

<mloskot> "to create a simple logon session."

<mloskot> what would confirm what my suppositions

<FrankW> Please include mloskot's notes and links in the ticket.

<FrankW> mloskot: I'm assuming you don't have a deep desire to work on the oracle driver?

<mloskot> FrankW: it's not that I don't have desire, but I'm very short with time now

<mloskot> not earlier than in 5-6 days

<mloskot> but I will participate in the ticket and jump in if I can

<cgs_bob> FrankW: yup. and if we can't get ogr to work with OS authentication, they will have another reason to reject foss4g

<FrankW> Oh, when you put it like that you motivate me!

• mloskot has no idea who those they are, so can't get very motivated ;-)

<cgs_bob> FrankW: we have done a lot of good stuff with foss4g, so our project is still alive

<mloskot> What I've just learned, OCILogon is a simple single-user-session per connection mode, OCISessionBegin provides more advanced features

<mloskot> ​http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci02bas.htm

<sigq> Title: OCI Programming Basics (at download.oracle.com)

<mloskot> Given that, I'm quite sure OGR OCI does not use API required to support Oracle OS Authentication

--> chaitanyach has joined this channel (n=chaitany@117.204.64.167).

<FrankW> I wonder if I could get oracle running on my new linux server.

<mloskot> As I see, it should be sufficient to dig ogrocisession.cpp only and replace OCILogon with OCIServerAttach + some OCIHandleAlloc calls + OCISessionBegin as shown in this section

<mloskot> OCIHandleAlloc

<mloskot> ​http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci02bas.htm#423550

<sigq> Title: OCI Programming Basics (at download.oracle.com)