Add support for connecting with LDAP in Thin Mode

[mkmoisen]

In cx_Oracle, in order to connect to an oracle database over LDAP, we need to create a sqlnet.ora file and ldap.ora file.

In oracledb's thin mode, sqlnet.ora is not supported, and presumably ldap.ora file is not either.

Is it possible to connect over LDAP in oracledb's thin mode?

If so, how do we do it?

[anthony-tuininga]

Not currently. I can convert this to an enhancement request if you would like. If you can provide any details on your requirements that would be helpful.

[mkmoisen]

I would appreciate being able to use thin mode if that would be a possibility. We are hoping to get away from needing the instant client as it slows down our ability to upgrade docker images.

---

In cx_Oracle, I do LDAP connections this way.

sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT)

For me, I need to put LDAP first, which minimizes the time Oracle spends.

ldap.ora:

DIRECTORY_SERVERS = (myldapserver.host.com:1234)
DEFAULT_ADMIN_CONTEXT = "dc=host,dc=com"
DIRECTORY_SERVER_TYPE = OID

So I suppose I would need some way to tell the thin client to 1) use ldap, 2) provide my ldap servers/ports, I guess this can be a list, as well as the admin context and server type.

[mkmoisen]

In addition, I noticed that in thick mode, I'm unable to connect over LDAP via:

oracledb.connect(user='myuser', password='mypassword', service_name='mydatabase')

This takes ~5 seconds before failing with:

oracledb.exceptions.DatabaseError: ORA-12545: Connect failed because target host or object does not exist

I can connect without issue over LDAP using dns:

oracledb.connect('myuser/mypassword@mydatabase')

[anthony-tuininga]

Thanks. I'll change this to an enhancement request. I don't know when this will be implemented though. You have to convince @cjbj that this enhancement is more important than others!

[cjbj]

'myuser/mypassword@mydatabase'

uses a network service alias, not a service name, so service_name='mydatabase' is not a valid equivalent.
The tnsnames.ora file maps that alias to a descriptor which will contain the actual service name.

[danidorbek]

i have tried to describe something similar in this discussion with a potential solution. Maybe that helps to speed up.

[ilmarkerm]

I'd like to add my strong upvote for this enhancement request.

[urosdigital]

+1

[mkmoisen]

@anthony-tuininga @cjbj Any chance we can get this feature to add support for connecting with LDAP in thin monde scheduled?

Connecting over LDAP is the preferred way at many companies that use Oracle.

[anthony-tuininga]

It is on the list of enhancements but there are many other enhancements on the list, too! If you have any compelling arguments why this particular enhancement should have its priority increased I'm sure that @cjbj would love to hear them!

[cjbj]

We do have a lot of enhancements to both Thin and Thick that our users want, including features we must add for supporting 23c database features. At least with LDAP there is already a general solution, for better or worse: Thick mode. Send me your "business case" so we can prioritize.

[ilmarkerm]

@cjbj here is our business case: Currently there is no proper JDBC-compatible alternative for LDAP connections from python. The Thick mode does not support supplying all connection string information from the URL and requires permanently changing ldap.ora file on the deployment container. This prevents automated application deployments, where all the application connection environment is supplied dynamically during deploy, depending on where the application was currently deployed.
We deploy applications automatically in many different environments all over the world, using the same application container and we cannot pre-create the ldap.ora file inside the container, since LDAP servers are different and should be supplied during deploy time - using JDBC driver compatible connection strings (as almost all applications currently use JDBC driver). Currently this issue prevents us from allowing developers to deploy Python applications that they request.

[mkmoisen]

@cjbj

I would argue that LDAP is the only good way to connect to Oracle and that we should be doing everything we can to incentivize the use of LDAP over TNS. Companies that can pay for Oracle are presumably using RAC and have failover between data centers. LDAP is the best way to handle this configuration as the DBAs can change the individual RAC hosts and the database can failover transparently to applications.

Thick mode today and cx_Oracle in the past have always been inconvenient due to the hard dependency on Oracle Instant Client.

My most recent inconvenience was due to a version incompatibility between a newer version of glibc that our security team wanted us to upgrade to, and a compatible version of libnsl which wasn't available in our companies yum mirror. After spending two days of effort on this, my work around is a Dockerfile that first installs the older version of glibc and libnsl, copies the libnsl.so.1 file, uninstalls libnsl, upgrades glibc, pastes the libnsl.so.1 file into /usr/lib64, and then installs oracle instant client skipping dependencies.

Installing the instant client adds a material amount of time to the container build process.

If someone installs an Oracle Instant Client with mismatched versions, especially major versions, this normally leads to subtle bugs. I can remember one weird bug with timezones - all of the dates that were passed in by the application server were stored in a different timezone on the server. Fun times.

Java programmers have it easier. They don't need oracle instant client, and don't need tnsnames.ora or ldap.ora. They just connect with an ldap string and call it a day. I remember struggling with this as a beginner. I think it took me an entire week to figure out. If you search for "cx_Oracle ldap" in Google you'll find my stackoverflow question from many years ago.

I worry that a lot of beginner programmers would think this is not worth the effort and would just stick with Java.

[cjbj]

