This discussion is archived
2 Replies Latest reply: Nov 13, 2012 12:15 PM by Bobster13 RSS

Creating a custom Authentication Scheme

973944 Newbie
Currently Being Moderated

Im trying to create a custom Authentication scheme so users off a table called users which has a username and password column.

I have followed this example and when i run the following in SQL command it works

vresult varchar2(10);
if validate_user_from_db ('baig','oracle') then
end if;

The problem is after this, the tutorial says to create a Authentication scheme from scratch although when i try to create one i only get the following options:
1. Based on a pre-configured scheme from the gallery
2. As a copy of an existing authentication scheme

I have tried everything i can find online about solving this problem but nothing seems to work, i have tried putting the function name in the Authentication Function Name section as well but when i try to run the login page i still get "Invalid Login Credentials"

Is there anything i need to put withing the actual login page? this is what is stored within the "process" section of my login page

p_username => :P101_USERNAME,
p_password => :P101_PASSWORD );

I am using version

If anyone could help me it would be greatly appreciated as i seem to be going round in circles!


Edited by: 970941 on Nov 13, 2012 3:30 AM
  • 1. Re: Creating a custom Authentication Scheme
    MartinF Explorer
    Currently Being Moderated

    Select '1. Based on a pre-configured scheme from the gallery'

    Give it as name and select the Scheme Type of 'Custom'.

    You can then leave most fields blank, but set 'Authentication Fuction Name' to the name of your funcation (testing_signon is our case) and the PL/SQL Code to something like:
      FUNCTION testing_signon
        (p_username IN VARCHAR2,
         p_password IN VARCHAR2)
       -- Test signon function used in development ONLY
        IF UPPER(p_username) = UPPER(p_password) THEN
          RETURN TRUE;
        END IF;
    Thsi should get you started. You can then explore other options.

    Hope this helps.
  • 2. Re: Creating a custom Authentication Scheme
    Bobster13 Newbie
    Currently Being Moderated
    Hi Warbie118,
    I actually have this working....Let me try and help you.

    1st you need to create a PACKAGE...
    Go to SQL Workshop > SQL Commands
    Run this code...

    create or replace PACKAGE pkg_auth AS
    function authenticate(p_username in varchar2,
    p_password in varchar2) return boolean;

    Then run this code (Package Body)

    create or replace PACKAGE BODY pkg_auth AS
    function authenticate(p_username in varchar2,
    p_password in varchar2) return boolean is
    -- default the result to 0
    v_result integer := 0;
    -- store 1 in v_result if a matching row
    -- can be found
    select 1
    into v_result
    from user_table
    where username = p_username
    and password = p_password;

    -- return true if a matching record was founr
    return(v_result = 1);
    -- if no record was found then return false
    when no_data_found then
    return false;
    end authenticate;

    Your package is now ready...(You can see it in the Object Browser > Packages

    CREATE>BASED ON A PRE-CONFIGURED SCHEME > CUSTOM > NAME Pkg auth >SETTINGS> Authentication function name (pkg_auth.authenticate) > Enable legacy authentication attributes (Yes) accept remaining defaults...(should now be the current scheme)


    Notice in the Package Body you reference this line...+from user_table+
    You have to create the user table and call it USER_TABLE (or change the table name in the package body)

    I would create a 1 line spreadsheet with these fields...USERNAME & PASSWORD (create the table via the sql workshop upload to get the triggers etc...)
    Username is the primary key to prevent duplicates, or you can have APEX add an ID field primary key... your choice.

    Column Name     Data Type     Nullable     Default     Primary Key
    USERNAME     VARCHAR2(50)     No          1
    PASSWORD     VARCHAR2(30)     Yes          

    More fields can be added as needed for GEO, ADMIN etc...

    I'll keep an eye on this...

    Edited by: Bobster13 on Nov 13, 2012 12:14 PM


  • Correct Answers - 10 points
  • Helpful Answers - 5 points