LDAP directory servers are all the rage, but many applications still rely on their own credentials tables, which are difficult to maintain in synch with the central directory. APIs, whether they are Java, C or PL/SQL are less than obvious and strongly on the 3GL side of programming. This paper shows how you can seamlessly integrate data from your LDAP server into the relational world, thanks to almost unknown features available since Oracle8i. They allow us to map what looks like an ordinary view upon the result of classic LDAP queries.
The idea of directory servers already dates back some time but it really began to bite in the first half of the 1990s when the Lightweight (as opposed to the heavyweight X500 standard) Directory Access Protocol emerged at the University of Michigan.
Since then, this Open Standard has gained momentum and universal acceptance. Everybody offers some LDAP implementation, from the free OpenLDAP (http://www.openldap.org) to Netscape, SUN, Lotus, Novell, Microsoft – in that case, Active Directory, LDAP with Microsoft characteristics … - and, quite obviously, Oracle, who have jumped on the bandwagon.
In the Oracle case, LDAP has even tolled the death of Oracle*Names which it replaces.
This paper is based on practical experience gained at l’Oréal Recherche, the research center of the cosmetics group, on interfacing existing Oracle applications with the newly implemented pilot LDAP directory.
L’Oréal Recherche has been an Oracle customer since 1983, and over the years almost all techniques have been used : Unix and NT have replaced VMS, the Pro*Fortran programs of yore have quickly been followed by SQL*Forms applications, then client-server applications developed mainly with PowerBuilder, and today all new applications are Intranet applications. Quite obviously, deciding to rewrite an old application is a daunting task, and at least some specimens of all periods are still in operation in one way or another today. What most applications have in common is security : in a highly competitive research environment, security is very tight and the privileges of users (mainly chemists) are precisely defined by the lab they belong to and the business line they are working for. Reference tables hold the privileges, and slowly each application began to use its own set of reference tables – which was not the most manageable. Recently, it has been decided to set up a proper referential – and to use a directory server to store it. This has been tantamount to a Copernician revolution : instead of having new users or transfers from one lab to another being recorded in the usual Oracle tables, they were to be recorded in a directory server, using a dedicated directory manager! But what of existing applications ? Imagine EMP and DEPT being stored outside Oracle, and you will have a proper idea of the situation.
A directory server is, at its simplest, a dedicated program listening for requests on a special port and answering those requests by searching a database. This ‘database’ may be hardly more than a glorified file. Quite obviously, a directory is not a place where thousands of updates occur per minute. Locking and write concurrency are very minor concerns. What counts is to be able to provide a quick answer to the request – which several products do quite decently. You can use Oracle, of course, but you don't have to.
What is peculiar in the LDAP model – at least to people who have been nurtured in the relational world – is that the LDAP model looks like a hierarchical database of old. A typical LDAP directory might have been implemented as follows :
Boxes indicate ‘object classes’ and there are several widely used object classes such as those above. Typically, a user will be known by his or her ‘common name’ (traditionally abbreviated as cn), but the real identifier in the LDAP world will be the ‘distinguished name’ or dn which lists the path from the node under scrutiny towards the top of the tree. For instance, Clark’s dn will be in the example above :
cn=Brian J Clark, ou=accounting, o=xyzcorp, c=US
This identifier, as anybody who has any experience of relational databases will know, is far from ideal, since if Clark were to abandon accounting in favour of something less creative but more respectable such as sales, his identifier would change. Heresy! Which is why one encounters more and more often ‘flatter’ hierarchies such as the following one, which are much closer in spirit to the relational model (and to the internet) :
The techniques explained below do not depend on the structure of the LDAP directory. To drive the point home, we shall use in our examples the former structure, which is the furthest removed from the relational model.
The two following screen-shots show how EMP and DEPT could look once loaded into a directory. Quite obviously, some information such as HIREDATE and SAL have disappeared, since this is typically not what you would store into an enterprise-wide directory, while department addresses and phone numbers and employee’s first name and e-mail address are now included :
LDAP BROWSER/EDITOR
© Jarek Gawor &
Gregor von Laszewski
Note that some attributes, such as ‘ou’ which might be considered the identifier of the department, are multi-valued attributes – ‘Sales’ and the department number, 30, are in effect defined as synonyms. However, in the left most part of the screen, ‘Sales’ appear because the distinguished name specifies ou=Sales,o=xyzcorp,c=US – and not ou=30,o=xyzcorp,c=US.
LDAP BROWSER/EDITOR
© Jarek Gawor &
Gregor von Laszewski
In a similar way, employees can have several, logically equivalent, ‘common names’ (cn), but what appears in the distinguished name is given preminence. Notice also that managers are specified by their distinguished name, not by their employee number.
How does one execute a query against a LDAP server? Short of using a graphical tool such as the Java LDAP browser/editor shown above, the steps, in whichever language you use are as follows :
q Create a session (init), which physically connects to the directory server
q Authenticate (bind)
q Issue a search query (search), which specifies a filter and how deep you search the tree
q Loop on the result set (first_entry, get_dn, next_entry)
q Disconnect (unbind)
Oracle provides the DBMS_LDAP PL/SQL package, which implements the function listed above. This package has some limitations (addressed below), but it’s an easy starting point. Coding with this package, while not being nightmarish, is not entirely user-friendly. Better to take the demo search program provided by Oracle :
$ORACLE_HOME/ldap/demo/plsql/search.sql
and adapt it. Once modified to make it less verbose, and to query only some attributes of employees (the inetOrgPerson object class) instead of all attributes of all entities in the directory, we can run, under SQL*Plus, the new modified script (named searchemp.sql – listing given in Appendix A) and here is what we get :
DBMS_LDAP Search Example simple_bind_s Returns : 0
search_s Returns : 0
LDAP message : 8CC35440(returned from search_s)
Number of Entries : 14
---------------------------------------------------
dn: cn=Charles A King,ou=Accounting,o=xyzcorp,c=US
sn : King
ou : 10
employeeNumber : 7839
===================================================
dn: cn=Brian J Clark,ou=Accounting,o=xyzcorp,c=US
sn : Clark
manager : cn=Charles A King,ou=Accounting,o=xyzcorp,c=US
ou : 10
employeeNumber : 7782
===================================================
[snip]
===================================================
dn: cn=Mary S Adams,ou=Research,o=xyzcorp,c=US
sn : Adams
manager : cn=William C Scott,ou=Research,o=xyzcorp,c=US
ou : 20
employeeNumber : 7876
===================================================
unbind_res Returns : 0
Directory operation Successful .. exiting
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.25
No problem for accessing the directory, but what of our existing applications and their queries against EMP and DEPT? Shall we have to recode everything using this awful PL/SQL package ?
The object option of Oracle allows you to paste what looks like an ordinary view above in-memory PL/SQL structures – a bit like Oracle’s V$ dynamic views which are the representation as views of memory structures, hence the name of ‘do-it-yourself dynamic views’ (DIY$ views for short). The principles of DIY$ views have been presented by this author and Robert Goral in an article published in the Online edition of Oracle Magazine. The steps are pretty simple :
create type LDAP_EMP_ROW
as object (common_name varchar2(50),
ename varchar2(20),
mgrname varchar2(50),
deptno number,
empno number);
/
create type LDAP_EMP_TABLE is table of LDAP_EMP_ROW;
/
The full function listing is given in Appendix B.
create or replace view LDAP$EMP
As select *
from Table( Cast( GET_LDAP_EMP() As LDAP_EMP_TABLE ));
SQL> desc ldap$emp
Name Null? Type
----------------------------------------- -------- -------------------------
COMMON_NAME VARCHAR2(50)
ENAME VARCHAR2(20)
MGRNAME VARCHAR2(50)
DEPTNO NUMBER
EMPNO NUMBER
SQL> select ename from ldap$emp 2 order by 1 3 /
ENAME -------------------- ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD
14 rows selected.
Elapsed: 00:00:00.20
Note that in LDAP$EMP the manager is identified by his or her ‘common name’ – not the employee number as in everybody’s favourite EMP table. Well, this is no problem, we can create another view above LDAP$EMP :
SQL> create or replace view v_ldapemp
2 as select e.empno, e.ename, m.empno mgr, e.deptno
3 from ldap$emp e,
4 ldap$emp m
5* where e.mgrname = m.common_name(+)
SQL> /
View created.
Elapsed: 00:00:00.11 SQL> select * from v_ldapemp 2 /
EMPNO ENAME MGR DEPTNO ---------- -------------------- ---------- ---------- 7788 SCOTT 7566 20 7934 MILLER 7782 10 7782 CLARK 7839 10 7902 FORD 7839 20 7698 BLAKE 7839 30 7566 JONES 7839 20 7654 MARTIN 7698 30 7844 TURNER 7698 30 7900 JAMES 7698 30 7521 WARD 7698 30 7499 ALLEN 7698 30 7369 SMITH 7902 20 7876 ADAMS 7788 20 7839 KING 10
14 rows selected.
Elapsed: 00:00:00.43
Et voilà. Any change in the directory server will be immediately visible through the views.
Contrarily to Oracle’s own V$ views, which map shared memory data structures, do-it-yourself views map user session memory; for one thing, it means that we do not have to issue a LDAP query each time we select from the LDAP$EMP view. If we package GET_LDAP_EMP (instead of using a standalone function) we can perfectly well use flags and query the directory server only the first time we reference the view – then directly return what we have in store.
Another, and most important point, is that since a session holds its own copy, it is totally impractical in a large organization to proceed exactly as stated above, if it means that each employee holds his/her own full in-memory copy of the contents of the LDAP server.
In the real world, we basically have the choice between two implementations :
It is extremely easy to modify GET_LDAP_EMP() to make it use a filter which returns only information relevant to the current session (such as access rights). One can use the pseudo column USER, or anything else. In the following example we use information set as CLIENT_INFO by a call to the DBMS_APPLICATION_INFO package. We add this to the code :
v_myname := initcap(sys_context('USERENV', 'CLIENT_INFO'));
and slightly modify the search criterion :
retval := DBMS_LDAP.search_s(my_session, ldap_base,
DBMS_LDAP.SCOPE_SUBTREE,
'(&(objectclass=inetOrgPerson)(sn='
|| v_myname || '))',
my_attrs,
0,
my_message);
Here is the result in practice (we have called the view built upon the modified function USER$LDAP$EMP by analogy with the Oracle dictionary views) :
SQL> exec dbms_application_info.set_client_info('SCOTT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00 SQL> select ename, empno 2 from user$ldap$emp;
ENAME EMPNO -------------------- ---------- SCOTT 7788
Elapsed: 00:00:00.05
SQL> exec dbms_application_info.set_client_info('WARD');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01 SQL> select ename, empno 2 from user$ldap$emp;
ENAME EMPNO -------------------- ---------- WARD 7521
Elapsed: 00:00:00.03
The other solution is to use the equivalent of snapshots – to have a single session which updates tables through simple INSERT … SELECT … which query the DIY$ views. This session doesn't need to do a ‘full refresh’ each time. If entries are not physically deleted but simply flagged as deleted, it’s just a matter of storing somewhere when a refresh takes place, and to use this date as part of the filter (all LDAP entries have a ‘modifytimestamp’ attribute). Instead of viewing everything, one can view only what has been modified since the latest refresh.
Transforming a basically hierarchical structure into what looks like relational tables is something which must be done carefully. There are basically two aspects to the question :
It’s obvious in the example we have taken, since the contents of DEPT should be obtained by using the filter ‘objectclass=OrganizationalUnit’. The difficulty is more about defining proper foreign keys – typically, relating employees to departments. Our example has used an ‘OrganizationalUnit’ tag (ou=) containing the DEPTNO and defined as an attribute of each employee entry. However, this is somewhat redundant since the distinguished name for each employee contains, in this case, the identification of the department he or she belongs to. In other words, our columns may contain standard attributes from the object class, but also elements from a broken down distinguished name.
The screen-shots above show that a same attribute (the common name or cn of employees for instance) can occur several times. This means that to have a proper relational design one should create a derived table, using the key of the entry, and having one row per possible value of the attribute per key. The obvious choice for the key is the distinguished name – however it is a poor one, since this rather unappetizing string sits oddly as the key of a relational table. It is often much better to use an attribute as the key.
Another way to handle multi-valued attributes would be of course, to use the object option of Oracle more intensely than as a convenient way to map views on memory structures – but this is an avenue which we have not explored.
In fact we have a wider choice of implementation than the Oracle LDAP PL/SQL APIs from the DBMS_LDAP package. For one thing, this package had one major snag : the version then available was incompatible with multi-threaded servers. (David Saslav, Principal Product Manager for Oracle Internet Directory has been kind enough to provide, since this paper was first published, the following information concerning the status of the DBMS_LDAP package regarding MTS - click here)
The essence of the interface is basically to fill PL/SQL structures with information queried from the directory server. There are, however, possibilities other than the DBMS_LDAP package to do this.
Many Java classes to access a LDAP server are available.
An external C procedure may be a bit difficult to use in this context, especially since the natural way to query the directory server is to do it repetitively until it returns nothing. External C procedures, which are dynamically loaded into memory, lack persistence between calls. There is however, another way to use C, and this is how it has been implemented at l’Oréal : The idea is to create a daemon program which listens on an Oracle pipe (DBMS_PIPE). Instead of directly querying the LDAP server, the GET_LDAP_EMP function sends a ‘get me emp’ message down the pipe. The daemon gets the message, and, obligingly, queries the LDAP server using C functions (the OpenLDAP library was used at l’Oréal). It then prepares each ‘row’ as a single string, with all column values separated by a special character, and shovels it into the pipe. At the other end, the function only has to split the string into its basic components and fill the PL/SQL structures. The daemon can even act as a cache for the LDAP server.
The main production databases of l’Oréal Recherche are accessed by a respectable number of concurrent users, and multi-threaded servers are used, which prevents calling the DBMS_LDAP functions and procedures. As stated above, the LDAP access has been implemented as a daemon service, which has been used to refresh tables which are snapshots of the LDAP directory – or at least, part of it. The structure of the LDAP directory is of the ‘flat’ kind, which means close in spirit to the relational model, and the contents is used to refresh 11 tables – the way access rights are defined are rather complex. The daemon architecture requires some specific C code (defining the search criteria, attributes wanted and various filters) and some specific SQL code (types, functions and views) to be written for each of the 11 tables – which would have been very tedious indeed without the help of a code generator.
A (C) code generator has been used to assist with the boring bit, namely the specific code. Its interface is a bit rough at the edges, but it does exactly what it is meant to do. It connects to the LDAP directory, lists the structure, lets the user define which ObjectClass has to be mapped to a DIY$ view, which attribute will appear as a column and under which name. Some basic transformations can also be defined, and the case of derived tables (with multi-valued attributes) is handled automatically. This is an example of how it is used, once you have selected the ObjectClass to work on (SKILLTAG is the only selected multi-valued attribute) :
Enter the # of column to modify (E to exit): 10 SQL name [nothing: remove from view/=: no change] :<CR> LDAP attribute SQL name Key Condition ----------------------- ------------------------ --- ---------------- 1 Distinguished name -- not in view -- n 2 uid USER_ID Y 3 sn -- not in view -- n 4 cn USER_NAME n 5 tag TAG n 'RADPPC.'||USER 6 givenname GIVENNAME n 7 orgunittag ORGUNITTAG n 8 *skilltag SKILL n 9 telephonenumber TELEPHONENUMBER n 10 labeleduri -- not in view -- n Enter the # of column to modify (E to exit): 9 SQL name [nothing: remove from view/=: no change] : PHONE Belongs to key [Y/N] : n Condition [nothing: none/=: no change] : <CR> LDAP attribute SQL name Key Condition ----------------------- ------------------------ --- ---------------- 1 Distinguished name -- not in view -- n 2 uid USER_ID Y 3 sn -- not in view -- n 4 cn USER_NAME n 5 tag TAG n 'RADPPC.'||USER 6 givenname GIVENNAME n 7 orgunittag ORGUNITTAG n 8 *skilltag SKILL n 9 telephonenumber PHONE n 10 labeleduri -- not in view -- n Enter the # of column to modify (E to exit): E *** Code generated for LDAP$USERS Please enter identifier for derived view LDAP$ [SKILL_USERS]: <CR> *** Code generated for LDAP$SKILL_USERS Item on which you want to build a view : <CR>
This program has allowed us to set up everything in place very quickly and its development has proved to be, in terms of time spent, an excellent investment.
However, as always with what is generated automatically, there is a downside : flexibility. Automatically generated views are the close image of what the directory server contains – but a little too close. Typically, constraints linked to the directory management tool, such as the necessity to prefix numerically identified entities of different nature with something specific of the entity were causing the data to be almost-exactly-but-not-quite what was expected to be found in tables.
This is why a second set of views has been set-up over the DYI$ views, mainly to ‘cook’ the raw output from the directory server. This has allowed us to, mainly, remove prefixes, reintegrate some special characters that the directory management program didn’t allow us to input, switch a ‘en’ language indicator into the ‘us’ one which was expected in the Oracle tables, and do a join with a transcoding table. This second set of views also allowed us, unexpectedly, to provide a very easy workaround for a number of minor bugs in the directory manager program.
The refresh of the Oracle tables from the LDAP server is executed by a cron job. This job starts the daemon program and stops it when it is done. Both the database (8.1.7) and the LDAP directory (iPlanet) run on the same machine, a Compaq Alpha running Tru64. A simple SQL script compares the contents of the Oracle table to the contents of the DIY$ views, and updates as required. Eleven tables are updated by the process, six of them are small reference tables with 100 rows or less, about 500 l’Oréal R&D employees are currently referenced in one table, and their privileges are stored in the four remaining tables, which hold each between 650 and 1,000 rows. All in all, about 3,250 rows can be affected. The refresh takes between 20 and 40s to run, depending on the load on the machine. When new applications get into production (which usually means many creations of new privileges) it is scheduled every 20mn.
The techniques presented in this paper have enabled extremely smooth implementation of the directory service, and with a minimum impact on the numerous existing applications. In fact, the directory server became the new reference at the same time as a major application rewrite went into production – and with almost no hiccough, but for a few bugs in the directory manager which were easily worked around thanks to the ‘double layer’ of views. Quite obviously, first generating the LDAP to Oracle tables mapping was no easy task and would have been extremely tedious without the code generator. But after the initial effort, the result is a view of the directory server which any normally competent SQL programmer can work with. The implementation is still ‘work in progress’, there will be no doubt a number of improvements in the near future. But it works, and it works well.
------------------------------------------------------------------------
-- $Header: $
--
-- Copyright (c) Oracle Corporation 2000. All Rights Reserved.
-- Modified by S Faroult, Oriole Ltd, 2002
--
-- FILE
-- searchemp.sql: A sample search program using DBMS_LDAP
--
-- DESCRIPTION
--
-- This SQL file contains the PL/SQL code required to perform
-- a typical search against an LDAP server.
--
-- MODIFIED (MM/DD/YY)
-- akolli 07/21/00 - created
------------------------------------------------------------------------
set serveroutput on size 30000
DECLARE
retval pls_integer;
my_session DBMS_LDAP.SESSION;
my_attrs DBMS_LDAP.STRING_COLLECTION;
my_message DBMS_LDAP.MESSAGE;
my_entry DBMS_LDAP.MESSAGE;
entry_index pls_integer;
my_dn varchar2(256);
my_attr_name varchar2(256);
my_ber_elmt DBMS_LDAP.BER_ELEMENT;
attr_index pls_integer;
i pls_integer;
my_vals DBMS_LDAP.STRING_COLLECTION ;
ldap_host varchar2(256);
ldap_port varchar2(256);
ldap_user varchar2(256);
ldap_passwd varchar2(256);
ldap_base varchar2(256);
BEGIN
retval := -1;
-- Customized variables
ldap_host := NULL; -- Oracle and ldap running on the same server
ldap_port := '389';
ldap_user := 'cn=Manager,o=xyzcorp,c=US';
ldap_passwd:= 'secret';
ldap_base := 'o=xyzcorp,c=US';
-- end of customizable settings
DBMS_OUTPUT.PUT('DBMS_LDAP Search Example ');
--
-- Choosing exceptions to be raised by DBMS_LDAP library.
--
DBMS_LDAP.USE_EXCEPTION := TRUE;
my_session := DBMS_LDAP.init(ldap_host,ldap_port);
-- bind to the directory
retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);
DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': '
|| TO_CHAR(retval));
--
-- issue the search
-- my_attrs(1) := '*'; would retrieve all attributes
-- We just want the employees (objectclass=inetOrgPerson)
-- and only SOME of their attributes
--
my_attrs(1) := 'EmployeeNumber';
my_attrs(2) := 'sn';
my_attrs(3) := 'ou';
my_attrs(4) := 'Manager';
retval := DBMS_LDAP.search_s(my_session, ldap_base,
DBMS_LDAP.SCOPE_SUBTREE,
'objectclass=inetOrgPerson',
my_attrs,
0,
my_message);
DBMS_OUTPUT.PUT_LINE(RPAD('search_s Returns ',25,' ') || ': '
|| TO_CHAR(retval));
DBMS_OUTPUT.PUT_LINE (RPAD('LDAP message ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_message,1,8)) ||
'(returned from search_s)');
-- count the number of entries returned
retval := DBMS_LDAP.count_entries(my_session, my_message);
DBMS_OUTPUT.PUT_LINE(RPAD('Number of Entries ',25,' ') || ': '
|| TO_CHAR(retval));
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
-- get the first entry
my_entry := DBMS_LDAP.first_entry(my_session, my_message);
entry_index := 1;
-- Loop through each of the entries one by one
while my_entry IS NOT NULL loop
-- print the current entry
my_dn := DBMS_LDAP.get_dn(my_session, my_entry);
-- DBMS_OUTPUT.PUT_LINE (' entry #' || TO_CHAR(entry_index) ||
-- ' entry ptr: ' || RAWTOHEX(SUBSTR(my_entry,1,8)));
DBMS_OUTPUT.PUT_LINE (' dn: ' || my_dn);
my_attr_name := DBMS_LDAP.first_attribute(my_session,my_entry,
my_ber_elmt);
attr_index := 1;
while my_attr_name IS NOT NULL loop
my_vals := DBMS_LDAP.get_values (my_session, my_entry,
my_attr_name);
if my_vals.COUNT > 0 then
FOR i in my_vals.FIRST..my_vals.LAST loop
DBMS_OUTPUT.PUT_LINE(' ' || my_attr_name || ' : ' ||
SUBSTR(my_vals(i),1,200));
end loop;
end if;
my_attr_name := DBMS_LDAP.next_attribute(my_session,my_entry,
my_ber_elmt);
attr_index := attr_index+1;
end loop;
my_entry := DBMS_LDAP.next_entry(my_session, my_entry);
DBMS_OUTPUT.PUT_LINE('===================================================');
entry_index := entry_index+1;
end loop;
-- unbind from the directory
retval := DBMS_LDAP.unbind_s(my_session);
DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' ||
TO_CHAR(retval));
DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');
END;
/
drop type LDAP_EMP_TABLE
/
drop type LDAP_EMP_ROW
/
create type LDAP_EMP_ROW
as object (common_name varchar2(50),
ename varchar2(20),
mgrname varchar2(50),
deptno number,
empno number);
/
create type LDAP_EMP_TABLE is table of LDAP_EMP_ROW;
/
create or replace function GET_LDAP_EMP
return LDAP_EMP_TABLE
is
ldap_emp LDAP_EMP_TABLE := LDAP_EMP_TABLE(LDAP_EMP_ROW(NULL,
NULL,
NULL,
NULL,
NULL));
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
my_attrs DBMS_LDAP.string_collection;
my_message DBMS_LDAP.message;
my_entry DBMS_LDAP.message;
entry_index PLS_INTEGER;
my_dn VARCHAR2(256);
my_attr_name VARCHAR2(256);
my_ber_elmt DBMS_LDAP.ber_element;
attr_index PLS_INTEGER;
i PLS_INTEGER;
my_vals DBMS_LDAP.STRING_COLLECTION ;
ldap_host VARCHAR2(256);
ldap_port VARCHAR2(256);
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
v_common_name varchar2(50);
v_ename varchar2(20);
v_mgrname varchar2(50);
n_deptno number;
n_empno number;
b_first boolean := TRUE;
BEGIN
retval := -1;
-- Customization
ldap_host := NULL ;
ldap_port := '389';
ldap_user := 'cn=Manager,o=xyzcorp,c=US';
ldap_passwd:= 'secret';
ldap_base := 'o=xyzcorp,c=US';
-- end of customizable settings
-- Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;
my_session := DBMS_LDAP.init(ldap_host,ldap_port);
-- bind to the directory
retval := DBMS_LDAP.simple_bind_s(my_session,
ldap_user, ldap_passwd);
-- issue the search
my_attrs(1) := 'employeeNumber';
my_attrs(2) := 'sn';
my_attrs(3) := 'ou';
my_attrs(4) := 'Manager';
retval := DBMS_LDAP.search_s(my_session, ldap_base,
DBMS_LDAP.SCOPE_SUBTREE,
'objectclass=inetOrgPerson',
my_attrs,
0,
my_message);
-- count the number of entries returned
retval := DBMS_LDAP.count_entries(my_session, my_message);
-- get the first entry
my_entry := DBMS_LDAP.first_entry(my_session, my_message);
entry_index := 1;
-- Loop through each of the entries one by one
while my_entry IS NOT NULL
loop
-- print the current entry
my_dn := DBMS_LDAP.get_dn(my_session, my_entry);
/*
* Get the common name from the distinguished name.
* First remove the 'higher level' stuff.
*/
v_common_name := rtrim(substr(my_dn, 1, instr(my_dn, ',') - 1));
/*
* Then remove the tag
*/
v_common_name := ltrim(substr(v_common_name,
instr(v_common_name, '=') + 1));
my_attr_name := DBMS_LDAP.first_attribute(my_session,my_entry,my_ber_elmt);
attr_index := 1;
while my_attr_name IS NOT NULL
loop
my_vals := DBMS_LDAP.get_values(my_session,my_entry,my_attr_name);
if my_vals.COUNT > 0
then
FOR i in my_vals.FIRST..my_vals.LAST
loop
if (lower(my_attr_name) = 'employeenumber') then
n_empno := to_number(substr(my_vals(i), 1, 20));
elsif (lower(my_attr_name) = 'sn') then
v_ename := upper(substr(my_vals(i), 1, 20));
elsif (lower(my_attr_name) = 'ou') then
n_deptno := to_number(substr(my_vals(i), 1, 20));
elsif (lower(my_attr_name) = 'manager') then
v_mgrname := rtrim(substr(my_vals(i), 1,
instr(my_vals(i), ',') - 1));
v_mgrname := ltrim(substr(v_mgrname,
instr(v_mgrname, '=') + 1));
end if;
end loop;
end if;
my_attr_name := DBMS_LDAP.next_attribute(my_session,my_entry,my_ber_elmt);
attr_index := attr_index +.1;
end loop;
if b_first
then
b_first := FALSE;
else
ldap_emp.extend;
end if;
ldap_emp(ldap_emp.last) := ldap_emp_row(v_common_name,
v_ename,
v_mgrname,
n_deptno,
n_empno);
my_entry := DBMS_LDAP.next_entry(my_session, my_entry);
entry_index := entry_index+1;
end loop;
-- unbind from the directory
retval := DBMS_LDAP.unbind_s(my_session);
return(ldap_emp);
END;
/
show error
create or replace view LDAP$EMP
As select *
from Table( Cast( GET_LDAP_EMP() As LDAP_EMP_TABLE ));