/ Database

從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;