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.
- 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.
- 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:- Create interface that will check Oracle credentials
- Create interface that will perform mixed authentication checking (against wished rules)
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).
- doCheckDbConnection (String p_user_name, String p_password, String p_host, String p_db_name, String p_port)
- doCheckDbConnection (String p_user_name, String p_password, String p_conn)
- doCheckSQL (String p_sql, String p_user_name, String p_password, String p_conn)
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.
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!
