8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_LDAP - Accessing LDAP From PL/SQL
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.
- Create DBMS_LDAP
- Connect And Authenticate
- Search Directory
- Loop Through Entries
- Loop Through Attributes
- Loop Through Values
- Put It All Together
- LDAPS Support
Create DBMS_LDAP
If the DBMS_LDAP package is not loaded, you can install it by running 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 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 Entries
The search returns a list of entries which can be looped through.
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;
/
LDAPS Support
The DBMS_LDAP package support LDAP over SSL (LDAPS). In order to use this you will get the certificates from the LDAPS servers and load them into an Oracle Wallet (as described here), then open the wallet in your code using the OPEN_SSL function call between the INIT and SIMPLE_BIND_S calls. The extract of code below shows how this should look.
-- Connect to the LDAP server.
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);
-- Open Wallet for SSL.
-- 1 = No Authentication.
-- 2 = One-way authentication.
-- 3 = Two-way authentication.
l_retval := DBMS_LDAP.open_ssl (ld => l_session,
sslwrl => 'file:/path/to/wallet',
sslwalletpasswd => 'WalletPassword',
sslauth => 2);
l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
dn => l_ldap_user,
passwd => l_ldap_passwd);
For more information see:
Hope this helps. Regards Tim...