Custom Authentication Scheme on Oracle APEX

Oracle Application Express (APEX) has a mechanism for securing its contents. There are two main mechanisms available on Oracle APEX for securing its contents which are Authentication and Authorization. In brief authentication is used for securing contents from outside of application by providing one login page while authorization is used for securing specific contents after authentication has been done.

CUSTOM AUTHENTICATION SCHEMES

There are several kind of authentication schemes available on Oracle Application Express (APEX). One of them is custom authentication scheme. With custom authentication scheme we can provide our own PL/SQL script to control the authentication mechanism.

Here are steps for creating custom authentication scheme on Oracle APEX:

1. Create a table with the name tlogin, a sequence and a trigger

--create table

CREATE TABLE tlogin (

 id NUMBER NOT NULL,

 username VARCHAR2 (8) NOT NULL,

 password VARCHAR2 (64) NOT NULL,

 status VARCHAR2 (8),

 CONSTRAINT pk_tlogin PRIMARY KEY (id)

);

--create sequences

CREATE SEQUENCE sqidlogin;

--create trigger

CREATE OR REPLACE TRIGGER trg_idlogin

BEFORE INSERT

ON tlogin

FOR EACH ROW

BEGIN

SELECT sqidlogin.NEXTVAL INTO: NEW.id FROM dual;

END trg_idlogin;

2. Create a new app, for example name it with room with id 114

3. Click Shared Component, in the Security area click Authentication Schemes

4. Click the Create button. Namely otentikasi_ruangan. In the Schema Type select Custom option.

5. In the Source section fill in the following code:

FUNCTION cek_otentikasi (p_username IN VARCHAR2, p_password IN VARCHAR2)

RETURN BOOLEAN

US

  rst NUMBER: = 0;

BEGIN

  SELECT 1 INTO rst FROM tlogin

WHERE UPPER (username) = UPPER (p_username)

AND password = p_password;

  RETURN TRUE;

  EXCEPTION WHEN NO_DATA_FOUND THEN

RETURN FALSE;

END cek_otentikasi;

6. In the Settings section, fill in the cek_otentikasi (function name in Source) in the Authentication Function Name field. Click the Create button.

7. Go back to Page Definition, click the Edit Application Properties button (top right) then click the Security tab

8. In the Authentication sub tab select the otentikasi_ruangan Authentication Scheme field and click Apply Changes

9. Run (RUN) the application, now the login is determined based on the username and password listed in the TLOGIN table (you should fill the tlogin tables with some values)

That’s it the steps for creating custom authentication scheme on Oracle Application Express (APEX). As the result when the application is running and user do the login process on login page, only listed users on tlogin table is allowed to be authenticated.

Thank You - Bobsis