Hi Tom

I have a application where the middle tier user connects to the database (muser).
muser also provides a connection pool to the database.The muser and password is stored
in a plain text file.A COM component reads this file and makes a connection to the database.

I have a requirement wherein i should allow folks to connect to my application
through the middletier only.If they make a connection through ODBC sqlplus TOAD etc they
should be given only read acesses to the data.I have read through proxy authentication.
But the examples are for java and OCI is there a example or a pointer for COM+,or ASP ?

Do you have any other ideas through application roles or something to do this ? I am using Oracle9iR2.

Hi Tom

Yes thats exactly what we are intending to do.create another user ruser with read only privlegies.However we need to prevent muser from logging in from sql plus toad etc.Since the users can easily see the password from the com.ini file stored in plain text.Just wundering is there any other way of cutting of users who use muser to log in to sqlplus and manage to get into the database

regards
Hrishy

Tom,
Please refer to the original question and your response. My question is that if I grant 20 different users connect through APP_SERVER and when all 20 users are connected to the database, will I see 21 or 40 sessions in V$session? I am assuming dedicated server, 9ir2.

Thanks

Tom,

I have setup proxy authentication and using Cold Fusion appls server as middle tier appsserv. I have a proxy user sfid_proxy which work on behalf of a shared schema SFID_MAPPED_SCHEMA in db. My real users are based in Oracle Directory. When I connect with 2 OID users, here is what I see in v$session

SID USERNAME PROCESS SUBSTR(PROGRAM,1,30)
------ ------------------------------ ------------ -----------------------------
11 SFID_PROXY 2883 cfserver@webA (TNS V1-V3)
15 OWB_RR JDBC Thin Client
18 SFID_MAPPED_SCHEMA 2883 cfserver@webA (TNS V1-V3)
19 SEC_ADMIN 2828:2208 sqlplusw.exe
21 SFID_MAPPED_SCHEMA 2883 cfserver@webA (TNS V1-V3)
22 SEC_ADMIN 4080:820 Toad.exe


How would I be sure that their is only one process (Physical circuit) for all these three session? Is the process columsn gives me unique id for the process? Which column in v$process tells me that there is one entry for all these three session ( 1 proxy and other 2 real users)?

Thanks a ton.

Process column in v$session, thanks, I was not 100% sure. One more thing, I would like to ask, on v$session, there is a client_identifier column, how can I update this column to the real user name. I have this little procedure but when I tried to use it, it only update client_identifier column for teh proxy user and not inidividually for every OID user. Am I doing something wrong here?

PROCEDURE set_client_id( p_client_id IN VARCHAR2 )
IS
BEGIN

dbms_session.set_identifier( p_client_id );
END;

USERNAME PROCESS SUBSTR(PROGRAM,1,30) CLIENT_IDENTIFIER
---------------- ------------ ------------------------- --------------
SEC_ADMIN 4080:820 Toad.exe
SFID_PROXY 9627 cfserver@webA (TNS V1-V3) ssahmad
OWB_RR JDBC Thin Client
SFID_MAPPED_SCHEMA9627 cfserver@webA (TNS V1-V3)
SFID 4080:820 Toad.exe
SFID_MAPPED_SCHEMA9627 cfserver@webA (TNS V1-V3)
SEC_ADMIN 2828:2208 sqlplusw.exe
SEC_ADMIN 4080:820 Toad.exe


In this v$session output, it updated "ssahmad" value to client_identifier for the proxy user (which is sfid_proxy in my case).

Thanks for all your help.

Thanks , it was my mistake? I was not calling the procedure correctly??

 
ops$tkyte@ORA9IR2> select client_identifier from v$session where username=user;
 
CLIENT_IDENTIFIER
----------------------------------------------------------------
 
 
ops$tkyte@ORA9IR2> exec dbms_session.set_identifier('hello world');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select client_identifier from v$session where username=user;
  
 
CLIENT_IDENTIFIER
----------------------------------------------------------------
hello world


it is whatever you set it to, if you don't set it, it wont be there. 

I want to utilize proxy authentication to allow the users to connect only through a Forms 6i client application.

I made a DLL that uses OCI to perform the proxy connection. I call the DLL from the ON-LOGON trigger of the main form. The problem is:

1. Forms 6i use client 8.0.
2. The OCI calls in the DLL must use client 8.1 (proxy authentication was not available before). I build the dll using oci.lib for 8i.

I have both clients installed. But when I call the DLL from the forms, it fails, because it chooses to work with client 8.0.

