0

I wanted to implement custom authentication in my oracle apex application. So for that I wrote some pl/sql codes to create a package.

create or replace package body user_tools_pak
as
  function login_auth(field_userid in varchar2,field_password in varchar2) 
  return boolean is
  begin
    select user_id
    from   USER_TABLE
    where  user_id = field_userid
    and    password = field_password;
    apex_util.set_session_state(p_name  => 'SESSION_USERID', p_value => user_id);
    return true;
  exception
    when no_data_found then
      return false;
  end login_auth;
end user_tools_pak;

But when I run it an error occurs.

enter image description here

Is there anything wrong with my code or am I missing something?

samman adhikari
  • 571
  • 1
  • 7
  • 17
  • check your variable names, filed_user_id for example. and in general, there are ways to identify what was the actual compilation issue, described, for example, [here](https://stackoverflow.com/questions/40421202/how-to-find-out-plsql-compilation-errors) – micklesh May 11 '20 at 09:04
  • 2
    Passwords should be hashed, not stored as plain text. – Tony Andrews May 11 '20 at 09:47

1 Answers1

2

The function should accept p_username and p_password as parameters and return boolean. This can be found in the help section of the authentication function name

Specify the name of the function that will verify the user's username and password, after they were entered on a login page. If you enter nothing, you allow any username/password to succeed. The function itself can be defined in the authentication's 'PL/SQL Code' textarea, within a package or as a stored function.

This function must return a boolean to the login procedure that calls it. It has 2 input parameters 'p_username' and 'p_password' that can be used to access the values an end user entered on the login page.

Examples Enter the following code in the 'PL/SQL Code' textarea

function my_authentication (
    p_username in varchar2,
    p_password in varchar2 )
    return boolean
is
    l_user my_users.user_name%type := upper(p_username);
    l_id   my_users.id%type;
    l_hash my_users.password_hash%type;
begin
    begin
        select id  , password_hash
          into l_id, l_hash
          from my_users
         where user_name = l_user;
    exception when no_data_found then
        l_hash := '-invalid-';
    end;

return l_hash = rawtohex(sys.dbms_crypto.hash (
                    sys.utl_raw.cast_to_raw (
                        p_password||l_id||l_user ),
                    sys.dbms_crypto.hash_sh512 ));
end;

and my_authentication as 'Authentication Function'.

screenshot

Anuswadh
  • 542
  • 1
  • 11
  • 19