-- Thanks for the question regarding "Proxy authentication ", version 9i release 2

  October 26, 2003 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: hrishy from PA

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. 

well, if everyone already uses a single super user "muser", all you need to do is setup another 
user "ruser" -- for read only user and broadcast that password?

muser is only available to the middle tier already right?  (you could store the password a little 
better, but only the middle tier can use muser in theory)

So, just create a read only account and let people use that -- grant it the proper least set of 
privs and you are good to go 

  October 28, 2003 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: hrishy from PA

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 

ummm, if you leave the keys in a fake rock outside your house AND the fake rock is obviously FAKE 
-- will people not rob you?

the problem is your architecture - that of storing plain text passwords in a file on the client.  

perhaps MS approaches to "security" are not "good"?

rethink your entire approach.  else, just let anyone do whatever they want.  for you have left that 
as a definite choice for anyone as it is. 

  October 19, 2004 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Arun Gupta from Harrisburg, PA USA

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 

you'll have a session for the "app_server account" and each proxy user.

as for whether you have 21 or 40 sessions -- you have to tell me.

You could have 21 (a single physical connection -- one v$process entry -- with 20 proxy 
authentications)

but you'll probably have 40 when all are being used -- 20 connections in a connection pool, each 
one having it's own physical connection (a v$process entry) and when the user "authenticates", 
another session would be created on that connection for them. 

How to know process entry   October 28, 2004 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Suhail from NY

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 tells you that. 

thanks   October 29, 2004 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Suhail from NY

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.  

client identifier is set to whatever you set it to -- you would have to set it. 

but why its not setting correctly   October 29, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Suhail 

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. 

proxy authentication in Forms 6i   February 10, 2005 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Todor Botev from Germany

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? 

please use otn.oracle.com -> discussion forums to talk about forms and how to do stuff in that 
particular tool.

It has been since march 1995 that I've touched it.  I'm way out of date on it.

(but it does sound to me like "you are plug out of luck" totally -- think about it for a moment, I 
don't see any way you could expect this to work -- you have a 8.0 client, period.  you cannot mix 
8.1 and 8.0 together like that in a single program) 

Proxy users & Oracle_XA   March 17, 2005 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Edgar from Latvia

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

Please help   February 2, 2006 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: RP from London, UK

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 

http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14355/proxya.htm#sthref708

it isn't any different from thick?

... The JDBC OCI and Thin driver switch sessions in the same manner. The drivers permanently switch 
to the new session, jeff. As a result, the proxy session, scott, is not available until the new 
session, jeff, is closed. .... 

Its not as straight forward as you think!   February 6, 2006 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: RP from London, UK

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 

I asked Matt Piermarini - a resident Java Jockey and he said, the code in bold should be added and 
then the example works:

        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);


            for (int x=0; x<3; x++) {
              ....
              OracleConnectionCacheManager cm = 
OracleConnectionCacheManager.getConnectionCacheManagerInstance();

              System.out.println("Active:"+cm.getNumberOfActiveConnections("MY_CACHE"));

              System.out.println("Avail:"+cm.getNumberOfAvailableConnections("MY_CACHE"));

              conn.close(OracleConnection.PROXY_SESSION);

            }
 

Not complete   February 12, 2006 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: RP from London, UK

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 

email me, I'll send you the entire piece of code Matt wrote for me.   

free cached connection before processing ResultSet   October 29, 2006 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jaromir D.B. Nemec from Austria

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);
    }; 

umm, give up the connection...

     give up the data....


they go hand in hand. 

Exactly the point…   October 30, 2006 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: Jaromir D.B. Nemec from Austria

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
 

and the answer is exactly:

as soon as you give up the connection, you give up the data.  It is done, gone, not yours anymore.

Think about it for a moment, how could it be any other way?  

Say you open a query:


select * from ten_billion_row_table;

it is not like we copy that into the client memory or anything, we fetch it as you ask for it 
(opening that query - instantaneous, getting to the last row - lots of work done during the fetch). 

  May 30, 2009 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Emad Kehail from Palestine

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