Custom Authentication in Oracle APEX

 Custom Authentication in Oracle APEX

 This blog demonstrate the steps for Custom Authentication in Oracle APEX.

Step1: We Need a table Login.

    

CREATE TABLE  "LOGIN"

   (           "LOGIN_PK" NUMBER(7,0) NOT NULL ENABLE,

                "ENTITY_OWNER_FK" NUMBER(7,0) NOT NULL ENABLE,

                "ENTITY_FK" NUMBER(2,0) NOT NULL ENABLE,

                "USERNAME" VARCHAR2(20) NOT NULL ENABLE,

                "PASSWORD" VARCHAR2(20) NOT NULL ENABLE,

"EMAIL" VARCHAR2(50),

 CONSTRAINT "LOGIN_PK" PRIMARY KEY ("LOGIN_PK") ENABLE,

                 CONSTRAINT "LOGIN_UK1" UNIQUE ("ENTITY_OWNER_FK", "ENTITY_FK") ENABLE,

                 CONSTRAINT "LOGIN_UK2" UNIQUE ("USERNAME") ENABLE

   )

/

 Step2: We Need a table ENTITY_TYPE.

CREATE TABLE  "ENTITY_TYPE"

   (           "ENTITY_PK" NUMBER(2,0) NOT NULL ENABLE,

                "ENTITY_TYPE" VARCHAR2(15) NOT NULL ENABLE,

CONSTRAINT "ENTITY_TYPE_PK" PRIMARY KEY ("ENTITY_PK") ENABLE

   )

/

ALTER TABLE  "LOGIN" ADD CONSTRAINT "LOGIN_ENTITY_TYPE_FK1" FOREIGN KEY ("ENTITY_FK")

                  REFERENCES  "ENTITY_TYPE" ("ENTITY_PK") ENABLE

/

 

 Step3: Now we will create a package which will be used for Authentication.

 

create or replace PACKAGE "DV_PKG" AS

 FUNCTION login_redirect_fun(uname IN VARCHAR2) RETURN varchar2;

FUNCTION login_valid_fun(p_username IN VARCHAR2,p_password IN VARCHAR2) RETURN boolean;

END DV_PKG;

 

PACKAGE BODY:

 

create or replace PACKAGE BODY "DV_PKG" AS

FUNCTION login_redirect_fun(UNAME IN VARCHAR2) RETURN VARCHAR2 AS

  V_PAGE_ID  VARCHAR2(45);

  BEGIN

         SELECT DECODE(ENTITY_FK,1,'100',2,'200',3,'300',4,'400') INTO V_PAGE_ID FROM LOGIN WHERE lower(username)=lower(UNAME);

         RETURN ':'||V_PAGE_ID;

 

  Exception when no_data_found then

      return ':5';

  END login_redirect_fun;

 

 FUNCTION login_valid_fun(p_username IN VARCHAR2,p_password IN VARCHAR2) RETURN boolean AS

  V_PAGE_ID  VARCHAR2(45);

  count1 number:=0;

  count2 number:=0;

 

  BEGIN

    if p_username is not null AND p_password is not null then

         select 1 into count1

         from login

         where LOWER(username)= LOWER(p_username) and LOWER(password) = LOWER(p_password);

 

         if count1=1 then

              return true;

         end if;

     else

            return false;

 

     end if;

 

   Exception when no_data_found then

      return false;

 

END login_valid_fun;

END DV_PKG;

 Step4: Now go to Shared Component->Authentication Scheme and Create New Authentication Scheme

 

 Step5: Now go to Login Page -> Processing and replace the existing code with following code.

   
    wwv_flow_custom_auth_std.login(
    P_UNAME       => :P9999_USERNAME,
    P_PASSWORD    => :P9999_PASSWORD,
        P_SESSION_ID  => APEX_APPLICATION.get_session_id,
    P_FLOW_PAGE   => :APP_ID||oratrix.dv_pkg.login_redirect_fun(:P9999_USERNAME)
    );
    

//In Above example oratrix is the Schema name you should replace it with your schema name.

Have a Happy Learning

Regards

Digvijaysinh Virpura