ldap - liamlamth/blog GitHub Wiki

ldapsearch -x -h msdc01.example.local -D "[email protected]" -w 'abcd1234' -b "ou=X,dc=gpch,dc=local" displayName mail

CREATE TABLE dc_email(
  NAME  VARCHAR2(300 BYTE), 
  EMAIL VARCHAR2(3000 BYTE))
CREATE TABLE dc_email_filter(
  FILTER_TYPE VARCHAR2(10 BYTE), 
  FILTER_WORD VARCHAR2(100 BYTE))
CREATE TABLE dc_email_addition(
  NAME  VARCHAR2(300 BYTE), 
  EMAIL VARCHAR2(3000 BYTE))

create or replace procedure p_email_refresh
is 
    l_ldap_host    VARCHAR2(256) := 'msdc01.example.local';
    l_ldap_port    VARCHAR2(256) := '389';
    l_ldap_user    VARCHAR2(256) := 'CN=mailuser,OU=IT-Users,DC=example,DC=local';
    l_ldap_passwd  VARCHAR2(256) := 'abcd1234';
    l_ldap_base    VARCHAR2(256) := 'dc=example,dc=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_attr1_name   VARCHAR2(256);
    l_attr2_name   VARCHAR2(256);
    l_ber_element  DBMS_LDAP.ber_element;
    l_vals1        DBMS_LDAP.string_collection;
    l_vals2        DBMS_LDAP.string_collection;
    
    l_like_clause  VARCHAR2(4000);
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);

    l_attrs(1) := 'displayName';
    l_attrs(2) := 'mail';
    l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                   base     => l_ldap_base,
                                   scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                   filter   => '&(objectClass=person)(mail=*)(displayName=*)',
                                   attrs    => l_attrs,
                                   attronly => 0,
                                   res      => l_message);

    IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN

        execute immediate 'truncate table dc_email';

        l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                         msg => l_message);

        << entry_loop >>
        WHILE l_entry IS NOT NULL LOOP

            l_attr1_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                                      ldapentry => l_entry,
                                                      ber_elem  => l_ber_element);
            l_attr2_name := DBMS_LDAP.next_attribute (ld        => l_session,
                                                      ldapentry => l_entry,
                                                      ber_elem  => l_ber_element);

            l_vals1 := DBMS_LDAP.get_values (ld        => l_session,
                                             ldapentry => l_entry,
                                             attr      => l_attr1_name);
            l_vals2 := DBMS_LDAP.get_values (ld        => l_session,
                                             ldapentry => l_entry,
                                             attr      => l_attr2_name);

            insert into dc_email(name, email) values(SUBSTR(l_vals1(0),1,200), SUBSTR(l_vals2(0),1,200));

            l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                            msg => l_entry);
        END LOOP entry_loop;

    END IF;
    
    -- Disconnect from the LDAP server.
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    
    select listagg('or lower('||filter_type||') like '''||filter_word||'''',' ') like_clause
    into l_like_clause
    from dc_email_filter
    where filter_word like '%\%%' escape '\';
    
    execute immediate 'delete from dc_email
                       where name in ( select filter_word
                                       from dc_email_filter
                                       where filter_word not like ''%\%%'' escape ''\''
                                       and filter_type = ''name''
                                     )' || l_like_clause;
                                     
    insert into dc_email select * from dc_email_addition;
    
    commit;
                                     
END;
/