Monday, March 12, 2012

Mixed authentication in Apex



Some time ago, I have described how to authenticate from Oracle Forms (or any other app) to Apex without prompting for username and password. In this scenario I was using one table for users (xe_users) which was holding all users that are allowed to login to Apex application. All about this solution can be read in one of mine previous postings on Apex theme, HROUG 2010.

Today post will cover a little different approach that come recently on mine desk. A task was to enable mixed authentication for Oracle platform. Task demands may be shortly described as: User need to authenticate in Apex app either with Oracle credentials or one associated with real Apex app.
  1. First problem is that we do not know Oracle real user credentials and do not want to save permanently any of those values. So we need to check login from Apex session and for that we need another session.
  2. Second, if user doesn't exists as real Oracle user, placed credentials has to be checked against Apex classic authentication and if they are OK, user may pass login.

The Solution

So, solution may be explained in next simplified steps:
  1. Create interface that will check Oracle credentials
  2. Create interface that will perform mixed authentication checking (against wished rules)
All should fit in existing custom authentication Apex model (mentioned before). And in mine case all objects are set in one schema (TOOLS) to facilitate security policy and maintenance.

Java part

To be able to create and run another session from current session in PL/SQL, probably the best way in Oracle is java. This is true because java is officially supported by default in any Oracle database after 9i. For that I have (with great help of mine colleague Goran Hafner) created java source directly in Oracle database. Here is the code:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CheckConnection" AS
/* $Header: CheckConnection.java 1.1 03/06/2012 11:10 damirv $ */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/* ------------------------------------------------------------------------------ 
* Filename: CheckConnection.java
* Purpose : Methods that make new session to database and check availability to select data 
*           with passed query string
*
* Date    : 22.02.2012.
* Author  : Damir Vadas
*           with GREAT help of Mr. Goran Hafner
*
* Remarks : 
*
* Changes (DD.MM.YYYY):
*         06.03.2012. Damir Vadas
*                     Added two overload methods for RAC (non generic) connections  
* ------------------------------------------------------------------------------ */

public class CheckConnection {

  private static PreparedStatement pstmt;
  private static Connection conn;

  /* Check connectivity to other database in new session withe elemenatl SELECT based query   */
  public static int doCheckDbConnection (String p_user_name, String p_password, String p_host, String p_db_name, String p_port) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_host+":"+p_port+":"+p_db_name, p_user_name, p_password);  
      String sql = "select 1 from dual ";
      // System.out.println("Try to run query ... "+sql);
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      pstmt.close();
      try {
        conn.close();
      }
      catch (SQLException ignore) { }
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }

  /* overload method for RAC environmentor any kind of non generic connection string */
  /* example of p_conn for RAC with 4 nodes (vip1-4) all on 1521 port and service name "RAC_TAF_LB":
   *             jdbc:oracle:thin:@(DESCRIPTION = (FAILOVER=ON)
   *                                              (LOAD_BALANCE=ON)
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))
   *                                              (CONNECT_DATA =(SERVER = DEDICATED)
   *                                                             (SERVICE_NAME = RAC_TAF_LB)
   *                                                                            (FAILOVER_MODE=(TYPE=session) 
   *                                                                            (METHOD=basic)
   *                                                             )
   *                                              )
   *                               )
   */  
  
  public static int doCheckDbConnection (String p_user_name, String p_password, String p_conn) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_conn, p_user_name, p_password);  
      String sql = "select 1 from dual ";
      // System.out.println("Try to run query ... "+sql);
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      pstmt.close();
      try {
        conn.close();
      }
      catch (SQLException ignore) { }
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }


  /* Check SELECT based query in own session (caller session-good for checkink uncatchable PLSQL exceptions like dblink based queries)   */
  public static int doCheckSQL (String p_sql, String p_user_name, String p_password, String p_host, String p_db_name, String p_port) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_host+":"+p_port+":"+p_db_name, p_user_name, p_password);  
      // System.out.println("Try to run query ... "+p_sql);
      pstmt = conn.prepareStatement(p_sql);
      pstmt.execute();
      pstmt.close();
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }

  /* overload method for RAC environmentor any kind of non generic connection string */
  public static int doCheckSQL (String p_sql, String p_user_name, String p_password, String p_conn) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_conn, p_user_name, p_password);  
      // System.out.println("Try to run query ... "+p_sql);
      pstmt = conn.prepareStatement(p_sql);
      pstmt.execute();
      pstmt.close();
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }
}
/
Beside in java commented part there is whole description and examples, interesting part of this code is that it support classic JDBC connection (5 parameters methods) as well as RAC or specific part of connections (3 parameters methods).
First method doCheckDbConnection, is intended to create Oracle session with passed credentials and perform elemental query (to be able to make this query create session privilege is enough and this privilege is least an Oracle user might have to create connection).
  1. doCheckDbConnection (String p_user_name, String p_password, String p_host, String p_db_name, String p_port)
  2. doCheckDbConnection (String p_user_name, String p_password, String p_conn)
