Hi All, I have created a function for authentication in a database table. I am able to test using function ‘authenticate_user’
directly from the database and it works. Also I have created the custom authentication scheme and made it default.
Under the section Authentication Function Name:
I gave the below value
return authenticate_user;
The authentication is not working from apex. I am using version -Oracle APEX 24.2.13. I feel the function and db side is good. I am missing something on the Apex authentication scheme side of things. Any help is appreciated.
CREATE OR REPLACE FUNCTION authenticate_user (
p_username IN VARCHAR2,
p_password IN VARCHAR2
) RETURN BOOLEAN AS
l_stored_hash SECRET.PASSWORD_HASH%TYPE;
l_is_active USERS.IS_ACTIVE%TYPE;
l_person_id USERS.PERSON_ID%TYPE;
l_input_hash VARCHAR2(256);
BEGIN
-- Fix: Change USER_NAME to PERSON_LOGIN
SELECT PERSON_ID, IS_ACTIVE
INTO l_person_id, l_is_active
FROM USERS
WHERE UPPER(PERSON_LOGIN) = UPPER(p_username);
-- Verify active status
IF l_is_active != 'Y' THEN
RETURN FALSE;
END IF;
-- Fetch hash using PERSON_ID
SELECT PASSWORD_HASH
INTO l_stored_hash
FROM SECRET
WHERE PERSON_ID = l_person_id;
-- Generate and compare hash
l_input_hash := APEX_UTIL.GET_HASH(p_values => apex_t_varchar2(p_password, p_username));
RETURN l_input_hash = l_stored_hash;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
WHEN OTHERS THEN
RETURN FALSE;
END;
/
Tested success
begin
if authenticate_user('adoe', 'Welcome123') then
dbms_output.put_line('LOGIN SUCCESSFUL');
else
dbms_output.put_line('LOGIN FAILED - Check your hash logic');
end if;
end;