Proxy Connections and PL/SQL

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Tue, 17 Apr 2007 14:15:35 -0700
Message-ID: <7F24308CD176594B8F14969D10C02C6C011B14B3@exch-mail2.win.slac.stanford.edu>

Proxy connections in effect allow a user to become another. One needs to grant a privilege

ALTER USER <user to be proxied> GRANT CONNECT THROUGH <PROXY_USER>. Then run the statement:  

 CONNECT <PROXY_USER>[<user to be proxied>]/<poxy_user's password>

This works great in SQL*Plus and there are java examples

private void proxyConnection() throws Exception {

        Properties properties = new Properties();
        properties.put("PROXY_USER_NAME", "pu_user_1");

        OracleConnection conn = (OracleConnection) DriverManager.getConnection(
                "jdbc:oracle:thin:pu_pool_user/pu_pool_user_at_localhost:1521:ora1012",
                properties);
        conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, properties);

        printUserInfo(conn);
        conn.close();

    }

What I want to do is to have log someone on as proxy_user and then proxy them. This is what the program above does. I want to be able to do it in pl/sql. I thought also of having pl/sql call a java stored procedure. However in the example the proxy connection is dependent on a normal jdbc connection.

In detail the user would be connected via a Database Access descriptor, then a screen would pop up asking them to authenticate against Active Directory, and if successful the connection would be proxied. Has anyone been successful doing this.

Ian MacGregor
Stanford Linear Accelerator Center

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 17 2007 - 16:15:35 CDT