CODEWITHSUNDEEP
oracle-databaseoracle-apexcustom-authentication
Dev Yego
Dev Yego

Reputation: 553

Oracle APEX Custom Authentication

I'm trying to implement custom login on APEX and the app keeps rejecting valid data. I have the users table defined as so:

CREATE TABLE "USERS" 
(   
 "USER_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
 "USERNAME" VARCHAR2(100) NOT NULL ENABLE, 
 "PASSWORD" VARCHAR2(512) NOT NULL ENABLE, 
 "IS_ADMIN" VARCHAR2(1) NOT NULL ENABLE, 
  CONSTRAINT "PK_CUSTOM_USERS" PRIMARY KEY ("USER_ID")
 USING INDEX  ENABLE, 
 CONSTRAINT "UQ_CUSTOM_USERS_USERNAME" UNIQUE ("USERNAME")
 USING INDEX  ENABLE
) ;

And the custom auth function:

create or replace FUNCTION authenticate_user (p_username VARCHAR2, p_password VARCHAR2)
RETURN BOOLEAN
IS
l_count NUMBER;
BEGIN
-- Debug statement to output input parameters
DBMS_OUTPUT.PUT_LINE('Input Username: ' || p_username);
DBMS_OUTPUT.PUT_LINE('Input Password: ' || p_password);

-- Query to check if the username and password match
SELECT COUNT(*)
INTO l_count
FROM USERS
WHERE USERNAME = p_username
  AND PASSWORD = p_password;

-- Debug statement to output the count
DBMS_OUTPUT.PUT_LINE('Count: ' || l_count);

-- Check if count is greater than 0
IF l_count > 0 THEN
    RETURN TRUE; -- Authentication successful
ELSE
    RETURN FALSE; -- Authentication failed
END IF;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RETURN FALSE; -- Return false on any error
END;

When I test function within APEX SQL command console, I get the expected behavior. Here is how I'm testing it:

DECLARE
v_username VARCHAR2(100) := 'tommy';
v_password VARCHAR2(100) := 'password';
v_is_valid BOOLEAN;
BEGIN
v_is_valid := authenticate_user(v_username, v_password);
IF v_is_valid THEN
    DBMS_OUTPUT.PUT_LINE('Authentication successful');
ELSE
    DBMS_OUTPUT.PUT_LINE('Authentication failed');
END IF;
END;

I have already instructed the app to use a Custom auth schema of type Custom. Still nothing works. I was following along this tutorial and his implementation works fine. Some more configuration here. Custom Authentication Schema enter image description here Application Authentication enter image description here

Upvotes: 0

Views: 355

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18650

I did a quick test with your code and it works fine. However... apex checks for uppercase usernames, so make sure that the username in the users table is stored as uppercase or do an UPPER(username) in the where clause. You'll have to have an index UPPER(username) then too to enforce uniqueness.

Unrelated comment (Super important !): Never store a password as cleartext. I wouldn't want to work in an organisation where passwords are stored in cleartext. That is a major security risk. Instead store an encrypted value and compare the encrytped value of the password the user submits to the stored value. The help for authentication scheme has a good example of an authentication function with a hashed password. Your could just copy that example and change the table and column names.

enter image description here

Upvotes: 1

Related Questions