Search
  • Bheem

How to change custom authentication in oracle apex.

Step 1: Login to your apex workspace.

Step 2: Click on your application.



Step 3: Click on shared components.



Step 4: Click on authentication schema.


Step 5: Click on existing scheme or create new scheme.

Step 6: change the scheme type & Authentication Function Name then click on apply changes.


PL/SQL Package

Package Spec

CREATE OR REPLACE PACKAGE ecom_login AS
    FUNCTION authenticate (
        p_username   IN   VARCHAR2,
        p_password   IN   VARCHAR2
    ) RETURN BOOLEAN;

    FUNCTION get_authorization_level (
        p_username VARCHAR2
    ) RETURN VARCHAR2;

END ecom_login;

Package Body

CREATE OR REPLACE PACKAGE BODY ecom_login AS
    FUNCTION authenticate (
        p_username   IN   VARCHAR2,
        p_password   IN   VARCHAR2
    ) RETURN BOOLEAN IS
        v_result     INTEGER := 0;
        l_password   VARCHAR2(100);
    BEGIN
        SELECT
            passwordhash
        INTO l_password
        FROM
            users
        WHERE
            ( upper(email) = upper(p_username)
              OR mobile = p_username )
            AND active_status = 'Y';

        IF l_password = p_password THEN
            RETURN true;
        ELSE
            RETURN false;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN false;
    END authenticate;

    FUNCTION get_authorization_level (
        p_username VARCHAR2
    ) RETURN VARCHAR2 IS
        p_role CHAR(1);
    BEGIN
        SELECT
            user_role
        INTO p_role
        FROM
            users
        WHERE
            upper(email) = upper(p_username)
            OR mobile = p_username;

        RETURN p_role;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
    END get_authorization_level;

END ecom_login;



20 views0 comments

Recent Posts

See All

How to create network ACL in oracle database.

when you are calling third party API in oracle using UTL_HTTP then you will face Network access denied by ACL error if ACL is not created. To create ACLfirst login with sys account then run below code