2 Replies Latest reply on Nov 13, 2012 8:15 PM by Bobster13-Oracle

    Creating a custom Authentication Scheme


      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 http://www.baigzeeshan.com/2010/08/how-to-setup-custom-authentication-in.html 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

          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)
             RETURN BOOLEAN 
             -- Test signon function used in development ONLY
              IF UPPER(p_username) = UPPER(p_password) THEN
                RETURN TRUE;
              END IF;
              RETURN FALSE;
          Thsi should get you started. You can then explore other options.

          Hope this helps.
          • 2. Re: Creating a custom Authentication Scheme
            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