Can't retrieve data from a function during login Oracle Apex

Question

I'm trying to retrieve the "RESPONSABLE" field from my EMPLOYES table during the login.

I created a function called "employe_resp" which SELECT the responsable column into a variable using the username and hashed password in the WHERE condition.

Here's the EMPLOYES table

Here's my function

FUNCTION employe_resp (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
IS
  v_resp EMPLOYES.responsable%TYPE;
BEGIN
  SELECT responsable
  INTO v_resp
  FROM EMPLOYES
  WHERE UPPER(email) = UPPER(p_username)
  AND pwd = hash_pwd(p_username, p_password);

  RETURN v_resp;
END employe_resp;

my application item is called G_RESP

On my login page, I created this process :

When logging in, I have a "data not found" error. The problem seems to come from my SELECT statement inside my function, but I don't understand why, because when I execute the SELECT using raw datas in SQL Commands, it's working

Does anyone have a tip or a workaround ?

Thanks in advance

Thomas

Answer 1

I think there is something wrong with the logic here. There are 2 concepts and you're mixing them.

Concept 1:Authentication: this is about the user logging into the application

Concept 2:Authorization: this is about what the user can do in an application

Note that you can setup your authorization as such that a user does not have access to any functionality in the application.

The password is only needed in the authentication function. Do not mix other (authorization) functionality in the authentication process. Instead create application processes (or computations) to populate the application item G_RESP. To run those queries, no password check is needed.

Or even better, create appropriate authorization schemes for each user role/responsibility. No password checks are needed for those queries either.