從Oracle DB透過LDAP驗證 Windows登入帳號密碼
資料來源:
此 PACKAGE 用來檢核輸入帳號、密碼是為個人電腦登入的帳號、密碼 若正確無誤,則回傳姓名、部門代號 否則則回傳 FALSE;1 OR FALSE;2 OR FALSE;3 代表各種錯誤狀況 CREATE OR REPLACE PACKAGE BODY dcp_login IS FUNCTION chk_pass(p_uid IN VARCHAR2, p_pass IN VARCHAR2) RETURN VARCHAR2 AS -- Adjust as necessary. l_user VARCHAR2(256) := p_uid; -- eg.'a012345'; l_ldap_host VARCHAR2(256) := 'aaaaa.com.tw'; l_ldap_port VARCHAR2(256) := '389'; l_ldap_user VARCHAR2(256) := p_uid || '@aaaaa.com.tw'; l_ldap_passwd VARCHAR2(256) := p_pass; -- AD login pass l_ldap_base VARCHAR2(256) := 'dc=aaaaa,dc=com,dc=tw'; --'cn=users,dc=aaaaa,dc=com,dc=tw'; -- Local l_retval PLS_INTEGER; l_session dbms_ldap.SESSION; l_attrs dbms_ldap.string_collection; l_message dbms_ldap.message; l_entry dbms_ldap.message; l_attr_name VARCHAR2(256); l_ber_element dbms_ldap.ber_element; l_vals dbms_ldap.string_collection; -- v_department VARCHAR2(256); v_sn VARCHAR2(256); v_givenname VARCHAR2(256); BEGIN l_retval := -1; -- 1. Initialize the LDAP session BEGIN l_session := dbms_ldap.init(l_ldap_host, l_ldap_port); EXCEPTION -- Connect fail WHEN OTHERS THEN RETURN 'false;1'; END; -- 2. Authenticate to the directory BEGIN l_retval := dbms_ldap.simple_bind_s(l_session, l_ldap_user, l_ldap_passwd); -- Wrong Password IF l_retval <> dbms_ldap_utl.success THEN RETURN 'false;2'; END IF; EXCEPTION -- Wrong Password WHEN OTHERS THEN RETURN 'false;2'; END; -- 3. Get all attributes l_attrs(1) := '*'; -- retrieve all attributes BEGIN -- 3-1. l_retval := dbms_ldap.search_s(ld => l_session, base => l_ldap_base, scope => dbms_ldap.scope_subtree, filter => 'sAMAccountName=' || l_user || '*', attrs => l_attrs, attronly => 0, res => l_message); --filter => 'objectclass=*', -- 3-2. IF dbms_ldap.count_entries(ld => l_session, msg => l_message) > 0 THEN -- 3-2-1. Get all the entries returned by our search. l_entry := dbms_ldap.first_entry(ld => l_session, msg => l_message); -- 3-2-2. < < entry_loop >> WHILE l_entry IS NOT NULL LOOP -- 3-2-2-1. Get all the attributes for this entry. l_attr_name := dbms_ldap.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element); -- 3-2-2-2. < < attributes_loop >> WHILE l_attr_name IS NOT NULL LOOP -- Get all the values for this attribute. BEGIN l_vals := dbms_ldap.get_values(ld => l_session, ldapentry => l_entry, attr => l_attr_name); EXCEPTION WHEN OTHERS THEN NULL; -- skip convert error END; ---- < < values_loop >> FOR i IN l_vals.first .. l_vals.last LOOP -- IF SUBSTRB(l_attr_name, 1, 5) = 'given' THEN v_givenname := SUBSTR(l_vals(i), 1, 200); END IF; IF SUBSTRB(l_attr_name, 1, 5) = 'depar' THEN v_department := SUBSTR(l_vals(i), 1, 200); END IF; IF l_attr_name LIKE 'sn%' THEN v_sn := SUBSTR(l_vals(i), 1, 200); END IF; /* IF (substrb(l_attr_name, 1, 5) IN ('given', 'depar') OR l_attr_name LIKE 'sn%') THEN dbms_output.put_line('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || substr(l_vals(i), 1, 200)); END IF; */ END LOOP values_loop; l_attr_name := dbms_ldap.next_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element); END LOOP attibutes_loop; -- end: 3-2-2-2. l_entry := dbms_ldap.next_entry(ld => l_session, msg => l_entry); END LOOP entry_loop; -- end: 3-2-2. END IF; -- end: 3-2. EXCEPTION WHEN OTHERS THEN -- others RETURN 'false;3'; END; --end: 3 RETURN v_sn || v_givenname || ';' || v_department; END chk_pass; -- FUNCTION get_userinfo(p_uid IN VARCHAR2, p_pass in varchar2) -- RETURN VARCHAR2; END dcp_login; ------------------- CALL FUNCTION: -------------------- SELECT dcp_login.chk_pass('empno', 'password') val FROM dual; |