oracle 读取ldap数据,oracle 查詢 ldap 帳戶信息的package

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;

/