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.
Your post is too helpful
ReplyDeleteGlad it help !
DeleteThank you so much for your code on authentication scheme. I am wondering if you can expand this to include to email validation and reset of password.
ReplyDelete