Overview#
We are not DBA experts but we have often been involved with integration work using Oracle SQL To Access LDAP.We shows some Example code to get you started with Oracle SQL To Access LDAP
DBMS_LDAP - Accessing LDAP From PL/SQL [1]#
The DBMS_LDAP package is a PL/SQL API to enable programatic searches and modifications of data within LDAP directories. In this article I'll demonstrate a simple LDAP search.
Loading the DBMS_LDAP package#
The DBMS_LDAP package is not loaded by default. To install it run the following script as SYS.SQL> @$ORACLE_HOME/rdbms/admin/catldap.sql
Connect And Authenticate#
The first step in any LDAP interaction is to connect to the LDAP server and authenticate yourself. This is done using the init and simple_bind_s functions.
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);
l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
dn => l_ldap_user,
passwd => l_ldap_passwd);
Search LDAP Directory#
Once connected we can search the directory.l_attrs(1) := '*'; -- retrieve all attributes
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=*',
attrs => l_attrs,
attronly => 0,
res => l_message);
The starting point for the search (base), depth of the search (scope), the attributes searched for (attrs) and the filter can be modified to build complex searches.
Loop Through Search Responses#
An LDAP search returns a Search Responses which can be looped through. Note: We do not check for SearchResultReferences within this code.The code assumes all the Search Responses are SearchResultEntrys.
IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session,
msg => l_message);
<< entry_loop >>
WHILE l_entry IS NOT NULL LOOP
...
...
l_entry := DBMS_LDAP.next_entry(ld => l_session,
msg => l_entry);
END LOOP entry_loop;
END IF;
Loop Through Attributes#
For each entry we loop through the associated attributes.l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
<< attributes_loop >>
WHILE l_attr_name IS NOT NULL LOOP
...
...
l_attr_name := DBMS_LDAP.next_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
END LOOP attibutes_loop;
Loop Through Values#
Finally, we retrieve the values associated with the attribute.<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;
Put It All Together#
If we put all these stages together we get the following.SET SERVEROUTPUT ON SIZE 1000000
DECLARE
-- Adjust as necessary.
l_ldap_host VARCHAR2(256) := 'server01.tshcomputing.com';
l_ldap_port VARCHAR2(256) := '389';
l_ldap_user VARCHAR2(256) := 'cn=orcladmin';
l_ldap_passwd VARCHAR2(256) := 'password';
l_ldap_base VARCHAR2(256) := 'cn=Users,dc=tshcomputing,dc=com';
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collection;
l_message DBMS_LDAP.message;
l_entry DBMS_LDAP.message;
l_attr_name VARCHAR2(256);
l_ber_element DBMS_LDAP.ber_element;
l_vals DBMS_LDAP.string_collection;
BEGIN
-- Choose to raise exceptions.
DBMS_LDAP.USE_EXCEPTION := TRUE;
-- Connect to the LDAP server.
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);
l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
dn => l_ldap_user,
passwd => l_ldap_passwd);
-- Get all attributes
l_attrs(1) := '*'; -- retrieve all attributes
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=*',
attrs => l_attrs,
attronly => 0,
res => l_message);
IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session,
msg => l_message);
<< entry_loop >>
WHILE l_entry IS NOT NULL LOOP
-- Get all the attributes for this entry.
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
<< attributes_loop >>
WHILE l_attr_name IS NOT NULL LOOP
-- Get all the values for this attribute.
l_vals := DBMS_LDAP.get_values (ld => l_session,
ldapentry => l_entry,
attr => l_attr_name);
<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;
l_attr_name := DBMS_LDAP.next_attribute(ld => l_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
END LOOP attibutes_loop;
l_entry := DBMS_LDAP.next_entry(ld => l_session,
msg => l_entry);
END LOOP entry_loop;
END IF;
-- Disconnect from the LDAP server.
l_retval := DBMS_LDAP.unbind_s(ld => l_session);
DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;
/
Oracle SQL Workshop to Bind with LDAP#
We show how to use Oracle SQL Workshop to Bind with LDAP[#1] - Largely gathered from http://www.oracle-base.com/Content unavailable! (broken link)https://ldapwiki.com/wiki/images/out.png