73 lines
2.5 KiB
MySQL
73 lines
2.5 KiB
MySQL
|
--------------------------------------------------------
|
||
|
|
-- DDL for Function FNC_AD_AUTH
|
||
|
|
--------------------------------------------------------
|
||
|
|
|
||
|
|
CREATE OR REPLACE EDITIONABLE FUNCTION "ENVX_OPER"."FNC_AD_AUTH" (
|
||
|
|
p_username IN VARCHAR2,
|
||
|
|
p_password IN VARCHAR2
|
||
|
|
) RETURN BOOLEAN IS
|
||
|
|
|
||
|
|
l_ldap_host VARCHAR2(500) := 'ldaplc.lc.gc.local';
|
||
|
|
l_ldap_port VARCHAR2(500) := '389';
|
||
|
|
l_ldap_base VARCHAR2(500) := 'DC=lc,DC=gc,DC=local';
|
||
|
|
l_dn_user VARCHAR2(500);
|
||
|
|
l_retval PLS_INTEGER;
|
||
|
|
l_session dbms_ldap.session;
|
||
|
|
v_boolean BOOLEAN;
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.fnc_ad_auth';
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
IF p_username IS NULL OR p_password IS NULL THEN
|
||
|
|
raise_application_error(-20000, 'Debe especificar sus credenciales.');
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
dbms_ldap.use_exception := true;
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
dn
|
||
|
|
INTO l_dn_user
|
||
|
|
FROM
|
||
|
|
TABLE ( apex_ldap.search(
|
||
|
|
p_username => 'CN=LCU SICD LDAP,OU=Usuarios de Servicios,DC=lc,DC=gc,DC=local',
|
||
|
|
p_pass => 'martesSICDsegura10',
|
||
|
|
p_host => 'ldaplc.lc.gc.local',
|
||
|
|
p_search_base => 'DC=lc,DC=gc,DC=local',
|
||
|
|
p_search_filter => '&(objectClass=person)(userPrincipalName='||p_username||'@lc.gc.local)(memberOf=CN=LCGU_SICD_Users,OU=Grupos,DC=lc,DC=gc,DC=local)',
|
||
|
|
p_attribute_names => 'CN') );
|
||
|
|
|
||
|
|
IF length(l_dn_user) > 1 THEN
|
||
|
|
|
||
|
|
l_session := DBMS_LDAP.init(l_ldap_host,l_ldap_port);
|
||
|
|
l_retval := dbms_ldap.simple_bind_s(ld => l_session, dn => l_dn_user, passwd => p_password);
|
||
|
|
l_retval := dbms_ldap.unbind_s(ld => l_session);
|
||
|
|
v_boolean := true;
|
||
|
|
RETURN v_boolean;
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
v_boolean := false;
|
||
|
|
RETURN v_boolean;
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_util.set_custom_auth_status(p_status => 'Usuario o Contraseña incorrectos');
|
||
|
|
|
||
|
|
IF length(l_session) > 1 THEN
|
||
|
|
l_retval := dbms_ldap.unbind_s(ld => l_session);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
|
||
|
|
v_boolean := false;
|
||
|
|
RETURN v_boolean;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/
|