oracle 查詢 ldap 帳戶信息的package

最新推荐文章于 2023-08-08 11:54:20 发布

mikean

最新推荐文章于 2023-08-08 11:54:20 发布

阅读量1.3k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

54 篇文章 0 订阅

订阅专栏

原文地址：oracle 查詢 ldap 帳戶信息的package 作者：gangjh

可以table形式返回數據.

点击(此处)折叠或打开

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) ,
telephoneNumber varchar2(256) ,
lastLogonTimestamp varchar2(256)
) ;
type ldap_user_table is table of ldap_user_info ;
function get_ldap_table_s(filter varchar2)
return ldap_user_table pipelined ;
/*******************************
一個參數
select * from
table(pkg_ldap.get_ldap_table(
q'# select distinct mail_no from yy3mat.y3_userm where status='Y' and length(nvl(mail_no,' '))>5 #',
'(&(objectClass=top)(mail=:param1))'))
兩個參數
select * from
table(pkg_ldap.get_ldap_table(
q'# select distinct mail_no,user_code
from yy3mat.y3_userm
where status='Y' and length(nvl(mail_no,' '))>5 #',
'(&(objectClass=top)(|(mail=:param1)(sn=:param2))) '))
*************************************************************/
l_filter_mail constant varchar2(256) :='(&(objectClass=top)(mail=:param1))';
function get_ldap_table(sqlquery varchar2, filter varchar2 default l_filter_mail)
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 VARCHAR2(256) := '3268';
l_ldap_user constant VARCHAR2(256) := 'user';
l_ldap_passwd constant VARCHAR2(256) := 'pass';
/*********************************************/
type string_array is table of varchar2(2000) index by pls_integer ;
type ldap_user_array is table of ldap_user_info index by pls_integer ;
procedure debug_msg(m varchar2) is
begin
-- dbms_output.put_line(m) ;
null;
end ;
/***************************************************************
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2013-11-05 gangjh 1. 從ldap 檢測email account是否存在
*****************************************************************/
function check_mail(l_sess DBMS_LDAP.session, mail_address varchar2) return pls_integer is
l_ldap_base VARCHAR2(256) := '';
l_attrs DBMS_LDAP.string_collection;
l_filter varchar2(256) := '&(objectClass=top)(mail=$mail_address$)';
l_message DBMS_LDAP.message;
l_retval PLS_INTEGER;
begin
l_attrs(1) := 'sn'; -- 中文名
l_attrs(2) := 'mail';
l_retval := DBMS_LDAP.search_s(ld => l_sess,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => replace(l_filter,'$mail_address$', mail_address) ,
attrs => l_attrs,
attronly => 0,
res => l_message);
return DBMS_LDAP.count_entries(ld => l_sess, msg => l_message) ;
end check_mail;
/*********************
找出所有參數, :xxx, 參數命名,冒號開始後多個字符或數字 ':[a-zA-Z0-9]+'
****************/
function parser_param(str in varchar2) return string_array is
-- l_filter_mail constant varchar2(256) :='&(objectClass=top)(mail=:param1)';
p1 varchar2(32767) ;
p varchar2(300) ;
ret string_array ;
i pls_integer :=0 ;
procedure foundparam(str in out varchar2 ,par out varchar2) is
idx pls_integer ;
begin
select regexp_substr(str, ':[a-zA-Z0-9]+') into par
from dual;
if par is not null then
idx := instr(str, par) ;
str := substr(str, idx + length(par)) ;
end if ;
end foundparam;
begin
p1 := str ;
loop
foundparam(p1, p) ;
exit when p is null;
i := i+1 ;
ret(i) := p ;
end loop;
return ret ;
end ;
function search_userinfo(l_sess DBMS_LDAP.session, l_filter varchar2) return ldap_user_info is
l_ldap_base VARCHAR2(256) := '';
l_attrs DBMS_LDAP.string_collection;
l_message DBMS_LDAP.message;
l_retval PLS_INTEGER;
l_entry DBMS_LDAP.message;
v_ret 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;
begin
l_vals := DBMS_LDAP.get_values (ld => l_sess,
ldapentry => v_entry,
attr => v_attr);
if l_vals.count() >0 then
return substr(l_vals( l_vals.first),1,200);
else
return null;
end if ;
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_retval := DBMS_LDAP.search_s(ld => l_sess,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => l_filter ,
attrs => l_attrs,
attronly => 0,
res => l_message);
if DBMS_LDAP.count_entries(ld => l_sess, msg => l_message) < 1 then
return null ;
end if ;
l_entry := DBMS_LDAP.first_entry(ld => l_sess,
msg => l_message);
v_ret.dn := dbms_ldap.get_dn(l_sess, l_entry) ;
v_ret.sn := l_get_value(l_entry, 'sn') ;
v_ret.cn := l_get_value(l_entry, 'cn') ;
v_ret.mail := l_get_value(l_entry, 'mail') ;
v_ret.givenName := l_get_value(l_entry, 'givenName') ;
v_ret.telephoneNumber := l_get_value(l_entry, 'telephoneNumber') ;
v_ret.lastLogonTimestamp := l_get_value(l_entry, 'lastLogonTimestamp') ;
return v_ret ;
end search_userinfo;
function search_usertable(l_sess DBMS_LDAP.session, l_filter varchar2) return ldap_user_array is
l_ldap_base VARCHAR2(256) := '';
l_attrs DBMS_LDAP.string_collection;
l_message DBMS_LDAP.message;
l_retval PLS_INTEGER;
l_entry DBMS_LDAP.message;
v_user ldap_user_info := null;
v_ret ldap_user_array ;
function l_get_value(v_entry DBMS_LDAP.message, v_attr varchar2) return varchar2 is
l_vals DBMS_LDAP.string_collection;
begin
l_vals := DBMS_LDAP.get_values (ld => l_sess,
ldapentry => v_entry,
attr => v_attr);
if l_vals.count() >0 then
return substr(l_vals( l_vals.first),1,200);
else
return null;
end if ;
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_retval := DBMS_LDAP.search_s(ld => l_sess,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => l_filter ,
attrs => l_attrs,
attronly => 0,
res => l_message);
if DBMS_LDAP.count_entries(ld => l_sess, msg => l_message) > 0 then
l_entry := DBMS_LDAP.first_entry(ld => l_sess,
msg => l_message);
WHILE l_entry IS NOT NULL LOOP
v_user.dn := dbms_ldap.get_dn(l_sess, 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.lastLogonTimestamp := l_get_value(l_entry, 'lastLogonTimestamp') ;
v_ret( v_ret.count()) := v_user ;
l_entry := dbms_ldap.next_entry(ld => l_sess,
msg => l_entry);
END LOOP ;
end if ;
return v_ret ;
end search_usertable;
/******************
查詢ldap數據,返回record type數據,
*****************/
function get_ldap_table(sqlquery varchar2, filter varchar2)
return ldap_user_table pipelined is
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
c_cur1 sys_refcursor ;
v_param1 varchar2(200) ;
v_param2 varchar2(200) ;
v_param3 varchar2(200) ;
v_userlist ldap_user_array ;
v_userinfo ldap_user_info := null ;
parmlist string_array ;
vallist string_array ;
v_search varchar2(32767) ;
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 );
parmlist := parser_param(filter) ;
open c_cur1 for sqlquery ;
loop
case
when parmlist.count() =1 then fetch c_cur1 into v_param1 ;
when parmlist.count() =2 then fetch c_cur1 into v_param1,v_param2 ;
when parmlist.count() =3 then fetch c_cur1 into v_param1,v_param2, v_param3 ;
else
raise_application_error(-20013, '查詢參數過多') ;
end case;
exit when c_cur1%notfound ;
/***********構造參數***********/
v_search := filter ;
vallist(1) := v_param1 ;
vallist(2) := v_param2 ;
vallist(3) := v_param3 ;
for i in 1.. parmlist.count() loop
v_search := replace(v_search, parmlist(i), vallist(i)) ;
end loop;
debug_msg(v_search) ;
/**********************/
v_userlist := search_usertable(l_session, v_search);
-- 沒有找到記錄,也返回一筆
if v_userlist.count() <=0 then
v_userinfo.search_pk := v_search ;
pipe row(v_userinfo) ;
end if ;
for x in 0..v_userlist.count()-1 loop
v_userlist(x).search_pk := v_search ;
pipe row(v_userlist(x)) ;
end loop;
end loop;
close c_cur1 ;
-- Disconnect from the LDAP server.
l_retval := DBMS_LDAP.unbind_s(ld => l_session);
return ;
exception
when others then
if c_cur1%isopen then
close c_cur1 ;
end if ;
raise ;
end ;
function get_ldap_table_s(filter varchar2)
return ldap_user_table pipelined is
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
v_usertable ldap_user_array ;
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 );
v_usertable := search_usertable(l_session, filter);
for x in 0..v_usertable.count()-1 loop
v_usertable(x).search_pk := filter ;
pipe row(v_usertable(x)) ;
end loop;
-- Disconnect from the LDAP server.
l_retval := DBMS_LDAP.unbind_s(ld => l_session);
return ;
end get_ldap_table_s;
function get_ldap_table3(c_cur1 in sys_refcursor, filter varchar2)
return ldap_user_table pipelined is
aa ldap_user_table ;
begin
select get_ldap_table('select mail from yy3mat.y3_userm',
'(&(objectClass=top)(mail=:param1))') into aa
from dual;
pipe row (aa(1));
return ;
end ;
end PKG_LDAP;
/

mikean

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 查詢 ldap 帳戶信息的package

原文地址：oracle 查詢 ldap 帳戶信息的package 作者：gangjh可以table形式返回數據.点击(此处)折叠或打开create or replace package PKG_LDAP AUTHID CURRENT_USERas/**************************************** Ver Dat
复制链接

扫一扫