APEX authentication function in custom schema doesn't work

Question

In scheme settings page in section Login Processing, field Authentication Function is set to AUTORYZACJA (function name)

Field logout URL: wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=4155:PUBLIC_PAGE

here is my function:

create or replace FUNCTION AUTORYZACJA (p_username in VARCHAR2, p_password in VARCHAR2)
return BOOLEAN
is
v_pwd_baza varchar2(4000);
v_liczba number;
begin
select count(*) into v_liczba from UZYTKOWNICY where upper(login) = upper(p_username);
if v_liczba > 0 then
select password into v_pwd_baza from UZYTKOWNICY where upper(login) = upper(p_username);
if p_password = v_pwd_baza then
return true;
else
return false;
end if;
else
return false;
end if;
end;

Loging using schema with this function does not work. Error is "invalid login credentials". I don't know what to do.

I will appreciate your help with this situation.

Obviously, your function has to work well, so there is something else. Do you pass valid the login credential`? If I were you I would use the database authentication instead of your own authentication. — neshkeev
Yes i've passed valid credentials. Selects within function work properely. The project i'm creating consists of users and groups of users and I do have to create it in way that allows to move users users between groups and every group have different tabsets visible. Is another way to implement such solution? — user3376246
I've changed fucntion to refactored by You, but APEX still rejects credentials — user3376246
Maybe something is wrong in application settings, page or APEX? — user3376246

Scott Scott · Accepted Answer · 2014-08-18T01:15:55

You should also always use hashed passwords, don't store them in plain text. See something like http://apexawy.blogspot.com.au/2011/07/custom-authentication-scheme.html

The ora-06553 you received was because you tried to reference a boolean in SQL (the return value) - SQL has no awareness of booleans. You would need to test using something like

begin
  if autoryzacja('login','password') then 
    dbms_output.put_line('true');
  else
    dbms_output.put_line('false');
  end if;
end;
/

APEX authentication function in custom schema doesn't work

2 Answers