oracle 查詢 ldap 帳戶信息的package

在plsql中有時會需要查看ldap Server上的數據,
如判段email帳戶是否存在,等等情形.

以Table函數返回ldap server上的數據.
導入使用時需要修改包體里面的l_ldap_host, l_ldap_user,l_ldap_passwd

使用範例:

select * from table(pkg_ldap.ldap_table_s('(&(objectClass=person)(mail=ganj*))'))

查詢效果圖如下


Oracle包的PL/SQL代碼如下:
該包中包含2個函數:
ldap_table_s: 取出ldap_server中的所有的attribute和值
get_user_table_s : 取出ldap_server特定的欄位值

create or replace package PKG_LDAP AUTHID CURRENT_USER as
/****************************************
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2013-11-10   gangjh           1. 獲取ldap 帳戶信息
*****************************************************************/


 type ldap_user_info is record (
 search_pk varchar2(256),
 dn varchar2(256),
 sn varchar2(256),
 cn varchar2(256),
 mail varchar2(256),
 givenName varchar2(256),
 department varchar2(256),
 description varchar2(256),
 title varchar2(256),
 st varchar2(256),
 memberOf varchar2(4000),
 telephoneNumber varchar2(256),
 lastLogonTimestamp date,
 pwdLastSet date
 ) ;

 type ldap_user_table is table of ldap_user_info ;

 type ldap_element is record
 (
   dn varchar2(256),
   attribute varchar2(256),
   string_val varchar2(256)
 );

 type ldap_table is table of ldap_element;


/*********************
@get_ldap_table_s : Table函數返回ldap查詢結果,類型為ldap_table
ex:
   select * from table(
      pkg_ldap.ldap_table_s('(&(objectClass=person)(objectClass=top)(mail=g*))'))
********************/
 function ldap_table_s(filter varchar2)
   return ldap_table pipelined ;


/*********************
@get_ldap_table_s :
   select * from table(pkg_ldap.get_user_table_s('(&(objectClass=person)(mail=gan*))'))
********************/
 function get_user_table_s(filter varchar2)
   return ldap_user_table pipelined ;



end PKG_LDAP;


/
create or replace package body PKG_LDAP as
/**************** ldap connection **********/
  l_ldap_host    constant VARCHAR2(256) := 'ldapserver';
  l_ldap_port    constant PLS_INTEGER := '389';
  l_ldap_user    constant VARCHAR2(256) := 'user';
  l_ldap_passwd  constant VARCHAR2(256) := 'pass';
--  l_ldap_base    constant varchar2(256) := 'DC=yydg,DC=com,DC=cn';
  l_ldap_base    constant varchar2(256) := '';
/*********************************************/


TYPE MESSAGE_COLLECTION IS TABLE OF DBMS_LDAP.MESSAGE
      INDEX BY BINARY_INTEGER;


procedure print(m varchar2) is
begin
  return;
  dbms_output.put_line(m) ;
end ;

function get_values(ld IN DBMS_LDAP.SESSION,
                    ldapentry IN DBMS_LDAP.MESSAGE,
                    attr IN VARCHAR2)
      RETURN DBMS_LDAP.STRING_COLLECTION IS
 aa  DBMS_LDAP.STRING_COLLECTION ;
BEGIN
    RETURN DBMS_LDAP.get_values (ld, ldapentry, attr);
    EXCEPTION
       WHEN OTHERS THEN
         RETURN aa;

end ;


function all_attributes(ld        DBMS_LDAP.session,
                        ldapentry DBMS_LDAP.message)
         return DBMS_LDAP.string_collection is

  l_attr    DBMS_LDAP.string_collection;
  ber_elem  DBMS_LDAP.ber_element;
  v_attr varchar2(300) ;
begin
    v_attr := DBMS_LDAP.first_attribute(ld, ldapentry, ber_elem);

    WHILE v_attr IS NOT NULL LOOP
        l_attr(l_attr.count()+1) := v_attr ;
        v_attr := dbms_ldap.next_attribute(ld, ldapentry, ber_elem);
    END LOOP ;

  return l_attr;
end;


function all_messages(ld    DBMS_LDAP.session,
                      msg   DBMS_LDAP.message)
         return MESSAGE_COLLECTION is

  l_msg  MESSAGE_COLLECTION;
  v_msg  DBMS_LDAP.message ;
begin
    v_msg := DBMS_LDAP.first_entry(ld, msg);

    WHILE v_msg IS NOT NULL LOOP
        l_msg(l_msg.count()+1) := v_msg ;
        v_msg := dbms_ldap.next_entry(ld, v_msg);
    END LOOP ;

  return l_msg;
end;



function logon(l_host varchar2,
               l_port  PLS_INTEGER,
               l_user varchar2,
               l_passwd varchar2)
         return DBMS_LDAP.session is

  l_sess   DBMS_LDAP.session;
  l_retval    PLS_INTEGER ;
begin
  -- Connect to the LDAP server.
  l_sess := DBMS_LDAP.init(hostname => l_host,
                           portnum => l_port);

  l_retval := DBMS_LDAP.simple_bind_s(ld => l_sess,
                                      dn => l_user,
                                      passwd => l_passwd );
  print(l_retval);
  return l_sess;
end;

-- Disconnect from the LDAP server.
procedure logout(l_session in out DBMS_LDAP.session) is
  l_retval pls_integer ;
begin
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  print(l_retval);
end ;

