Simple LDAP verification

T

To verify user against LDAP/AD you can use this simple script. If you need more you should explore this great article about LDAP or this great article for use in APEX or latest documentation for DBMS_LDAP.

DECLARE
    FUNCTION ldap_login (
        in_login    VARCHAR2,
        in_pwd      VARCHAR2,
        in_server   VARCHAR2,
        in_port     NUMBER      := 389
    )
    RETURN BOOLEAN AS
        l_sess      DBMS_LDAP.SESSION;
        l_resp      PLS_INTEGER;
    BEGIN
        IF (in_login IS NOT NULL AND in_pwd IS NOT NULL) THEN
            DBMS_LDAP.USE_EXCEPTION := TRUE;
            l_sess := DBMS_LDAP.INIT(in_server, in_port);  -- must match with ACL
            l_resp := DBMS_LDAP.SIMPLE_BIND_S(l_sess, in_login, in_pwd);
            l_resp := DBMS_LDAP.UNBIND_S(l_sess);
            RETURN TRUE;
        END IF;
        RETURN FALSE;
    EXCEPTION
    WHEN OTHERS THEN
        BEGIN
            l_resp := DBMS_LDAP.UNBIND_S(l_sess);
        EXCEPTION
        WHEN OTHERS THEN
            NULL;
        END;
        RETURN FALSE;
    END;
BEGIN
    IF NOT ldap_login('&LOGIN', '&PASSWORD', '&SERVER', 389) THEN
        RAISE_APPLICATION_ERROR(-20000, 'LDAP_VERIFICATION_FAILED');
    END IF;
END;
/

You will need to setup ACL using SYSDBA account.

DECLARE
    in_acl_name         CONSTANT VARCHAR2(64)   := 'ldap_login.xml';
    in_acl_user         CONSTANT VARCHAR2(30)   := 'USER';
    in_acl_server       CONSTANT VARCHAR2(64)   := '&SERVER';
    in_acl_port_start   CONSTANT NUMBER(5)      := 389;
    in_acl_port_end     CONSTANT NUMBER(5)      := in_acl_port_start;
BEGIN
    BEGIN
        DBMS_NETWORK_ACL_ADMIN.DROP_ACL(in_acl);
    EXCEPTION
    WHEN OTHERS THEN
        NULL;
    END;
    --
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(in_acl, '', in_acl_user, TRUE, 'CONNECT');
    --
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(in_acl, in_acl_server, in_acl_port_start, in_acl_port_end);
    --
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(in_acl, in_acl_user, TRUE, 'RESOLVE');
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(in_acl, in_acl_user, TRUE, 'CONNECT');
    --
    COMMIT;
END;
/