Implement Custom Authentication Scheme in Oracle Application Express - Part III

CREATE TABLE IN ORACLE APEX

While creating the application we used the default Application Express Authentication Scheme (step 9) to access the application. In this exercise, you will create a custom authentication scheme based on a table named DEMO_USERS. This table will carry all users of our application with their respective obfuscated passwords. The key components of this authentication scheme are CUSTOM_AUTH and CUSTOM_HASH functions. These two functions compare the given username and password to the stored values in the DEMO_USERS table. If there is a match, the user is authenticated to access the application. Besides authentication, the table also contains couple of columns (Products and Admin_User) to incorporate authorization. Follow the steps mentioned below to create this important table to fulfill the custom authentication and  authorization requirements of our application. In SQL Commands under SQL Workshop, enter the following statements individually (ignoring the underlined text):

Statement # 1 - Create DEMO_USERS table:
CREATE TABLE "DEMO_USERS"
( "USER_ID" NUMBER,
"USER_NAME" VARCHAR2(100),
"PASSWORD" VARCHAR2(4000),
"CREATED_ON" DATE,
"QUOTA" NUMBER,
"PRODUCTS" CHAR(1),
"EXPIRES_ON" DATE,
"ADMIN_USER" CHAR(1),
CONSTRAINT "DEMO_USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)

Statement # 2 - Create Trigger
CREATE OR REPLACE TRIGGER "BI_DEMO_USERS"
BEFORE
insert on "DEMO_USERS"
for each row
begin
for c1 in (
select DEMO_USERS_SEQ.nextval next_val
from dual
) loop
:new.USER_ID := c1.next_val;
:new.admin_user := 'N';
:new.created_on := sysdate;
end loop;
end;

Statement # 3 - Enable Trigger
ALTER TRIGGER "BI_DEMO_USERS" ENABLE

Statement # 4 - Create Another Trigger
CREATE OR REPLACE TRIGGER "DEMO_USERS_T1"
BEFORE
insert or update on "DEMO_USERS"
for each row
begin
:NEW.user_name := upper(:NEW.user_name);
end;

Statement # 5 - Enable Trigger
ALTER TRIGGER "DEMO_USERS_T1" ENABLE
 
Statement # 6 - Create Sequence to auto-generate User_ID column
CREATE SEQUENCE "DEMO_USERS_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 121 CACHE 20 NOORDER NOCYCLE
 
Statement # 7 - Add Application Administrator Record
INSERT INTO demo_users (USER_NAME, PASSWORD, CREATED_ON, QUOTA, PRODUCTS, EXPIRES_ON, ADMIN_USER) values
('ADMIN','0CF8137A4E6A77A777C30D4AA85AC5DE', TO_DATE('01-01-2013','MM-DD-YYYY'), 0, 'Y', TO_DATE('12-31-
2020','MM-DD-YYYY'),'Y')

Statement # 8 - Add Application Developer Record
INSERT INTO demo_users (USER_NAME, PASSWORD, CREATED_ON, QUOTA, PRODUCTS, EXPIRES_ON, ADMIN_USER) values
('DEMO','25F743BE60A13BC099A61DF1B8E734F7', TO_DATE('01-01-2013','MM-DD-YYYY'), 1000, 'Y', TO_DATE('12-31-
2020','MM-DD-YYYY'), 'N')

Statement # 9 - Add Application User Record
INSERT INTO demo_users (USER_NAME, PASSWORD, CREATED_ON, QUOTA, PRODUCTS, EXPIRES_ON, ADMIN_USER) values
('TEST','988CB30F1EDE09BD710366F12475FED1', TO_DATE('01-01-2013','MM-DD-YYYY'), 2000, 'N', TO_DATE('12-31-
2020','MM-DD-YYYY'), 'N')

In the last three statements (7,8, and 9), you created three application users: Admin, Demo, and Test. You added these records to the table with obfuscated passwords: asa, demo, and test respectively. Once you add these three records, you can access the application with any of these credentials. But, first you need to add couple of database functions to handle the custom authentication scheme. Continue >>>

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"