1 2 Previous Next 15 Replies Latest reply on May 6, 2012 1:19 PM by Hesh

    Web application -custom authentication

    Hesh
      I am trying to create a custom authentication for a web application . Is this possible ? I find examples for DB applications .

      I did following for this and strucked badly..

      create table vl_emp_master (empid number,pwd varchar2(100));
      
      insert into vl_emp_master (empid,pwd) values (4781,'1234');
      
      insert into vl_emp_master (empid,pwd) values (4787,'1234');
      created a package
      create or replace package pkg_tr_usr as
       function tr_check_usr(p_uname varchar2,p_pwd varchar2) return boolean;
      end;
      
      CREATE OR REPLACE package body pkg_tr_usr as 
      function tr_check_usr(p_uname in varchar2,p_pwd varchar2)return boolean is
      v_ret integer :=0;
      begin
      
      select 1 into v_ret from vl_emp_master where empid=to_number(p_uname) and pwd=p_pwd and rownum=1;
      
      return (v_ret=1);
      
      exception
      when no_data_found then
      v_ret:=0;
      return (v_ret=0);
      end tr_check_usr;
      end;
      tested like this...

      declare
      bres boolean :=false;
      begin
      
      bres:= pkg_tr_usr.tr_check_usr('4781','1234');
      
      if bres=false then
      dbms_output.put_line('1');
      else
      dbms_output.put_line('0');
      end if;
      end;
      and did following changes in authentication process...

      In 'Application Properties'-->'Authentication-->'Authentication Function'-->return pkg_tr_usr.tr_check_usr;

      but not able to log in , getting following error..
      ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to 'TR_CHECK_USR' ORA-06550: line 2, column 1: PL/SQL: Statement ignored
      Thanks,
      Hesh
        • 1. Re: Web application -custom authentication
          jariola
          Hi,

          Your authentication function parameter names must be 'p_username' and 'p_password'
          >
          This function must return a boolean to the login procedure that calls it. It has 2 input parameters 'p_username' and 'p_password' that can be used to access the values an end user entered on the login page.
          >

          Regards,
          Jari

          -----
          My Blog: http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai
          1 person found this helpful
          • 2. Re: Web application -custom authentication
            Hesh
            Thank you,

            but how to give them? I tried with...
            return pkg_tr_usr.tr_check_usr( 'p_username' ,'p_password');
            
             ORA-06550: line 2, column 8: PLS-00224: object 'PKG_TR_USR.TR_CHECK_USR(p_username, p_password)' must be of type function or array to be used this way ORA-06550: line 2, column 1: PL/SQL: Statement ignored 
            and
            return pkg_tr_usr.tr_check_usr( p_username ,p_password);
            
            ORA-06550: line 2, column 33: PLS-00201: identifier 'P_USERNAME' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored 
               
            but did not work, any inputs please


            Thanks,
            • 3. Re: Web application -custom authentication
              913014
              You didn't understand him correctly

              Authentication function has 2 parameters 'p_username' and 'p_password', so you will need to change your authentication function to have those parameters like this:

              create or replace package pkg_tr_usr as

              function tr_check_usr(p_username IN VARCHAR2, p_password IN VARCHAR2) return boolean;

              end;

              CREATE OR REPLACE package body pkg_tr_usr as

              function tr_check_usr(p_username IN VARCHAR2, p_password IN VARCHAR2) return boolean IS

              v_ret integer :=0;
              begin

              select 1 into v_ret from vl_emp_master where empid=to_number(p_username) and pwd=p_password and rownum=1;
              return (v_ret=1);
              exception
              when no_data_found then
              v_ret:=0;
              return (v_ret=0);
              end tr_check_usr;

              end;

              This rownum=1 in select statement is strange, why would you check for username and password if u always select first row?
              I hope this helps you.
              • 4. Re: Web application -custom authentication
                Hesh
                Yes :), it seems working but I got this error...
                 You are not authorized to access this application or function.
                 
                Thanks,
                • 5. Re: Web application -custom authentication
                  913014
                  Hmm i haven't seen this error before but lets go through the process of creating custom authentication

                  For now you are good, you have authentication function inside database.

                  Now go to your application, then click on Shared components and you will see section "Security". Click on Authentication Schemas.
                  Inside you have 3 types (Application Express(default), Database, Database account).
                  Do not modify any of them, you will need to create your own authentication

                  So go Create, then type some name (for example MyCustomAuthentication), and press next until you get Authentication Function screen

                  Here you will need to select radio button that says "Use my custom authentication function" and in text area below you will type this :

                  return pkg_tr_usr.tr_check_usr;

                  After that step you can press next button untill process ends.

                  At the end you will see MyCustomAuthentication beside default 3 types. Now click on Change Current (in bar (Application Schemas, Subscription, Change Current, History)) and select MyCustomAuthentication.
                  • 6. Re: Web application -custom authentication
                    Hesh
                    Thanks for your reposes,

                    but 'Shared components' is exists for only 'Data base applications', my application is a 'web-sheet application'. Can you guide my what to use when 'Data-base applications' 'Web-sheet applications' both look similar to me.


                    Thanks,
                    • 7. Re: Web application -custom authentication
                      Hesh
                      got it but not sure whether it is a proper way to do it or not...

                      along with the steps I mentioned..

                      Edit Properties-->Authorization-->Edit Access control List-->Add user names from table which you have created earlier.

                      Thanks,
                      Hesh.
                      • 8. Re: Web application -custom authentication
                        913014
                        Yes for websheet application is a litte different.

                        In authentication section u need to set Custom authentication, and in field Authentication Function (default value -BUILTIN-) type return pkg_tr_usr.tr_check_usr;

                        After that, you need to set Authorization.
                        First go to Edit Access Control List, then click on Crete Entry and create user with Username
                        Username must be one from your database table.

                        I believe that's all. If it works plz mark this topic as answered.
                        • 9. Re: Web application -custom authentication
                          Hesh
                          This is working fine but is there any way to avoid this process of manually creating 'user'?

                          my problem is I have to create for four thousand (4000) employees !

                          Thanks,
                          Hesh.
                          • 10. Re: Web application -custom authentication
                            913014
                            Ok i will assume that apex is installed in the same schema as yours "vl_emp_master" table.

                            First you need to manually create admin user. All users in websheet application are stored in APEX$_ACL table. So we need to insert all 4000 in there automatically.

                            APEX$_ACL(ID, WS_APP_ID, USERNAME, PRIV, CREATED_ON, CREATED_BY, UPDATED_ON, UPDATED_BY)

                            We want to insert:
                            WS_APP_ID that is your application id (for testing i will assume that's 100)
                            USERNAME - empid from your "vl_emp_master" table
                            PRIV - A - admin, R - reader i believe and there is one more letter i cant remember
                            CREATED_ON, UPDATED_ON you can put here today's date
                            CREATED_BY, UPDATED_BY thats your workspace name (for testing i will assume that's 'Hesh')

                            Ok, so open your sqldeveloper, sqlworkshop or sqlplus doesn't matter and type:


                            SELECT * FROM APEX$_ACL, now you should see just your admin user.

                            Ok now we will insert all 4000 of them.
                            INSERT INTO APEX$_ACL (WS_APP_ID, USERNAME, PRIV, CREATED_ON, CREATED_BY, UPDATED_ON, UPDATED_BY)
                            SELECT 100, empid, 'R', '03.05.2012', 'Hesh', '03.05.2012', 'Hesh'
                            FROM vl_emp_master
                            Edited by: 910011 on May 3, 2012 2:10 PM
                            • 11. Re: Web application -custom authentication
                              Hesh
                              Awesome,

                              one thing where did you find this?

                              Thanks,
                              Hesh.
                              • 12. Re: Web application -custom authentication
                                913014
                                Well there is no topics available on websheet authentication, but i knew that apex store everything in database. So i stared with this:

                                select owner, table_name, column_name from all_tab_columns where column_name like '%USERNAME%' AND OWNER <> 'SYS'

                                When i saw ACL that was it :)
                                • 13. Re: Web application -custom authentication
                                  Hesh
                                  Thank you,

                                  That would be usefull for future issues !

                                  Hesh.
                                  • 14. Re: Web application -custom authentication
                                    Martin1
                                    Hi Hesh,

                                    i created a ProofOfConcept document regarding table driven authentication. If you are interested i can send you via email.

                                    Regards,
                                    Martin
                                    1 2 Previous Next