I’ve found myself needing to search LDAP data more often and each time I do it’s been sort of obnoxious to have to rewrite variations of mostly similar DBMS_LDAP routines. Frequently I then need to use that data in a SQL query of some sort to combine it with other data found in one or more of our Oracle databases.
So I thought it would be helpful if I could query the LDAP data via a single function. My initial thought was a pipelined table function; but that doesn’t work for multiple reasons. First, the row contents vary based on what you’re searching for so I couldn’t declare an object type to present distinct columns. Second, piping the results back would entail keeping a connection to the LDAP server open. While that’s not necessarily a show stopper, it would be an unusual usage and could certainly cause timeout problems if the consuming code had significant delays between reading the rows.
I also considered a polymorphic table function, but that too has problems because as you traverse an LDAP hierarchy each level can have different sets of attributes. So, in a SQL result set that would be like each row having a different set of columns. Also, I wanted the function to be usable with some older systems and using this would restrict it 18c and higher.
This led me to an XML or JSON type output. Choosing between XML and JSON, I went with JSON simply because it’s more compact, thus the output is smaller, so less data to transport. Native JSON support started in 11g and that was as far back as I felt reasonable to support; but even if I did find myself on an ancient 10g or older db I could still manually parse out JSON values without too much trouble if I had to.
Next I needed to decide what I wanted my output to look like. This I based mostly on the results of an LDAP search. Whether your search scope is BASE, ONE LEVEL, or SUB-TREE the results are all flat. That is, the hierarchy is not inherent in the output. Rather, it must be deciphered from the distinguished names of each entry.
For instance if I search the whole subtree of the zflexldap sample server I might get results like this:
o=company1,dc=zflexsoftware,dc=com ou=london,o=company1,dc=zflexsoftware,dc=com ou=usa,o=company1,dc=zflexsoftware,dc=com ou=group,ou=london,o=company1,dc=zflexsoftware,dc=com ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com ou=users,ou=london,o=company1,dc=zflexsoftware,dc=com ...
The results are not sorted by their distinguished names or by their hierarchy. Furthermore, other than reading the dn, there is no designation of which entries are under others. Since the results won’t be sorted coming out of LDAP, it will be necessary to do that within the SQL context. Distinguished Names can be a little tricky though since they are concatenated in an order that is not conducive for normal text-based sorting. However, by using an object type (ldap_dn source code is available at the end of article) with an ORDER member function we can handle DN-based sorting.
An example usage with the sample DNs from above:
SELECT LPAD(dn, 65)
FROM (SELECT 'o=company1,dc=zflexsoftware,dc=com' dn FROM DUAL
UNION ALL
SELECT 'ou=london,o=company1,dc=zflexsoftware,dc=com' FROM DUAL
UNION ALL
SELECT 'ou=usa,o=company1,dc=zflexsoftware,dc=com' FROM DUAL
UNION ALL
SELECT 'ou=group,ou=usa,o=company1,dc=zflexsoftware,dc=com' FROM DUAL
UNION ALL
SELECT 'ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com'
FROM DUAL
UNION ALL
SELECT 'ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com'
FROM DUAL
UNION ALL
SELECT 'ou=users,ou=london,o=company1,dc=zflexsoftware,dc=com' FROM DUAL)
ORDER BY ldap_dn(dn);
Left-padding the output makes it easier to see the sorting in action
LPAD(DN,65)
-----------------------------------------------------------------
o=company1,dc=zflexsoftware,dc=com
ou=london,o=company1,dc=zflexsoftware,dc=com
ou=users,ou=london,o=company1,dc=zflexsoftware,dc=com
ou=usa,o=company1,dc=zflexsoftware,dc=com
ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com
ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com
ou=group,ou=usa,o=company1,dc=zflexsoftware,dc=com
Once I had the entries coming back in SQL and I could sort them into their hierarchical order I next needed to decide how the attributes would be included. Since each entry could have any number of attributes a JSON array would accommodate them. Furthermore, each attribute can have more than one value, similar to a nested-table column within a database table, so that’s another array. Finally, I decided to include the search parameters that generated the results in the final output, so the final JSON object could be self-describing. The final format I settled on will be structured as one of the following, depending on the attribute search criteria specified.
{
"base": "...",
"scope": "...",
"filter": "...",
"attributes": ["...","..."],
"attributes_only": "N",
"results": [
{
"dn": "...",
"attributes": [
{
"objectclass": ["top","organization"]
},
{...}
]
},
{...}
]
}
Or, if the search specifies an attributes only result (i.e. names, but not values) then the attributes array will consist of an array of json strings rather than array of json objects.
{
"base": "...",
"scope": "...",
"filter": "...",
"attributes": ["...","..."],
"attributes_only": "Y",
"results": [
{
"dn": "...",
"attributes": ["...","..."]
},
{...}
]
}
And finally if the search specifies that no attributes will be returned the attribute arrays will be excluded entirely, as will the attributes_only key/value which, obviously, won’t apply.
{
"base": "...",
"scope": "...",
"filter": "...",
"attributes": ["1.1"],
"results": [
{"dn": "..."},
{"dn": "..."}
]
}
If an error is encountered, the function will still return but will include an “error” key/value pair, for example:
{
"base": "o=company1,dc=zflexsoftware,dc=com",
"scope": "subtree",
"filter": "objectclass=*",
"attributes": "1.1",
"error": "ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials"
}
The structures above are formatted for easier reading. The actual CLOB values will be a single line without extraneous white space. It is generated by the TO_CLOB member function of the pl/sql JSON_OBJECT_T type.
The search criteria correspond to the base, scope, filter, attrs, and attronly parameters of the DBMS_LDAP.search_s function and are reflected in the ldap_to_json (source code found at end of this article) parameters as well.
CREATE OR REPLACE FUNCTION ldap_to_json(
p_host IN VARCHAR2,
p_base IN VARCHAR2,
p_scope IN VARCHAR2,
p_filter IN VARCHAR2,
p_attributes IN VARCHAR2 DEFAULT '*',
p_attributes_only IN VARCHAR2 DEFAULT NULL,
p_port IN NUMBER DEFAULT DBMS_LDAP.port,
p_user_dn IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL)
RETURN CLOB
- P_HOST – the host name or ip address of the LDAP server
- P_BASE – the portion of the LDAP tree that will be searched
- P_SCOPE – ‘BASE’, ‘ONELEVEL’, or ‘SUBTREE’ (not case sensitive), it will also accept the dbms_ldap numeric values corresponding to those scopes ‘0’,’1′, or ‘2’ respectively.
- P_FILTER – LDAP syntax for search criteria, essentially the “WHERE clause” looking for entries with certain attributes, (note the values of the attributes is not part of the search criteria)
- P_ATTRIBUTES – the list of attributes (defaults to all) that will be returned from matching entries. Recognizes the LDAP special values ‘*’, ‘+’, and ‘1.1’. If names are needed they should be passed in as a json array text regardless of the number of names passed in (e.g. ‘[“uid”]’ or ‘[“objectclass”,”ou”,”cn”]’)
- P_ATTRIBUTES_ONLY – determines if the attribute values will be returned, or just the attribute names. If the P_ATTRIBUTES value is special LDAPvalue ‘1.1’, then this parameter is not used.
- P_PORT – specifies the LDAP server port
- P_USER_DN – if the LDAP server requires binding (authentication) the user DN of the bind
- P_PASSWORD – the password for binding with the specified user DN
Sample Usage with an anonymous connection
Here I’m querying against an LDAP server used for Oracle TNS service name lookup.
SELECT *
FROM JSON_TABLE(
ldap_to_json(p_host => 'myldapserver',
p_base => 'cn=OracleContext,dc=sds,dc=home',
p_scope => 'onelevel',
p_filter => 'objectclass=*',
p_attributes => '["cn","orclNetDescString"]'),
'$'
COLUMNS(
NESTED PATH '$.results[*].attributes'
COLUMNS(
tns_service_name PATH '$.cn[0]',
connection_description PATH '$.orclNetDescString[0]')))
jt
WHERE tns_service_name LIKE 'test%';
TNS_SERVICE_NAME CONNECTION_DESCRIPTION
-------------------- -----------------------------------------------------------------------------------------------------------------------------
test (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=mydbserver) (PORT=1521)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=test)))
testxe (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
Sample Usage with authentication (binding)
This example uses the public ZFLEXLDAP test server with one of their sample logins. I’m viewing the results as just the full json text, not parsing the fields into rows and columns
SELECT ldap_to_json(
p_host => 'www.zflexldap.com',
p_user_dn => 'cn=ro_admin,ou=sysadmins,dc=zflexsoftware,dc=com',
p_password => 'zflexpass',
p_base => 'o=company1,dc=zflexsoftware,dc=com',
p_scope => 'subtree',
p_filter => 'objectclass=*'
)
FROM DUAL
Results (after formatting and truncating):
{
"base": "o=company1,dc=zflexsoftware,dc=com",
"scope": "subtree",
"filter": "objectclass=*",
"attributes": "*",
"attributes_only": "N",
"results": [
{
"dn": "o=company1,dc=zflexsoftware,dc=com",
"attributes": [
{
"objectclass": [
"top",
"organization"
]
},
{
"o": [
"company1"
]
}
]
},
{
"dn": "ou=london,o=company1,dc=zflexsoftware,dc=com",
"attributes": [
{
"ou": [
"london"
]
},
{
"objectclass": [
"top",
"organizationalUnit"
]
}
]
},
... results truncated
{
"dn": "uid=consultant_tony,ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com",
"attributes": [
{
"objectClass": [
"top",
"person",
"organizationalPerson",
"inetOrgPerson"
]
},
{
"givenName": [
"tony"
]
},
{
"uid": [
"consultant_tony"
]
},
{
"sn": [
"mcc"
]
},
{
"cn": [
"tony mcc"
]
}
]
}
]
}
Sample Usage with authentication, DN return, attribute extraction, and DN-sorted output
SELECT LPAD(dn, 90) dn, givenname, sn
FROM JSON_TABLE(
ldap_to_json(
p_host => 'www.zflexldap.com',
p_user_dn => 'cn=ro_admin,ou=sysadmins,dc=zflexsoftware,dc=com',
p_password => 'zflexpass',
p_base => 'o=company1,dc=zflexsoftware,dc=com',
p_scope => 'subtree',
p_filter => 'objectclass=*',
p_attributes => '["givenName","sn"]'),
'$'
COLUMNS(
NESTED PATH '$.results[*]'
COLUMNS(
dn VARCHAR2(100) PATH '$.dn',
givenname VARCHAR2(10) PATH '$.attributes.givenName[0]',
sn VARCHAR2(10) PATH '$.attributes.sn[0]'))) jt
WHERE givenname IS NOT NULL
ORDER BY ldap_dn(dn);
DN GIVENNAME SN
------------------------------------------------------------------------------------------ ---------- ----------
uid=consultant_Bob,ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com Bobby Greenatta
uid=consultant_Ted,ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com Teddy Johnson
uid=consultant_Mike,ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com Mike Fisnessa
uid=consultant_tony,ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com tony mcc
uid=consultant_Sathish,ou=users,ou=consultants,ou=usa,o=company1,dc=zflexsoftware,dc=com Sathish Kumar
uid=staff_Joe,ou=users,ou=staff,ou=usa,o=company1,dc=zflexsoftware,dc=com Joe Smith
uid=staff_susan,ou=users,ou=staff,ou=usa,o=company1,dc=zflexsoftware,dc=com Susan Mills
uid=staffmember1,ou=users,ou=staff,ou=usa,o=company1,dc=zflexsoftware,dc=com Joanna Barns
The LDAP_TO_JSON function
CREATE OR REPLACE FUNCTION ldap_to_json(
p_host IN VARCHAR2,
p_base IN VARCHAR2,
p_scope IN VARCHAR2,
p_filter IN VARCHAR2,
p_attributes IN VARCHAR2 DEFAULT '*',
p_attributes_only IN VARCHAR2 DEFAULT NULL,
p_port IN NUMBER DEFAULT DBMS_LDAP.port,
p_user_dn IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL)
RETURN CLOB
IS
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- Translate scope names to dbms_ldap scope numbers
-- if given valid scope numbers, use them
-- If no match, then treat as a BASE scope
v_scope NUMBER
:= CASE UPPER(TRIM(p_scope))
WHEN 'BASE' THEN 0
WHEN 'ONELEVEL' THEN 1
WHEN 'SUBTREE' THEN 2
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
ELSE 0
END;
-- If the requested search attributes are null
-- treat it as the wildcard for everything
v_attributes VARCHAR2(32767) := NVL(p_attributes, '*');
-- Translate Y or 1 into Boolean TRUE, all else false
v_attributes_only BOOLEAN
:= UPPER(TRIM(NVL(p_attributes_only, 'N'))) IN ('Y', '1');
v_error_code PLS_INTEGER;
v_session DBMS_LDAP.session;
v_search_attrs DBMS_LDAP.string_collection;
v_search_results DBMS_LDAP.MESSAGE;
v_entry DBMS_LDAP.MESSAGE;
v_distinguished_name VARCHAR2(256);
v_attribute_name VARCHAR2(256);
v_attribute_values DBMS_LDAP.string_collection;
v_ber DBMS_LDAP.ber_element;
v_out json_object_t := json_object_t;
v_json_attributes json_array_t;
v_json_key_value json_object_t;
v_json_values json_array_t;
v_json_entries json_array_t := json_array_t();
v_json_entry json_object_t := json_object_t;
BEGIN
BEGIN
-- Create a header for the results that defines the search critera.
v_out.put('base', p_base);
v_out.put('scope', p_scope);
v_out.put('filter', p_filter);
CASE
WHEN v_attributes IN ('*', '+', '1.1')
THEN
-- LDAP special values
-- * Return all user attributes
-- + Return all operational attributes
-- 1.1 Return NO attributes
v_search_attrs(1) := v_attributes;
v_out.put('attributes', v_attributes);
ELSE
-- If not one of the LDAP special values
-- then parse it as a JSON array
v_json_attributes := json_array_t(v_attributes);
FOR i IN 0 .. v_json_attributes.get_size - 1
LOOP
v_search_attrs(i) := v_json_attributes.get_string(i);
END LOOP;
v_out.put('attributes', v_json_attributes);
END CASE;
IF v_attributes != '1.1'
THEN
-- If the no attributes are being returned the
-- attributes only parameter is irrelevant
v_out.put('attributes_only', p_attributes_only);
END IF;
-- The LDAP return codes will all be 0 for success
-- If an error occurs, the functions will raise an exception
-- rather than return an error code.
DBMS_LDAP.use_exception := TRUE;
-- Connect to LDAP server
v_session := DBMS_LDAP.init(hostname => p_host, portnum => p_port);
IF p_user_dn IS NOT NULL
THEN
-- If a username is provided then
-- authenticate ("bind" in LDAP jargon)
v_error_code :=
DBMS_LDAP.simple_bind_s(ld => v_session,
dn => p_user_dn,
passwd => p_password);
END IF;
-- Conduct search
-- Note, DBMS_LDAP does not support paginated results
-- So if the result is too big a SIZELIMIT_EXCEEDED error will be raised
v_error_code :=
DBMS_LDAP.search_s(
ld => v_session,
base => p_base,
scope => v_scope,
filter => p_filter,
attrs => v_search_attrs,
attronly => CASE WHEN v_attributes_only THEN 1 ELSE 0 END,
res => v_search_results);
-- Loop through entries until they run out (NULL reached)
v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);
WHILE v_entry IS NOT NULL
LOOP
-- Create a JSON object for this entry
-- Identify it with a key value of its distinguished name
v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
v_json_entry := json_object_t();
v_json_entry.put('dn', v_distinguished_name);
-- Create a new array to hold the attributes, with or without values
v_json_attributes := json_array_t();
v_attribute_name :=
DBMS_LDAP.first_attribute(v_session, v_entry, v_ber);
-- Loop through entries until they run out (NULL reached)
IF v_attribute_name IS NOT NULL
THEN
WHILE v_attribute_name IS NOT NULL
LOOP
CASE
WHEN v_attributes_only
THEN
-- Add the name of this attribute to the array
v_json_attributes.append(v_attribute_name);
ELSE
-- Attributes can have multiple values
-- If the values are being returned create an array
-- to hold them.
-- Then loop through the values appending to array.
v_attribute_values :=
DBMS_LDAP.get_values(v_session,
v_entry,
v_attribute_name);
v_json_values := json_array_t();
IF v_attribute_values.COUNT > 0
THEN
FOR i IN v_attribute_values.FIRST ..
v_attribute_values.LAST
LOOP
v_json_values.append(v_attribute_values(i));
END LOOP;
END IF;
-- Create a JSON object to hold the array
-- with a key of the attribute's name
v_json_key_value := json_object_t();
v_json_key_value.put(v_attribute_name,
v_json_values);
-- Append the key/array object
-- to the array of attributes
v_json_attributes.append(v_json_key_value);
END CASE;
v_attribute_name :=
DBMS_LDAP.next_attribute(v_session, v_entry, v_ber);
END LOOP;
DBMS_LDAP.ber_free(v_ber, 1);
-- Append the attributes to the entry
v_json_entry.put('attributes', v_json_attributes);
END IF;
-- Append the completed JSON object for this LDAP entry
-- to the array of search results
v_json_entries.append(v_json_entry);
v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
END LOOP;
-- Unbind closes the session with the LDAP server
v_error_code := DBMS_LDAP.unbind_s(v_session);
-- Append the entries to the result header
v_out.put('results', v_json_entries);
EXCEPTION
WHEN OTHERS
THEN
-- If something went wrong include the error in the output
-- and try to clean up
v_out.put('error', SQLERRM);
BEGIN
v_error_code := DBMS_LDAP.unbind_s(v_session);
EXCEPTION
WHEN DBMS_LDAP.invalid_session
THEN
-- If the session isn't valid, then nothing to unbind
-- So it's safe to ignore
NULL;
WHEN OTHERS
THEN
-- If the clean up itself failed then
-- report that in the output as well
v_out.put('unbind_error', SQLERRM);
END;
END;
-- Return the output as JSON text
RETURN v_out.TO_CLOB();
END;
The LDAP_DN object
CREATE OR REPLACE TYPE ldap_dn AS OBJECT
(
-- Depending on the LDAP server implementation
-- the length of the DN might be limited to a few hundred bytes
-- or unlimited, or some where in between.
dn VARCHAR2(32767),
ORDER MEMBER FUNCTION sorter(p_obj IN ldap_dn)
RETURN INTEGER
);
/
CREATE OR REPLACE TYPE BODY ldap_dn
AS
ORDER MEMBER FUNCTION sorter(p_obj IN ldap_dn)
RETURN INTEGER
IS
v_index INTEGER := 0;
v_diff INTEGER := 0;
self_elements DBMS_LDAP.string_collection;
other_elements DBMS_LDAP.string_collection;
-- As with the DN in its entirety
-- individual elements of the DN may be limited
-- to as low as a few dozen characters
-- or a couple thousand or unlimited
self_val VARCHAR2(32767);
other_val VARCHAR2(32767);
BEGIN
self_elements := DBMS_LDAP.explode_dn(dn, 0);
other_elements := DBMS_LDAP.explode_dn(p_obj.dn, 0);
-- Walk backwards from the end of each dn
-- Compare each element until one differs
-- or until we run out of elements in at least one of them
WHILE v_diff = 0
AND v_index < LEAST(self_elements.COUNT, other_elements.COUNT)
LOOP
self_val := self_elements(self_elements.COUNT - v_index - 1);
other_val := other_elements(other_elements.COUNT - v_index - 1);
v_diff :=
CASE
WHEN self_val > other_val THEN 1
WHEN self_val = other_val THEN 0
ELSE -1
END;
v_index := v_index + 1;
END LOOP;
-- If all elements match to this point
-- then whichever DN has fewer should sort first
IF v_diff = 0
THEN
v_diff := self_elements.COUNT - other_elements.COUNT;
END IF;
RETURN v_diff;
END;
END;
/
Some possible future enhancements I’d like to explore:
- Add support for wallets to enable SSL communication
- Put the function in a package and overload it with parameters to take a dbms_ldap.string_collection for the attributes list instead of parsing the inputs and generating the collection internally
- Put it in a package with XML formatted output
- If a list of attributes is specified or no attributes is specified then the issues of the polymorphic table function are ameliorated, making a direct ldap to rows/columns possible.
I hope you find this useful and I welcome suggestions. I’m always happy to share content that I think might make someone else’s job a little easier and even more so to publish this article in tribute to Joel Kallman, a man I admired and definitely miss for his charm, technical proficiency, and of course, his passion for building a community around Oracle products.