apexauthbody.sql

create or replace package body apexauth
as
PROCEDURE login 
          (
           p_uname IN VARCHAR2
          ,p_password IN VARCHAR2
          ,p_session_id IN VARCHAR2
          ,p_flow_page IN VARCHAR2
          )
IS
 lv_goto_page NUMBER DEFAULT 1;
BEGIN
 
 -- This logic is a demonstration of how to redirect 
 -- to different pages depending on who successfully 
 -- authenticates. In my example, it simply demonstrates 
 -- the ADMIN user going to page 1 and all other users going
 -- to page 100. Add you own logic here to detrmin which page 
 -- a user should be directed to post authentication.
 IF UPPER(p_uname) = 'ADMIN'
 THEN
  lv_goto_page := 1;
 ELSE
  lv_goto_page := 100;
 END IF;

 wwv_flow_custom_auth_std.login 
 (
  p_uname => p_uname,
  p_password => p_password,
  p_session_id => p_session_id,
  p_flow_page => p_flow_page || ':' || lv_goto_page
  );

EXCEPTION
WHEN OTHERS
THEN 
 RAISE;
END login;

procedure new_pwd
(
     p_username in varchar2
    ,p_password1 in varchar2
    ,p_password2 in varchar2
    ,p_password3 in varchar2
)
is
  old_pwd_hash varchar2(50);
  new_pwd_hash varchar2(50);
  valid_user_flag boolean:=false;
  
begin

/* get hash for old and new password*/
old_pwd_hash:=get_hash(upper(p_username),upper(p_password1));
new_pwd_hash:=get_hash(upper(p_username),upper(p_password2));

valid_user_flag:= valid_user(upper(p_username), old_pwd_hash);
if valid_user_flag = true then
    dbms_output.put_line('user present');
    update app_users
    set password = new_pwd_hash
    where username like upper(p_username);
    commit;    
    apex_application.g_print_success_message := 'Sucessfull! Please Login';
else    
    apex_application.g_print_success_message := 'Unsucessful! Please Register';    
end if;

EXCEPTION
WHEN NO_DATA_FOUND
THEN

raise_application_error (-20000, 'User does not exist. Please Register');

end new_pwd;

PROCEDURE valid_user2 (p_username IN VARCHAR2, p_password IN VARCHAR2)
AS
v_dummy VARCHAR2 (1);
BEGIN
SELECT '1'
INTO v_dummy
FROM app_users
WHERE UPPER (username) = UPPER (p_username)
AND password = p_password;
EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error (-20000, 'Invalid username / password.');
END valid_user2;

FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
BEGIN
valid_user2 (p_username, p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN RETURN FALSE;
END valid_user;

PROCEDURE add_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
AS
new_pwd_hash varchar2(50);
BEGIN
new_pwd_hash:=get_hash(upper(p_username),upper(p_password));

INSERT INTO app_users (username, password)
VALUES (UPPER (p_username),new_pwd_hash);

COMMIT;

EXCEPTION
WHEN OTHERS
THEN 
 ROLLBACK; 
 RAISE;
END add_user;


FUNCTION get_hash (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
input_string => UPPER (p_username) 
                || '/' 
                || UPPER (p_password));
END get_hash;
end apexauth;
/