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

          — Write to
 Session, Notification must enter a username and 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

                 — Write
 to Session, User not found.

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

           Return False;

     End;

     If v_Password <>
 p_Password Then   

        — Write to
 Session, Password incorrect.

        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;

     —

     — Write user
 information to Session.

     —

     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

        — Redirect to
 Page 1 (Home Page).

        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

        — Login Failure,
 redirect to page 101 (Login Page).

        Owa_Util.Redirect_Url('f?p=&APP_ID.:101:&SESSION.');

     End If;

  End;

End Pkg_Security;

/

应用程序项定义：

使用应用程序项可以维护会话状态。应用程序项可以通过使用计算, 处理或在 URL 中传递值来设置。使用 "新建实例时" 计算可以为会话设置一次项值。使用应用程序项可以维护未显示并且未指定给任何页的会话状态。