Other two override methods have ability to perform custom query that might not be catch in classic Oracle PLSQL code-i.e. db_link tables:


  • doCheckSQL (String p_sql, String p_user_name, String p_password, String p_host, String p_db_name, String p_port)
    1. doCheckSQL (String p_sql, String p_user_name, String p_password, String p_conn)
    All four exposed public methods return "1" for successful completion and "-1" for any kind of error. All methods are wrapped in exception handler so result must always pass mentioned values.

    Publish java class

    After recompiling class next step is to publish java class through PLSQL code. This is the way how Oracle handle java into PLSQL code. Here is the code for that step:
    CREATE OR REPLACE PACKAGE CHECKCONNECTION AS
    /* $Header: TOOLS.JAVA_PKG.pks 1.1 03/06/2011 11:30 damirv $ */
    
    /*--------------------------------------------------------------------------------------------------------------------
     NAME    : CHECKCONNECTION.pks
     PURPOSE : PLSQL published method for java class CheckConnection
    
     Date    : 22.02.2012.
     Author  : Damir Vadas
    
     Remarks : 
    
       Changes (DD.MM.YYYY, Name, CR/TR#):
    -------------------------------------------------------------------------------------------------------------------- */
    
    FUNCTION doCheckDbConnection(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2, Param4 VARCHAR2, Param5 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckDbConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckDbConnection(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckDbConnection(java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2, Param4 VARCHAR2, Param5 VARCHAR2, Param6 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2, Param4 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    end;
    /
    
    This interface has only specification part. Be aware that Java is case sensitive language so it's declaration must follow this rule.

    The PLSQL wrapper

    Last step dealing with Java is to create PLSQL wrapper. This code will be called from all other PLSQL parts.
    CREATE OR REPLACE PACKAGE "JAVA_PKG" AUTHID CURRENT_USER AS
    /* $Header: TOOLS.JAVA_PKG.pks 1.1 03/06/2011 11:40 damirv $ */
    /*--------------------------------------------------------------------------------------------------------------------
     NAME    : JAVA_PKG
     PURPOSE : Handle with all kind of Java based classes wrapped in this package
    
     Date    : 22.02.2012.
     Author  : Damir Vadas
    
     Remarks : Granted to public
               Might have somne aditional privileges to run. 
               I.E. "dbms_java.grant_permission"
               
               Each user must have correct aditional privileges to query host ina way: 
    
               BEGIN
                  SYS.DBMS_JAVA.GRANT_PERMISSION(
                     grantee           => 'TOOLS'
                    ,permission_type   => 'SYS:java.net.SocketPermission'
                    ,permission_name   => 'servername1'
                    ,permission_action => 'resolve'
                     ,key               => KEYNUM
                    );
                END;
                /
               
                DECLARE
                  KEYNUM NUMBER;
                BEGIN
                  SYS.DBMS_JAVA.GRANT_PERMISSION(
                     grantee           => 'TOOLS'
                    ,permission_type   => 'SYS:java.net.SocketPermission'
                    ,permission_name   => '192.168.0.11:1521'
                    ,permission_action => 'connect,resolve'
                     ,key               => KEYNUM
                    );
                END;
                / 
    
    
       Changes (DD.MM.YYYY, Name, CR/TR#):
                29.02.2012  Damir Vadas
                            ORA-29532 , wrong Java call in plsql interface
                06.03.2012  Damir Vadas
                            Added two methods for RAC or non standard connection strings
    -------------------------------------------------------------------------------------------------------------------- */
    
    FUNCTION doCheckDbConnection (p_user_name IN VARCHAR2,
                                  p_password  IN VARCHAR2,
                                  p_host      IN VARCHAR2,
                                  p_db_name   IN VARCHAR2,
                                  p_port      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckDbConnection"         
    -- For each connection (different server or port, next two statements has to be run as sys. 
    -- Example for teb-ref server and port 1521 :
    -- exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.net.SocketPermission','teb-ref', 'resolve' );
    -- exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.net.SocketPermission', '192.168.168.109:1521', 'connect,resolve' );
    
    --SQL> select java_pkg.doCheckDbConnection ('USER1','user1','teb-ref','edis','1521') from dual;
    --
    --XE_AUTH_MIX_PKG.DOCHECKDBCONN
    --------------------------------
    --                             1
    --
    --SQL>
    
    FUNCTION doCheckDbConnection (p_user_name IN VARCHAR2,
                                  p_password  IN VARCHAR2,
                                  p_conn      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckDbConnection" for non standard or RAC connection
    -- Example for 4 nodes (vip 1-4) all on 1521 port, with RAC_TAF_LB oracle service        
    -- SQL> select java_pkg.doCheckDbConnection ('USER1','user1','(DESCRIPTION = (FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_TAF_LB)(FAILOVER_MODE=(TYPE=session) (METHOD=basic))))') from dual;
    --
    -- JAVA_PKG.DOCHECKDBCONNECTION('USER1','user1','(DESCRIPTION=(FAILOVER=ON)(LO
    ----------------------------------------------------------------------------------
    --                                                                               1
    --
    -- SQL>
    --------------------------------------------------------------------------------------------------------------------------
    FUNCTION doCheckSQL (p_sql       IN VARCHAR2,
                         p_user_name IN VARCHAR2,
                         p_password  IN VARCHAR2,
                         p_host      IN VARCHAR2,
                         p_db_name   IN VARCHAR2,
                         p_port      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckSQL"
    -- Good for checking DB links which cannot be trapped in PLSQL exception part!
    
    --SQL> select java_pkg.doCheckSQL ('select 1 from dual@geaprod','USER1','user1','teb-ref','edis','1521') from dual;
    --
    --JAVA_PKG.DOCHECKSQL('SELECT1
    ------------------------------
    --                           1
    --
    --SQL> select java_pkg.doCheckSQL ('select 1 from dual@hact','USER1','user1','teb-ref','edis','1521') from dual;
    --
    --JAVA_PKG.DOCHECKSQL('SELECT1
    ------------------------------
    --                          -1
    --
    --SQL> 
    
    FUNCTION doCheckSQL (p_sql       IN VARCHAR2,
                         p_user_name IN VARCHAR2,
                         p_password  IN VARCHAR2,
                         p_conn      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckSQL" for non standard or RAC connection
    -- example is similar like  previous overload "doCheckDbConnection(p_user_name, p_password,p_conn)" 
    
    END JAVA_PKG;
    /
    
    Be aware that:
    • This package has AUTHID Clause, what bring us to ability to grant it to PUBLIC, without compromising any security
    • To be able to run this package, additional SYS.DBMS_JAVA.GRANT_PERMISSION has to be granted to invoker.
    PAckage body is very easy-it just run previously defined published PLSQL package.
    CREATE OR REPLACE PACKAGE BODY "JAVA_PKG" 
    AS
    FUNCTION doCheckDbConnection (
                                  p_user_name   IN VARCHAR2,
                                  p_password    IN VARCHAR2,
                                  p_host        IN VARCHAR2,
                                  p_db_name     IN VARCHAR2,
                                  p_port        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckDbConnection (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckDbConnection (
                                  p_user_name   IN VARCHAR2,
                                  p_password    IN VARCHAR2,
                                  p_conn        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckDbConnection (java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL (
                         p_sql         IN VARCHAR2,
                         p_user_name   IN VARCHAR2,
                         p_password    IN VARCHAR2,
                         p_host        IN VARCHAR2,
                         p_db_name     IN VARCHAR2,
                         p_port        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL (
                         p_sql         IN VARCHAR2,
                         p_user_name   IN VARCHAR2,
                         p_password    IN VARCHAR2,
                         p_conn        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
      
    END JAVA_PKG;
    /
    

    The XE_AUTH_MIX_PKG package

    In mentioned previous example of automated Apex authentication, I was using XE_AUTH_PKG, so now this code should be placed in additional XE_AUTH_MIX_PKG package. It's intention is to perform mixed authentication:
    CREATE OR REPLACE PACKAGE "XE_AUTH_MIX_PKG" AS
    /* $Header: xe_auth_pkg.pks 1.1 03/06/2012 11:40 damirv $ */
    /*--------------------------------------------------------------------------------------------------------------------
     NAME    : xe_auth_mix_pkg
     PURPOSE : Autentification module against XE_USERS table with some kind of DB authentification-mix of both!
    
     Date    : 21.02.2012.
     Author  : Damir Vadas
    
     Remarks : This package use XE_AUTH_PKG and all XE_* based objects and their intention is to make extension for 
               core xe_auth authentication ..
               Internaly uses java source TOOLS."CheckConnection" which is heavily used in this pkg
    
               Use JAVA_PKG to check db connectivity and credentials.
    
     Changes (DD.MM.YYYY, Name, CR/TR#):
              06.03.2012, Damir Vadas
                          Added two overload functions that deal with non standard connection string (RAC etc)
    -------------------------------------------------------------------------------------------------------------------- */
    
    -- Very dangerous in real WEB because each check_password open new DB connection to retrieve correct credentials
    -- Function return:
    --           0 if user is not in XE_USERS table
    --           1 if authentication is OK (rather DB side or against xe_users auth)
    --          -1 if authentication failed (rather DB side or against xe_users auth)
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER;
    -- set serveroutput on size 123456;
    -- 
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int := XE_AUTH_MIX_PKG.check_password(p_ID=>1, p_PASSWORD=>'user1',p_HOST=>'teb-ref',p_DB_NAME=>'edis',p_PORT=>'1521');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
    
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER;
    -- set serveroutput on size 123456;
    -- 
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int := XE_AUTH_MIX_PKG.check_password(p_ID=>1, p_PASSWORD=>'user1',p_CONN=>'(DESCRIPTION = (FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_TAF_LB)(FAILOVER_MODE=(TYPE=session) (METHOD=basic))))');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
    
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER 
             ) RETURN PLS_INTEGER;
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int :=   xe_auth_pkg.check_password(p_USERNAME=>'USER!',p_PASSWORD=>'user1',p_HOST=>'teb-ref',p_DB_NAME=>'edis',p_PORT=>'1521');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
    
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER;
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int :=   xe_auth_pkg.check_password(p_USERNAME=>'USER!',p_PASSWORD=>'user1',p_CONN=>'(DESCRIPTION = (FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_TAF_LB)(FAILOVER_MODE=(TYPE=session) (METHOD=basic))))');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
     
    END XE_AUTH_MIX_PKG;
    /
    
    As you see in comment part there are out of the box examples of using this PLSQL methods so I will not explain them separately.
    And here is body part of that package:
    CREATE OR REPLACE PACKAGE BODY "XE_AUTH_MIX_PKG" AS
    FUNCTION check_password_core ( p_ID       IN XE_USERS.ID%TYPE
                                  ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                                  ,p_CONN     IN VARCHAR2
                                  ,p_HOST     IN VARCHAR2
                                  ,p_DB_NAME  IN VARCHAR2
                                  ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER
    IS
      l_username      XE_USERS.USERNAME%TYPE;
      l_salt          XE_USERS.SALT%TYPE;
      l_hash_password XE_USERS.PASSWORD%TYPE;
      l_password      XE_USERS.PASSWORD%TYPE;
      l_temp          PLS_INTEGER;
    BEGIN
      -- get basic data (must be present in XE_UERS table)
      SELECT upper(USERNAME)  , PASSWORD  ,SALT
        INTO l_username, l_password,l_salt
        FROM XE_USERS
       WHERE ID=p_ID;
      dbms_output.put_line('l_username='||l_username);
      dbms_output.put_line('l_password='||l_password);
      dbms_output.put_line('l_salt='||l_salt);
      
      -- check db credentials for that username and sent p_password
      SELECT COUNT(*)
        INTO l_temp 
        FROM dba_users
       WHERE username=l_username;
      IF l_temp=0 THEN
        -- non db user -must have credentials in XE_USERS tabale!  
        l_hash_password := XE_CRYPTO_PKG.HASH_PASSWORD(l_username,p_password,l_salt);
        dbms_output.put_line('l_hash_password='||l_hash_password);
        IF l_hash_password=l_password THEN
          RETURN common_pkg.OK;
        ELSE
          RETURN common_pkg.not_ok;
        END IF;
      ELSE
        IF p_CONN is null THEN
          return JAVA_PKG.doCheckDbConnection (l_username,p_PASSWORD,p_HOST,p_DB_NAME,p_PORT);
        ELSE
          return JAVA_PKG.doCheckDbConnection (l_username,p_PASSWORD,p_CONN);
        END IF; 
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN common_pkg.NOTHING_TO_DO;
      WHEN common_pkg.busy_resource THEN
        RETURN common_pkg.BUSY_RES;
      WHEN OTHERS THEN
        l_temp := auto_log_error_xe_auth_f ('fnc "XE_AUTH_MIX_PKG.check_password_core", when others p_ID:"'||p_ID||'", l_username:"'||l_username||'", p_PASSWORD:"'||p_PASSWORD||'", p_DB_NAME:"'||p_DB_NAME||'", '||p_PASSWORD||'", p_PORT:"'||p_PORT||'", p_CONN:"'||p_CONN||'"');
        RETURN l_temp;
    END check_password_core;
    
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      return check_password_core (p_ID=>p_ID, P_PASSWORD=>P_PASSWORD,p_CONN=>null, p_HOST=>p_HOST, p_DB_NAME=>p_DB_NAME, p_PORT=>p_PORT);
    END check_password;
    
    
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      return check_password_core (p_ID=>p_ID, P_PASSWORD=>P_PASSWORD,p_CONN=>p_CONN,p_HOST=>null, p_DB_NAME=>null, p_PORT=>null);
    END check_password;
    
    -- **********************************************************************************************************
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      RETURN check_password_core (p_ID=>XE_AUTH_PKG.get_id_from_username(upper(p_USERNAME)), p_PASSWORD=>p_PASSWORD, p_CONN=>null, p_HOST=>p_HOST, p_DB_NAME=>p_DB_NAME, p_PORT=>p_PORT);
    END check_password;
    
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      RETURN check_password_core (p_ID=>XE_AUTH_PKG.get_id_from_username(upper(p_USERNAME)), P_PASSWORD=>P_PASSWORD,P_CONN=>P_CONN, p_HOST=>null, p_DB_NAME=>null, p_PORT=>null);
    END check_password;
    END XE_AUTH_MIX_PKG;
    /
    
    Similarly like in Java part, there are overload function which are used in the same situations like those mentioned there ... based on JDBC connection type.

    The End

    Hope this will help someone.

    Cheers!
  • No comments :

    Post a Comment

    Zagreb u srcu!

    Copyright © 2009-2014 Damir Vadas

    All rights reserved.


    Sign by Danasoft - Get Your Sign