Tùy chỉnh xác thực trong Oracle APEX

create table USER_ACCOUNT
(
  USER_NAME VARCHAR2(30) not null,
  PASSWORD  VARCHAR2(30) not null,
  USER_TYPE VARCHAR2(10) not null,
  ACTIVE    VARCHAR2(1) not null,
  EMAIL     VARCHAR2(64) not null,
  FULL_NAME VARCHAR2(64) not null
) ;
 
alter table USER_ACCOUNT
  add constraint USER_ACCOUNT_PK primary key (USER_NAME) ;
alter table USER_ACCOUNT
  add constraint USER_ACCOUNT_UK unique (EMAIL) ;

-----------------------------------

insert into user_account (USER_NAME, PASSWORD, USER_TYPE,
 ACTIVE, EMAIL, FULL_NAME)
values ('tom', 'tom123', 'admin', 'Y', 'tom@example.com', 'Tom');

insert into user_account (USER_NAME, PASSWORD, USER_TYPE,
ACTIVE, EMAIL, FULL_NAME)
values ('jerry', 'jerry123', 'user', 'Y', 'jerry@example.com', 'Jerry');

insert into user_account (USER_NAME, PASSWORD, USER_TYPE,
ACTIVE, EMAIL, FULL_NAME)
values ('donald', 'donald123', 'guest', 'N', 'donald@example.com', 'Donald');

Commit;

Create Or Replace Package Pkg_Security Is

   Function Authenticate_User(p_User_Name Varchar2
                             ,p_Password  Varchar2) Return Boolean;

   -----
   Procedure Process_Login(p_User_Name Varchar2
                          ,p_Password  Varchar2
                          ,p_App_Id    Number);

End Pkg_Security;
/
Create Or Replace Package Body Pkg_Security Is

   Function Authenticate_User(p_User_Name Varchar2
                             ,p_Password  Varchar2) Return Boolean As
      v_Password User_Account.Password%Type;
      v_Active   User_Account.Active%Type;
      v_Email    User_Account.Email%Type;
   Begin
      If p_User_Name Is Null Or p_Password Is Null Then
         -- Thông báo nhập user name và password.
         Apex_Util.Set_Session_State('LOGIN_MESSAGE'
                                    ,'Please enter Username and password.');
         Return False;
      End If;
      ----
      Begin
         Select u.Active
               ,u.Password
               ,u.Email
         Into   v_Active
               ,v_Password
               ,v_Email
         From   User_Account u
         Where  u.User_Name = p_User_Name;
      Exception
         When No_Data_Found Then
            -- Ghi vào Session user không tồn tại.
            Apex_Util.Set_Session_State('LOGIN_MESSAGE'
                                       ,'User not found');
            Return False;
      End;
      If v_Password <> p_Password Then
         -- Thông báo mật khẩu không chính xác.
         Apex_Util.Set_Session_State('LOGIN_MESSAGE'
                                    ,'Password incorrect');
         Return False;
      End If;
      If v_Active <> 'Y' Then
         Apex_Util.Set_Session_State('LOGIN_MESSAGE'
                                    ,'User locked, please contact admin');
         Return False;
      End If;
      ---
      -- Ghi các thông tin người dùng vào Session (Để sử dụng về sau).
      --
      Apex_Util.Set_Session_State('SESSION_USER_NAME'
                                 ,p_User_Name);
      Apex_Util.Set_Session_State('SESSION_EMAIL'
                                 ,v_Email);
      ---
      ---
      Return True;
   End;

   --------------------------------------
   Procedure Process_Login(p_User_Name Varchar2
                          ,p_Password  Varchar2
                          ,p_App_Id    Number) As
      v_Result Boolean := False;
   Begin
      v_Result := Authenticate_User(p_User_Name
                                   ,p_Password);
      If v_Result = True Then
         -- Chuyển tới trang 1 theo mặc định (Trang HOME).
         Wwv_Flow_Custom_Auth_Std.Post_Login(p_User_Name -- p_User_Name
                                            ,p_Password -- p_Password
                                            ,v('APP_SESSION') -- p_Session_Id
                                            ,p_App_Id || ':1' -- p_Flow_page
                                             );
      Else
         -- Chuyển về trang login
         Owa_Util.Redirect_Url('f?p=&APP_ID.:101:&SESSION.');
      End If;
   End;

End Pkg_Security;
/

Apex_Util.Set_Session_State('LOGIN_MESSAGE'
                                             ,'User not found');

Pkg_Security.Process_Login(:P101_USERNAME,
                           :P101_PASSWORD,
                           :APP_ID);