import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

import oracle.jdbc.OracleConnection;

import oracle.jdbc.pool.OracleDataSource;

class ProxySessionSample {

final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))";

/*

*/

public static void main(String args[]) throws SQLException {

OracleDataSource ods = new OracleDataSource();

// retrieve a database connection of user "proxy"

OracleConnection proxyConn = getConnection("proxy", "proxy", DB_URL, ods);

// isProxySession is false before opening a proxy session

System.out.println("Before a proxy session is open, isProxySession: "

+ proxyConn.isProxySession());

// check if the user is "proxy"

checkUser(proxyConn);

// open a proxy session for the user "jeff".

// This session reuses existing proxy session to connect as user, "jeff".

// There is no need to authenticate the user "jeff".

demoProxySession(proxyConn, "jeff");

// open a proxy session for the user "smith".

// This session reuses existing proxy session to connect as user "smith"

// There is no need to authenticate the user "smith".

demoProxySession(proxyConn, "smith");

// Close the proxy connection

proxyConn.close();

}

/*

private static void demoProxySession(OracleConnection conn, String proxyUser)

throws SQLException {

Properties prop = new Properties();

prop.put(OracleConnection.PROXY_USER_NAME, proxyUser);

// corresponds to the alter sql statement (select, insert roles)

String[] roles = { "select_role", "insert_role" };

prop.put(OracleConnection.PROXY_ROLES, roles);

conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);

System.out.println("======= demoProxySession BEGIN =======");

System.out.println("After the proxy session is open, isProxySession: "

+ conn.isProxySession());

// proxy session can act as users "jeff" & "smith" to access the

// user "proxy" tables

try (Statement stmt = conn.createStatement()) {

// Check who is the database user

checkUser(conn);

// play insert_role into proxy.proxy_account, go through

stmt.execute("insert into proxy.proxy_account values (1)");

System.out.println("insert into proxy.proxy_account, allowed");

// play select_role from proxy.proxy_account, go through

try (ResultSet rset = stmt.executeQuery("select * from "

+ " proxy.proxy_account")) {

while (rset.next()) {

// display the execution results of a select query.

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

}

System.out.println("select * from proxy.proxy_account, allowed");

// play delete_role from proxy.proxy_account, SQLException

stmt.execute("delete from proxy.proxy_account where purchase=1");

} catch(Exception e) {

System.out.println("delete from proxy.proxy_account, not allowed");

}

System.out.println("======= demoProxySession END =======");

// Close the proxy session of user "jeff"

conn.close(OracleConnection.PROXY_SESSION);

}

}

/*

private static OracleConnection getConnection(String user, String password,

String url, OracleDataSource ods) throws SQLException {

ods.setUser(user);

ods.setPassword(password);

ods.setURL(url);

return ((OracleConnection) ods.getConnection());

}

/*

private static void checkUser(Connection conn) throws SQLException {

try (Statement stmt = conn.createStatement()) {

try (ResultSet rset = stmt.executeQuery("select user from dual")) {

while (rset.next()) {

System.out.println("User is: " + rset.getString(1));

}

}

}

}

}

Rem ProxySessionSample.sql

Rem

Rem Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.

Rem

Rem NAME

Rem ProxySessionSample.sql - Complimentory SQL file for ProxySessionSample.java

Rem

Rem DESCRIPTION

Rem The sample shows connecting to the Oracle Database using Proxy authentication

Rem or N-tier authentication.

Rem

Rem MODIFIED (MM/DD/YY)

Rem nbsundar 04/10/15 - Created

Rem

Rem This sample requires connecting as a system user that has the ability to

Rem create user and grant necessary privileges

connect system/manager

Rem Clean up before creating necesary schema

drop role select_role;

drop role insert_role;

drop role delete_role;

drop user jeff cascade;

drop user smith cascade;

drop user proxy cascade;

Rem Create new database users "jeff", "smith" and "proxy"

create user proxy identified by proxy;

create user jeff identified by jeffpasswd;

create user smith identified by smithpasswd;

Rem Grant necessary privileges to DB users "proxy", "jeff" and "smith"

grant create session, connect, resource, unlimited tablespace to proxy;

grant create session, connect, resource, unlimited tablespace to jeff;

grant create session, connect, resource, unlimited tablespace to smith;

Rem Create roles and grant necessary roles to users "jeff" and "smith"

create role select_role;

create role insert_role;

create role delete_role;

Rem Connect as a proxy user

connect proxy/proxy

Rem Create the table which will be shared with the users "jeff" and "smith"

create table proxy_account (purchase number);

insert into proxy_account values(11);

insert into proxy_account values(13);

Rem Grant the required privileges

grant select on proxy_account to select_role;

grant insert on proxy_account to insert_role;

grant delete on proxy_account to delete_role;

Rem Connect as system user to grant necessary roles to the DB users "jeff" and "smith"

connect system/manager

grant select_role, insert_role, delete_role to jeff;

grant select_role, insert_role, delete_role to smith;

Rem grant the users "jeff" and "smith" to connect through proxy with specified roles

alter user jeff grant connect through proxy with role select_role, insert_role;

alter user smith grant connect through proxy with role select_role, insert_role;

commit;

exit