I'm somehow stuck at the moment. How could I tell the OCI which client to use? Is it at all possible to combine the 8.0 and 8.1 clients in the was I try do it?

Is it possible to use proxy authenticated users with XA?
(tuxedo application server on midtier)

Hi Tom,

scoured otn forums and the docs (plus that great David Knox book which says you can d it but doesn't show how) but what i cannot find is an example of proxy authentication using the thin driver.

I managed to get it working for just passing in the username but not when the password is also required.

Can you show an example?

Thanks

I've tried various combinations. The docs are next to useless because its only a snippet and the useful info is missing.

Here's the code using the thick driver that works:

private static void testProxyThickConnection() {
long connectTime = 0;
long connectionStart = 0;
long connectionStop = 0;

System.out.println("Proxy Thick");

try {
OracleOCIConnectionPool ods = new OracleOCIConnectionPool();
ods.setURL("jdbc:oracle:oci:@" + tnsAlias);
ods.setUser("app_user");
ods.setPassword("app_user");

java.util.Properties prop = new java.util.Properties();
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "3");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "20");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1");
ods.setPoolConfig(prop);

java.util.Properties userNameProp = new java.util.Properties();
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME,
"scott");
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD,
"tiger");
connectionStart = System.currentTimeMillis();

Connection conn = ods.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME,
userNameProp);
connectionStop = System.currentTimeMillis();

conn.close();

ods.close();
} catch (Exception e) {
System.out.println(e.toString());
}

// print connection time
connectTime = (connectionStop - connectionStart);
System.out.println("Initial connection time for pool: " + connectTime +
" ms.");
}

and here's my attempt at trying to use the thin and the error i am getting:

private static void testProxyThinConnection() {
long connectTime = 0;
long connectionStart = 0;
long connectionStop = 0;

System.out.println("Proxy Thin");

try {
OracleDataSource ods = new OracleDataSource();
ods.setURL(
"jdbc:oracle:thin:app_user/app_user@172.22.50.69:1521:ORCL01");

ods.setConnectionCachingEnabled(true);

java.util.Properties prop = new java.util.Properties();
prop.setProperty("InitialLimit", "3");
prop.setProperty("MinLimit", "3");
prop.setProperty("MaxLimit", "20");
ods.setConnectionCacheProperties(prop);

connectionStart = System.currentTimeMillis();

OracleConnection conn = (OracleConnection) ods.getConnection();

java.util.Properties userNameProp = new java.util.Properties();
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME,
"scott");
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD,
"tiger");

conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME,
userNameProp);

connectionStop = System.currentTimeMillis();

conn.close();

ods.close();
} catch (Exception e) {
System.out.println(e.toString());
}

// print connection time
connectTime = (connectionStop - connectionStart);
System.out.println("Initial connection time for pool: " + connectTime +
" ms.");
}

Proxy Thick
Initial connection time for pool: 161 ms.
Proxy Thin
java.sql.SQLException: User credentials doesn't match the existing ones
Initial connection time for pool: -1139260840702 ms.

Can you help????

Thanks

RP

Hi Tom,

added those lines but still no luck. Any chance of an example that is complete? Here's my attempt at trying to add those extra lines:

try {
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@" + tnsAlias);
ods.setUser("app_user");
ods.setPassword("app_user");

ods.setConnectionCachingEnabled(true);
ods.setConnectionCacheName("MY_CACHE");

java.util.Properties prop = new java.util.Properties();
prop.setProperty("InitialLimit", "3");
prop.setProperty("MinLimit", "3");
prop.setProperty("MaxLimit", "20");
ods.setConnectionCacheProperties(prop);

connectionStart = System.currentTimeMillis();

OracleConnection conn = (OracleConnection) ods.getConnection();

java.util.Properties userNameProp = new java.util.Properties();
userNameProp.setProperty(OracleConnection.PROXY_USER_NAME,
"scott");
userNameProp.setProperty(OracleConnection.PROXY_USER_PASSWORD,
"tiger");

conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME,
userNameProp);

connectionStop = System.currentTimeMillis();

conn.close(OracleConnection.PROXY_SESSION);

ods.close();
} catch (Exception e) {
System.out.println(e.toString());
}

and the error:

Proxy Thin
java.sql.SQLException: ORA-28178: password not provided by proxy

Is it possible to free the connection just after the execution of the statement but before the processing of the ResultSet? See the code below. The idea is to minimize the time the connection is used. But apparently the close of the connection closes the statements and therefore the result set as well; leading to a closed statement SQLException.

Thanks

Jaromir

