Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#3185 closed enhancement (fixed)

Allow OGR to access Oracle Databases that use OS Authentication

Reported by: cgsbob Owned by: warmerdam
Priority: normal Milestone:
Component: OGR_SF Version: unspecified
Severity: normal Keywords: OCI Oracle
Cc: Mateusz Łoskot, ilucena

Description (last modified by warmerdam)

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)

Change History (13)

comment:1 Changed 10 years ago by warmerdam

Cc: Mateusz Łoskot ilucena added
Component: defaultOGR_SF
Description: modified (diff)
Keywords: OCI Oracle added

Ivan,

Would you be interested in reworking the session setup to allow OS authentication?

comment:2 Changed 10 years ago by cgsbob

Maybe a GDAL programmer can use the info at http://orclib.sourceforge.net/doc/html/. According to this web page this library supports OS Authentication.

comment:3 Changed 10 years ago by ilucena

Sorry to take so long to answer. I wasn't sure that I was going to have time to do that.

Anyway, I just implemented the OS authentication in OGR/OCI and it seems to be working fine.

See some use case:

% ogrinfo oci:
INFO: Open of `oci:'
      using driver `OCI' successful.
1: SCOTT.USGS24KGRID
% ogr2ogr -f "ESRI Shapefile" out.shp oci: SCOTT.USGS24KGRID

It doesn't really need to put a "/" as in "sqlplus /" but it doesn't hurt to put it.

% ogrinfo oci:/

Should I proceed and commit the changes, or should I just attach a patch?

comment:4 Changed 10 years ago by warmerdam

Ivan,

You rock! Please commit it in trunk and folks can test it from there. Unless it gets tested, and their is a hue and cry I'd hesitate to back port it into 1.7.

comment:5 Changed 10 years ago by ilucena

Resolution: fixed
Status: newclosed

You haven't seem the code yet ;)

You can see that this connection process is much more elaborated, with several different calls to OCI until getting to the point where it calls OCISessionBegin() passing OCI_CRED_RDBMS and use Oracle's credential or OCI_CRED_EXT and use the OS credential. I haven't tested in Windows and Oracle 10g yet but I will.

comment:6 Changed 10 years ago by ilucena

Committed revision r19000

comment:7 Changed 10 years ago by Mateusz Łoskot

Ivan, great work indeed. Thanks!

comment:8 in reply to:  3 Changed 10 years ago by cgsbob

Ivan,

Thank you so much for your work. I won't be able to test this until it shows up on OSGEO4W. Got any ideas when that might happen?

comment:9 Changed 10 years ago by warmerdam

I have advised Ivan that the work should remain in trunk until it is tested, and it is not normal for the OSGeo4W "gdaldev" (trunk) package to include drivers like OCI that would normally be handled as a plugin. So I think we are in a catch-22!

comment:10 Changed 10 years ago by ilucena

I can build a OCI plugin dll compatible with OSGEO4W and put it somewhere for download.

comment:11 in reply to:  10 Changed 10 years ago by cgsbob

Thanks Ivan. I'll keep scanning OSGEO4W for the appearance of this plugin.

Replying to ilucena:

I can build a OCI plugin dll compatible with OSGEO4W and put it somewhere for download.

comment:12 Changed 10 years ago by warmerdam

I would note that cgibob likely wants to use this with qgis which is using the gdal16 package now, so the plugin was need to be built for that.

comment:13 Changed 10 years ago by ilucena

Allows OS Authentication for GeoRaster at revision: r19008

Note: See TracTickets for help on using tickets.