function search_s(ld     DBMS_LDAP.SESSION,
                  filter varchar2)
         return MESSAGE_COLLECTION is
 l_msg     DBMS_LDAP.message;
 l_retval  PLS_INTEGER;
 l_attrs   DBMS_LDAP.string_collection;
begin
    l_retval := DBMS_LDAP.search_s(ld => ld,
                                   base => l_ldap_base,
                                   scope => DBMS_LDAP.SCOPE_SUBTREE,
                                   filter => filter ,
                                   attrs => l_attrs,
                                   attronly => 0,
                                   res => l_msg);
    print(dbms_ldap.err2string(l_retval));
    return all_messages(ld, l_msg) ;
end;




function ldap_table_s(filter varchar2)
   return ldap_table pipelined is

  l_session      DBMS_LDAP.session;
  l_msgs         MESSAGE_COLLECTION ;
  l_attrs        DBMS_LDAP.string_collection;
  l_vals         DBMS_LDAP.string_collection;
  v_one_attr     ldap_element;

BEGIN
    l_session := logon(l_ldap_host, l_ldap_port, l_ldap_user, l_ldap_passwd) ;

    l_msgs := search_s(l_session, filter) ;

    for m1 in 1..l_msgs.count() LOOP --循環取出多個dn數據

        v_one_attr.dn := DBMS_LDAP.get_dn(ld => l_session, ldapentry => l_msgs(m1)) ;
        l_attrs := all_attributes(l_session, l_msgs(m1));

        for t1 in 1.. l_attrs.count() loop
            v_one_attr.attribute := l_attrs(t1);
            l_vals := get_values (ld => l_session, ldapentry => l_msgs(m1), attr => v_one_attr.attribute);
            FOR i in 0.. l_vals.count()-1 LOOP
                v_one_attr.string_val := l_vals(i);
                pipe row (v_one_attr);
            END LOOP;
        END LOOP ;
    END LOOP ;

    logout(l_session);

    return ;
end ldap_table_s;



--n: 這個數字代表表1601年1月1日（星期一）0點開始,已經經過多少個100納秒
function to_timestamp(n number) return date is
  v_start constant date := to_date('16010101','yyyymmdd') ;
begin
  return v_start + n /10000000/60/1440;
  exception when others then
    dbms_output.put_line('n val:'||n);
    return null;
end;

/***********************
@get_userinfo 轉換entry數據為 ldap_user_info record,
  有多個值時只取第一個值
*********************/
function get_userinfo(ld      DBMS_LDAP.session,
                      l_entry DBMS_LDAP.message)
         return ldap_user_info is

  v_user ldap_user_info := null;

  function l_get_value(v_entry DBMS_LDAP.message, v_attr varchar2) return varchar2 is
  l_vals         DBMS_LDAP.string_collection;
  v1 varchar2(4000) :='' ;
  begin
    l_vals := DBMS_LDAP.get_values (ld => ld,
                                    ldapentry => v_entry,
                                    attr => v_attr);
    for r in 0.. l_vals.count() -1 loop
        v1 :=v1 ||chr(10)|| l_vals(r);
    end loop;
    return substr(v1, 2) ;
  end ;
begin

/*  l_attrs(1) := 'sn'; -- 中文名
  l_attrs(2) := 'cn';
  l_attrs(3) := 'mail';
  l_attrs(4) := 'telephoneNumber';
  l_attrs(5) := 'givenName';
  l_attrs(6) := 'lastLogonTimestamp';
  l_attrs(7) := 'pwdLastSet';  */

  v_user.dn := dbms_ldap.get_dn(ld, l_entry) ;
  v_user.sn := l_get_value(l_entry, 'sn') ;
  v_user.cn := l_get_value(l_entry, 'cn') ;
  v_user.mail := l_get_value(l_entry, 'mail') ;
  v_user.givenName := l_get_value(l_entry, 'givenName') ;
  v_user.telephoneNumber := l_get_value(l_entry, 'telephoneNumber') ;
  v_user.department := l_get_value(l_entry, 'department') ;
  v_user.description := l_get_value(l_entry, 'description') ;
  v_user.title := l_get_value(l_entry, 'title') ;
  v_user.st := l_get_value(l_entry, 'st') ;
  v_user.memberOf := l_get_value(l_entry, 'memberOf') ;
  v_user.lastLogonTimestamp := to_timestamp(l_get_value(l_entry, 'lastLogonTimestamp')) ;
  v_user.pwdLastSet := to_timestamp(l_get_value(l_entry, 'pwdLastSet')) ;

  return v_user;
end;



/******************
  @get_ldap_table_s  --   查詢ldap數據,
*****************/

 function get_user_table_s(filter varchar2)
           return ldap_user_table pipelined is

  v_userinfo   ldap_user_info := null ;

  l_session      DBMS_LDAP.session;
  l_msgs         MESSAGE_COLLECTION;
BEGIN

    l_session := logon(l_ldap_host, l_ldap_port, l_ldap_user, l_ldap_passwd) ;

    l_msgs := search_s(l_session, filter) ;

    FOR r in 1..l_msgs.count() LOOP
        v_userinfo := get_userinfo(l_session, l_msgs(r)) ;
        v_userinfo.search_pk := filter ;
        pipe row(v_userinfo) ;
    END LOOP ;

  logout(l_session);

  return ;
end get_user_table_s;



BEGIN
    DBMS_LDAP.USE_EXCEPTION := TRUE;
end PKG_LDAP;
/

阿飛
2015/05/19 修改