OracleDataSource ods = new OracleDataSource();
ods.setURL ( "jdbc:oracle:thin:@localhost:1521:jarastro" );
ods.setUser("scott"); // user name
ods.setPassword("tiger"); // password
// enable implicit caching
ods.setConnectionCachingEnabled( true );
// set cache properties (use a properties file in production code.)
Properties cacheProperties = new Properties();
cacheProperties.setProperty( "InitialLimit", "2" );
cacheProperties.setProperty( "MinLimit", "3" );
cacheProperties.setProperty( "MaxLimit", "15" );
ods.setConnectionCacheProperties(cacheProperties);
// create the connection cache
OracleConnectionCacheManager occm =
OracleConnectionCacheManager.getConnectionCacheManagerInstance();
occm.createCache( "myCache", ods, cacheProperties );
Connection conn1 = ods.getConnection();
Statement stmt = conn1.createStatement();
ResultSet rs = stmt.executeQuery("select 1 x from dual");
conn1.close(); // free connection
if (rs.next()) { // java.sql.SQLException: Closed Statement: next <<<<<<<
int x = rs.getInt(1);
System.out.println( "result " + x);
};

The question is if it is really necessary to hold the (pooled) connection until the last row of the result set is fetched.
The idea was to hold the connection during the execution of the statement than free it but retain the result set (cursor).

Jaromir

Hello Tom,

We are currently moving our systems to the web. We are using J2EE as a platform for development.

Anyhow, the developers are complaining the Proxy user authentication is slow when they are using it.

The following is what they have send to me:

<quote>
I implemented a proxy based OCI-JDBC-Connection Pool and the instantiation just went fine, but when
trying to connect through the proxy user using ( getProxyConnection() ) it takes a very long time
(7 - 13 seconds) every time I try to establish a connection!!
The way I establish a pool is the same as follows:

sql> create user student_fund identified by student_fund;
sql> grant connect, resource to student_fund;
sql> create user a1 identified by TEST;
sql> grant create session to a1 ;
sql> alter user a1 grant connect through student_fund;
sql> create user a2 identified by TEST;
sql> grant create session to a2 ;
sql> alter user a1 grant connect through student_fund;

The Java code as follows:
======================================================================================
static OracleOCIConnectionPool ods;

/**
* @param args the command line arguments
*/
public static void main(String[] args) throws Exception {
// TODO code application logic here

if (args.length < 8) {
System.out.println("Usage: java -jar OraclePooling {HOST} {SERVICE_NAME} {PROXY_USERNAME}
{PROXY_PASSWORD} {USERNAME_1} {PASSWORD_1} {USERNAME_2} {PASSWORD_2}");
System.exit(0);
}

ods = new OracleOCIConnectionPool();

String tnsAlias = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = " +
" (PROTOCOL = TCP)(HOST = " + args[0] + ")(PORT = 1521)) )" +
" (CONNECT_DATA = (SERVICE_NAME = " + args[1] + ") ) )";

System.out.println(tnsAlias);

for (int i = 0; i < args.length; i++) {
System.out.println("arg " + i + " : " + args);
}

ods.setURL("jdbc:oracle:oci:@" + tnsAlias);
ods.setUser(args[2]);
ods.setPassword(args[3]);

Properties prop = new Properties();

prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "100");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "10");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_TIMEOUT, "60000");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "10");
prop.setProperty(OracleOCIConnectionPool.CONNPOOL_NOWAIT, "true");

ods.setPoolConfig(prop);

doSomething(args[4], args[5]);

doSomething(args[6], args[7]);

}

public static void doSomething(String username, String password) throws Exception {
long ts = System.currentTimeMillis();

OracleConnection conn = getPooledConnection(username, password);

long tf = System.currentTimeMillis();

ResultSet rs = conn.createStatement().executeQuery("SELECT user FROM DUAL");

rs.next();

System.out.println(rs.getString(1));

//conn.close(OracleConnection.PROXY_SESSION);

System.out.println(conn);

System.out.println("Time taken " + (tf - ts) + " Milliseconds");
}

public static OracleConnection getPooledConnection(String username, String password) throws
Exception {
Properties userNameProp = new Properties();
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME,
username);
userNameProp.setProperty(OracleOCIConnectionPool.PROXY_PASSWORD,
password);
return ods.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME,
userNameProp);
}
<end of quote>

I am working with them to know where is the problem and why the connection is taking so much time.
By the way, if we just did a connection pooling without a proxy authentication then it is so fast...

Is there anything we have missed???

Thanks