@ilmarkerm @mkmoisen thanks for comments; it's just a matter of scheduling (+ completing some legal work). Have some patience.

Random points:

• I have been continuing to champion improving Oracle Call Interface usability for LDAP which should make Thick mode usage easier; there is some progress internally.
• Instant Client 21+ doesn't need libnsl
• There is a new centralized config store for cloud being worked on for all language drivers. ODP.Net doc is here and gives a flavor of this project. Python support is not yet scheduled but is one of our 'must dos'

[mkmoisen]

Hi @cjbj @anthony-tuininga

I tried @danidorbek's workaround here and it appears to work just fine.

All this work around does is manually interrogate the LDAP server to retrieve the TNS string, and then uses the TNS string to connect.

Is this suitable? I'm unclear if thick mode ldap connection does any more than this.

import ldap3
import oracledb

def connect(schema, password, database):
    with ldap3.Connection('my-ldap.com') as conn:
        conn.search('cn=OracleContext,dc=my,dc=com', f"(cn={database})", attributes=['orclNetDescString'])
        tns_string = conn.entries[0].orclNetDescString.value
        print(tns_string)

    return oracledb.connect(f'{schema}/{password}@{tns_string}')

[cjbj]

We haven't done any serious investigation yet either so I can't comment. (Anthony is working hard on this and this - let's not distract him!)

[cjbj]

A question from our team looking at the OCI improvements: what authentication, if any, are you using for LDAP? (cc @mkmoisen @ilmarkerm @danidorbek @urosdigital )

[mkmoisen]

@cjbj we don't use authentication for the ldap server.

[ilmarkerm]

@cjbj we don't use authentication either

[danidorbek]

@cjbj - no-authentication either for us. I am rather confident that Oracle name resolution (at least some time ago) did not support any authentication. It had to be open for read.

[urosdigital]

@cjbj sorry for late response, no auth also

[cjbj]

Thanks for all the feedback I'll pass it on - including the note that doc on authentication could be enhanced or otherwise we should explain the options better.

A couple of random references re authentication:

• https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_THIN_LDAP_SECURITY_AUTHENTICATION
• https://www.oracle.com/a/otn/docs/database/oracle-net-oud-oid-directory-naming.pdf

[cjbj]

Also see #71 (reply in thread)

[mac-vtl]

Hi @cjbj @anthony-tuininga
Do you think this feature will be prioritized/added to the roadmap? Since async is available in thin mode only, using thick mode is not an option for us. The work arounds described in this issue seem to work for people but it makes it difficult to use when relying on libs like sqlalchemy/sqlmodel. This also does not scale well when having to put the same workaround in many different projects.
Thanks!

[cjbj]

@mac-vtl I'm torn. From our point of view, we recognize there is interest. We also recognize that there is a user-space solution, so perhaps it makes more sense for us to use our resources on projects that need our internal knowledge. (And there is always Thick mode support!!) Also I'm not immediately jumping for joy at the thought of including more 3rd party libraries into python-oracledb. Any custom low-level implementation will be a big project.

PRs for documentation or code changes welcome! Or even examples of how you are using a userspace implementation. It would be good to share with the community.

[anthony-tuininga]

I have just pushed code that will allow you to integrate the proposed scripts into your code. If you are able to build from source you can verify that it works for you. Here is an example:

import ldap3
import re

def ldap_hook(protocol, ldap_dsn, params):
    """
    ldap_dsn is in the form "ldapserver/dbname,cn=OracleContext,dc=dom,dc=com"
    """
    pattern = r"^(.+)\/(.+)\,(cn=OracleContext.*)$"
    match = re.match(pattern, ldap_dsn)
    ldap_server, db, ora_context = match.groups()

    server = ldap3.Server(ldap_server)
    conn = ldap3.Connection(server)
    conn.bind()
    conn.search(ora_context, f"(cn={db})", attributes=['orclNetDescString'])
    connect_string = conn.entries[0].orclNetDescString.value
    params.parse_connect_string(connect_string)

oracledb.register_protocol("ldap", ldap_hook)

conn = oracledb.connect("user/password@ldap://ldapserver/dbname,cn=OracleContext,dc=dom,dc=com")

If you are able to play around with this feature and let me know if it works properly for you, that would be appreciated! We are still uncertain how samples like the one above will be shipped with the driver itself. Suggestions for that are also welcome.

[cjbj]

Related doc is at https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#ldap-directory-naming

[mkmoisen]

@anthony-tuininga

Thanks very much for adding this.

---

The example linked by @cjbj works for me as well.

---

Just a thought, instead of making us write out ldap_hook, could oracledb have a default function that is pre-registered for the ldap protocol, which users may optionally override if they have some non-standard implementation?

With oracledb thick mode, as well as in jdbc, users do not need to be aware of the internals behind ldap.

Regardless, thanks very much for getting this done!

[mkmoisen]

Previously you mentioned not wanting to require ldap3 which I agree, but the default function could simply raise the import error or raise a pretty message to the user.

[cjbj]

@mkmoisen if there was a built-in LDAP library then shipping a pre-registered template would be simple. But the normal legal & security & technology (best one?, portability, bundling etc) questions come into play whenever a 3rd party module is needed.

Yes, I fixed the code typo in the doc :)