Shulou(Shulou.com)05/31 Report--

Editor to share with you how APEX login through the verification of user information in the database, I hope you will gain something after reading this article, let's discuss it together!

1. Custom Authentication in Oracle APEX1.1. Introduction

This document is based on:

Oracle APEX 5.0

Oracle APEX 5.1.4 Test available

1.2. Default authentication of APEX

The default login page of APEX is 101, and the default authentication method is APEX user authentication. The main test of this paper is to verify and log in through the user information in the database.

1.3. SQL Script

1) create a user information table USER_ACCOUNT

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',' Yee, 'tom@example.com',' Tom')

Insert into user_account (USER_NAME, PASSWORD, USER_TYPE,ACTIVE, EMAIL, FULL_NAME)

Values ('jerry',' jerry123', 'user',' Yee, 'jerry@example.com',' Jerry')

Insert into user_account (USER_NAME, PASSWORD, USER_TYPE,ACTIVE, EMAIL, FULL_NAME)

Values ('donald',' donald123', 'guest',' Night, 'donald@example.com',' Donald')

Commit

2) create a stored procedure PKG_SECURITY

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 (paired user name paired 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 ('favored paired appended ID.VO11VOLARO SESSION.')

End If

End

End Pkg_Security

/

1.4. Declaring Application Items

3) in the application, click "shared components"Application items"

Application item definition:

Use application items to maintain session state. Application items can be set by using calculations, processing, or passing values in URL. Use the when you create a new instance calculation to set the item value once for the session. Use application items to maintain session state that is not displayed and not assigned to any page.

4) Click "create" button:

Enter the corresponding attribute value:

Name: LOGIN_MESSAGE

Range: Application

Then click "create Application item" at the top right

Application items can be called later in PL/SQL

Such as: Apex_Util.Set_Session_State ('LOGIN_MESSAGE','User not found')

Again, create two more application items:

SESSION_USER_NAME

SESSION_EMAIL

1.5. Custom Authentication

5) Edit the "login page" in the application, and the login page number is 101

6) right-click "Content Body" to create a new area.

7) Select the new area and modify the attribute value:

Identification "title: LOGIN_MESSAGE

Identity type: static content

Source text: & LOGIN_MESSAGE. Pay attention to the end with ".", if not, the value of the variable cannot be returned.

Server condition type: item is not null

Server condition item: select application item: LOGIN_MESSAGE

8) switch to the "processing" tab on the left navigation bar, replace "handle", "handle", "Login", and modify the "source"PL/SQL code"

Default value:-- apex_authentication.login (p_username = >: P101 USERNAME journal pendant password = >: P101_PASSWORD)

Replacement value: Pkg_Security.Process_Login (: P101passport USERNAMErect lemonade P101roomPASSWORDJR APPPlayID)

9) Save and run the test

Attach the user information in the test table

User name user password user status

Tom tom123 Y

Jerry jerry123 Y

Donald donald123 N

After reading this article, I believe you have a certain understanding of "how APEX logs in through the verification of user information in the database". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.