How to use DBMS_LDAP (part 2: Connecting)

Establishing a Connection

The DBMS_LDAP.INIT function is used to connect to an LDAP server. On success, the function returns a session handle of DBMS_LDAP.SESSION type.

    FUNCTION init (hostname IN VARCHAR2,
                   portnum  IN PLS_INTEGER )
      RETURN SESSION;

DBMS_LDAP.PORT is a constant defined to the default port of 389. The INIT function itself does not have default values for the input parameters, so you must pass in a port, even if it’s just the default port.

The simplest complete block (connect,disconnect) would look something like this:

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no standalone close function.
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

Note, this assumes the default exception processing is in effect. That is, on error, the DBMS_LDAP functions will raise an exception. So the v_result assignment would be moot because the assignment would never happen on error. To make the error handling explicit, the same block could be implemented like this:

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no standalone close function.
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

The USE_EXCEPTION setting will persist for the duration of a session, so once set TRUE or FALSE, it will remain in effect across DBMS_LDAP calls. To ensure your error handling works as expected, you will need to set it yourself within your own code to make sure you don’t inherit an unexpected setting from some other code run previously within your user’s session.

The other option would be to set USE_EXCEPTION to FALSE. Doing so would then change the exception handling to be based on the results of each function call.
If INIT fails, instead of raising an exception, it will simply return a NULL value for the session handle. If unbind fails, it will return a numeric code for the error. These will not be SQLCODE values; but rather, when possible, a standard LDAP result code as defined in the Appendix of RFC4511.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := FALSE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    IF v_session IS NULL
    THEN
        DBMS_OUTPUT.put_line('Init failed');
    ELSE
        -- Even if you don't BIND a username/password, you should still UNBIND
        -- to close the session, there is no close
        v_result := DBMS_LDAP.unbind_s(v_session);

        IF v_result != DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line(
                   'Unbind failed with result code: '
                || TO_CHAR(v_result, 'fm9999')
                || ' '
                || DBMS_LDAP.err2string(v_result));
        END IF;
    END IF;
END;

The examples above illustrate a basic ANONYMOUS connection (i.e. no username or password required.) If we wanted to do work on the server, then we would do so between the INIT and the UNBIND_S calls.

If the server requires authentication, then an anonymous connection won’t work. We must send username and password to the server. In LDAP terminology this referred to as “binding.” DBMS_LDAP includes two binding functions: SIMPLE_BIND_S with a fixed authentication method and BIND_S which allows selection of methods. Strangely, DBMS_LDAP defines multiple authentication constants but the documentation, as of 18c, claims: “The only valid value is DBMS_LDAP_UTL.AUTH_SIMPLE.” Also of note, (as of this writing,) the 18c PL/SQL Packages and Types Reference links to the 12.2 Fusion Middleware Application Developer’s Guide for Oracle Identity Management documentation. That may be updated at a future date but it’s something to keep an eye one as future releases/updates come along.

For the sake of simplicity in the examples below I will leave exceptions turned on and allow them to propagate.

When binding to an LDAP server, you won’t use a simple user id as you might with an Oracle database or a UNIX server, but rather your login id will be a full distinguished name and then authenticated with a password.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Login with the DN for a user account and password 
    v_result := DBMS_LDAP.simple_bind_s(
                      ld => v_session, 
                      dn => 'uid=myuserid,ou=users,dc=example,dc=net', 
                      passwd => 'secret_password');

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no close
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

Using the DBMS_LDAP.BIND_S function, the coding is nearly identical. The only difference being the “passwd” parameter is named “cred” and the addition of a fourth parameter to specify the authentication method. Again, as of 18c, the documentation specified only one legal value: AUTH_SIMPLE, which makes this method functionally identical to using SIMPLE_BIND_S.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Login with the DN for a user account and password
    v_result :=
        DBMS_LDAP.bind_s(ld     => v_session,
                         dn     => 'uid=myuserid,ou=users,dc=example,dc=net',
                         cred   => 'secret_password',
                         meth   => DBMS_LDAP.auth_simple);

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no close
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

The last option to explore is connecting with encrypted communication through SSL. To do so you will need a wallet containing the certificates of the servers. Then call OPEN_SSL before authenticating. You must specify the location with a full “file:” path, not the name of a database directory object.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.ssl_port);

    -- Provide the certificate information needed for SSL communication.
    v_result :=
        DBMS_LDAP.open_ssl(ld                => v_session,
                           sslwrl            => 'file:/your/wallet/directory',
                           sslwalletpasswd   => 'wallet_password',
                           sslauth           => 2);

     -- Login with the DN for a user account and password 
    v_result := DBMS_LDAP.simple_bind_s(
                      ld => v_session, 
                      dn => 'uid=myuserid,ou=users,dc=example,dc=net', 
                      passwd => 'secret_password');

    v_result := DBMS_LDAP.unbind_s(v_session);
END;

Hopefully these examples will cover the use cases you might expect to see. In addition to the connection and binding syntax it’s also important to note the error handling flag to either return error codes or raise exceptions.

In my next post I’ll cover searching through the directory tree to find specific records or groups of records.

Table of Contents

Establishing a Connection