//*************************************************************************** // (c) Copyright IBM Corp. 2007 All rights reserved. // // The following sample of source code ("Sample") is owned by International // Business Machines Corporation or one of its subsidiaries ("IBM") and is // copyrighted and licensed, not sold. You may use, copy, modify, and // distribute the Sample in any form without payment to IBM, for the purpose of // assisting you in the development of your applications. // // The Sample code is provided to you on an "AS IS" basis, without warranty of // any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR // IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF // MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do // not allow for the exclusion or limitation of implied warranties, so the above // limitations or exclusions may not apply to you. IBM shall not be liable for // any damages you suffer as a result of using, copying, modifying or // distributing the Sample, even if IBM has been advised of the possibility of // such damages. //*************************************************************************** // // SAMPLE FILE NAME: TrustedContext.java // // PURPOSE: To demonstrate // 1. Creating a trusted Context object. // 2. How to establish explicit trusted connection. // 3. Authorizing switching of the user on a trusted connection. // 4. Acquiring trusted context-specific privileges through Role inheritance. // 5. Altering a trusted context object. // 6. Dropping a trusted context object. // // PREREQUISITES: // 1. a) Database "testdb" must exist. // Create the database using command given below: // db2 "CREATE DATABASE testdb" // b) Update the configuration parameter SVCENAME. // db2 "update dbm cfg using svcename <TCP/IP port num>" // c) Set communication protocol to TCP/IP. // db2set DB2COMM=TCPIP // d) Stop and start the DB2 instance. // db2 terminate; // db2stop; // db2start; // 2. Following users with corresponding passwords must exist // a) A user with SECADM authority on database. // padma with "padma123" // Grant SECADM authority to user "padma" commands given below: // db2 "CONNECT TO testdb" // db2 "GRANT SECADM ON DATABASE TO USER padma" // db2 "CONNECT RESET" // b) A valid system authorization ID and passord. // bob with "bob123" // c) Normal Users without SYSADM and DBADM authorities. // joe with "joe123" // pat with "pat123" // mat with "mat123" // // EXECUTION: i) javac TrustedContext.java (compile the sample) // ii) java TrustedContext <serverName> <portNumber> <userid> <password> // eg: java TrustedContext db2aix.ibm.com 30308 padma padma123 // userid and password that are passed must have the SECADM authority. // // INPUTS: NONE // // OUTPUTS: Successful establishment of trusted connection and switch user. // // // // SQL Statements USED: // CREATE TRUSTED CONTEXT // ALTER TRUSTED CONTEXT // GRANT // CREATE TABLE // CREATE ROLE // INSERT // UPDATE // DROP ROLE // DROP TRUSTED CONTEXT // DROP TABLE // // JAVA CLASSES USED: // Statement // ResultSet // Connection // DB2ConnectionPoolDataSource // DB2PooledConnection // Properties // // ************************************************************************* // For more information on the sample programs, see the README file. // // For information on developing Java applications see the Developing Java Applications book. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, compiling, and running DB2 // applications, visit the DB2 Information Center at // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp // *************************************************************************/ // // SAMPLE DESCRIPTION // // /************************************************************************* // 1. Connect to database and create the trusted context object. // 2. Establish the explicit trusted connection and grant privileges to the roles. // 3. Switch the current user on the connection to a different user // with and without authentication. // 4. Switch the current user on the connection to an invalid user. // 5. Alter the trusted context object after disabling it. // 6. Drop the objects created for trusted context and roles. // *************************************************************************/ // import the required classes import java.util.*; import java.sql.*; import java.io.*; import java.net.InetAddress; import com.ibm.db2.jcc.*; import com.ibm.db2.jcc.DB2Connection; import com.ibm.db2.jcc.DB2ConnectionPoolDataSource; class TrustedContext { public static void main (String[] args) { try { // Users and Passwords String authid = new String("bob"); String authid_pwd = new String("bob123"); String user1 = new String("joe"); String user1_pwd = new String("joe123"); String user2 = new String("pat"); String user2_pwd = new String("pat123"); String user3 = new String("mat"); String user3_pwd = new String("mat123"); // get the command line arguments String serverName = args[0]; String portNumber = args[1]; String userid = args[2]; String password = args[3]; // Local variables and classes String ctname = new String("CTX1"); String databaseName = new String("testdb"); Connection con = null; Statement stmt; ResultSet rs=null; String url,newUser,newPassword; String sqlid = " "; Object[] objects = new Object[6]; byte[] cookie = new byte[1]; com.ibm.db2.jcc.DB2ConnectionPoolDataSource ds1 = new com.ibm.db2.jcc.DB2ConnectionPoolDataSource(); java.util.Properties properties = new java.util.Properties(); com.ibm.db2.jcc.DB2PooledConnection pooledCon = (com.ibm.db2.jcc.DB2PooledConnection)objects[0]; // load the driver Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); url = "jdbc:db2://"+serverName+":"+portNumber+"/testdb"; System.out.println("\n This sample will demonstrate " + "\n\t 1. Creating a trusted Context object. " + "\n\t 2. How to establish explicit trusted connection. " + "\n\t 3. Authorizing the switching of the user on a trusted connection. " + "\n\t 4. Acquiring trusted context-specific privileges through Role inheritance. " + "\n\t 5. Altering a trusted context object. " + "\n\t 6. Dropping a trusted context object. "); System.out.println("\n"); // connect to the database try { con = DriverManager.getConnection(url,userid,password ); System.out.println(userid + " connected to the database"); con.setAutoCommit(true); } catch(Exception ex) { System.out.println(" Connect to database failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Create roles try { stmt = con.createStatement(); stmt.execute("CREATE ROLE def_role"); System.out.println("\n Role def_role created "); stmt.execute("CREATE ROLE tc_role"); System.out.println("\n Role tc_role created "); stmt.execute("grant role def_role to user "+user1); stmt.execute("grant role tc_role to user "+user2); stmt.close(); } catch(Exception ex) { System.out.println(" Role creation failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Create the trusted context object with // system authorization id as authid // for IP domain name containing serverName // with no default role // users as user1 with authentication and // user2 having tc_role privileges and without authentication try { stmt = con.createStatement(); String sql = "CREATE TRUSTED CONTEXT " + ctname + " BASED UPON CONNECTION USING SYSTEM AUTHID " + authid + " ATTRIBUTES ( ADDRESS '" + serverName + "') " + " DEFAULT ROLE def_role" + " ENABLE " + " WITH USE FOR " + user1 + " WITH AUTHENTICATION, " + user2 + " ROLE tc_role WITHOUT AUTHENTICATION"; stmt.execute(sql); System.out.println(); System.out.println(sql); System.out.println(" Trusted context object "+ ctname +" Created" ); stmt.close(); } catch(Exception ex) { System.out.println(" Trusted context object " + ctname + " creation failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Add a comment to the Trusted context object try { stmt = con.createStatement(); stmt.execute("COMMENT ON TRUSTED CONTEXT ctx1 IS 'Trusted Context object used to establish explicit trusted connection!'"); con.commit(); stmt.close(); } catch(Exception ex) { System.out.println(" Adding a comment to the Trusted context object failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } try { String remarks = ""; System.out.println(); System.out.println("SELECT remarks FROM SYSIBM.SYSCOMMENTS"); System.out.println( " COMMENT ON TRUSTED CONTEXT\n" + " -----------------------------\n"); stmt = con.createStatement(); // perform a SELECT rs = stmt.executeQuery("SELECT remarks FROM SYSIBM.SYSCOMMENTS"); // retrieve and display the result from the SELECT statement while (rs.next()) { remarks = rs.getString(1); System.out.println( " " + remarks); } rs.close(); stmt.close(); } catch(Exception ex) { System.out.println(" Trusted context object comment not created"); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // close the connection con.close(); // establish explicit trusted connection and switch user id to a different user /************************************************** * Create datasource for connection to database. **************************************************/ try { ds1.setServerName(serverName); ds1.setPortNumber(Integer.valueOf(portNumber).intValue()); ds1.setDatabaseName(databaseName); ds1.setDriverType (4); } catch(Exception ex) { System.out.println(" Datasource creation failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /************************************ * Establish the explicit trusted connection *************************************/ try { System.out.println("\n Establish explicit trusted connection using "+ authid +"..."); objects = ds1.getDB2TrustedPooledConnection(authid, authid_pwd, properties); pooledCon = (com.ibm.db2.jcc.DB2PooledConnection)objects[0]; System.out.println(" Established explicit trusted connection for "+ authid ); cookie = (byte[])objects[1]; newUser = null; newPassword = null; rs = null; sqlid = null; stmt = null; } catch(Exception ex) { System.out.println(" Failed to establish trusted connection for " + authid ); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /************************************************************** * Connect as authid to check explicit trusted connection worked or not * authid is the system authorization ID defined for the trusted context ***************************************************************/ newUser = authid; newPassword = authid_pwd; try { System.out.println("\n Get connection as "+ newUser +" ..."); con = pooledCon.getDB2Connection(cookie, newUser, newPassword, null, null, null, properties); stmt = con.createStatement(); System.out.println("\t Check who is currently connected to database, should be "+ newUser +" ..."); rs = stmt.executeQuery("values SYSTEM_USER"); rs.next(); sqlid = rs.getString(1); System.out.println("\tCurrent user connected to database = " + sqlid); if((sqlid.trim()).equalsIgnoreCase(newUser.trim())) { System.out.println(" Connected as "+newUser); System.out.println(" Trusted connection worked "); } else { System.out.println(" Trusted connection failed "); } } catch(Exception ex) { System.out.println(" Trusted connection for "+ newUser +" failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Create a table and populate the table Statement stmt1 = con.createStatement(); try { stmt1.executeUpdate("CREATE TABLE test.tc_emp_table (emp_no INT, emp_name VARCHAR (20), emp_sal DECIMAL)"); stmt1 = con.createStatement(); stmt1.executeUpdate("INSERT INTO test.tc_emp_table VALUES(100, 'Padma Kota', 30000)"); stmt1.executeUpdate("INSERT INTO test.tc_emp_table VALUES(200, 'Kathy Smith',20000)"); System.out.println("\n Created and Inserted data into table test.tc_emp_table using " + sqlid ); } catch(Exception ex) { System.out.println("\n Failed to create table test.tc_emp_table and populate it"); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Grant privileges to the roles. try { // Grant SELECT privilege on this table to the role def_role stmt1.execute("GRANT SELECT ON TABLE test.tc_emp_table TO ROLE def_role"); System.out.println(" Granted SELECT privilege on table test.tc_emp_table to def_role "); // Grant UPDATE privilege on this table to the role tc_role stmt1.execute("GRANT UPDATE ON TABLE test.tc_emp_table TO ROLE tc_role"); System.out.println(" Granted UPDATE privilege on table test.tc_emp_table to tc_role "); stmt1.close(); } catch(Exception ex) { System.out.println(" Failed to grant privileges on the table test.tc_emp_table"); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /************************************************************* * Switch to new user user1 under a trusted connection by * providing authentication information. * user1 is explicitly defined as a user of the trusted context **************************************************************/ newUser = user1; newPassword = user1_pwd; try { System.out.println("\n Attempt switch user to "+newUser+" ..."); con = pooledCon.getDB2Connection(cookie,newUser, newPassword, null, null, null, properties); stmt = con.createStatement(); System.out.println("\t Check who is currently connected to database, should be "+ newUser +" ..."); rs = stmt.executeQuery("values SYSTEM_USER"); rs.next(); sqlid = rs.getString(1); System.out.println("\tCurrent user connected to database = " + sqlid); if((sqlid.trim()).equalsIgnoreCase(newUser.trim())) { System.out.println(" Connected as "+newUser); System.out.println(" Success on switch user for "+newUser+ " by providing authentication information"); } else { System.out.println(" Switch user failed "); } rs.close(); } catch(Exception ex) { System.out.println(" Switch user for " +newUser+" failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // check whether the user inherited trusted context-specific default privileges try { stmt = con.createStatement(); rs = stmt.executeQuery("SELECT emp_name FROM test.tc_emp_table where emp_no = 100 "); System.out.println("\n Select emp_name from table tc_emp_table... "); while (rs.next()) { String name = rs.getString(1); System.out.println("\t" + name); } rs.close(); System.out.println(" User "+ sqlid +" has inherited trusted context-specific default privileges"); } catch(Exception ex) { System.out.println(" Failed to inherit trusted context-specific privileges "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /********************************************************************** * Switch to new user user2 under a trusted connection without * providing authentication information. * Update the table as user2 has UPDATE privilege on the table. ***********************************************************************/ newUser = user2; // Connect to database not from trusted conection and try to update the table // user2 should not be able to update the table try { // connect to the database Connection con1 = DriverManager.getConnection(url,user2,user2_pwd ); System.out.println(user2 + " Connected to the database not from trusted connection"); stmt = con1.createStatement(); System.out.println("\n Update table tc_emp_table.... "); stmt.executeUpdate("UPDATE test.tc_emp_table set emp_sal = 38000 where emp_no = 200"); System.out.println("\n Updated table tc_emp_table"); con1.close(); } catch (SQLException sqle) { System.out.println(" Update table failed "); } try { System.out.println("\n Attempt switch user to "+newUser+" ..."); con = pooledCon.getDB2Connection(cookie,newUser, null, null, null, null, properties); stmt = con.createStatement(); System.out.println("\t Check who is currently connected to database, should be "+ newUser +" ..."); rs = stmt.executeQuery("values SYSTEM_USER"); rs.next(); sqlid = rs.getString(1); System.out.println("\tCurrent user connected to database = " + sqlid); if((sqlid.trim()).equalsIgnoreCase(newUser.trim())) { System.out.println(" Connected as "+newUser); System.out.println(" Success on switch user for "+newUser+ " without providing authentication information"); } else { System.out.println(" Switch user failed "); } } catch(Exception ex) { System.out.println(" Switch user failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // check whether the user inherited trusted context-specific privileges try { stmt = con.createStatement(); stmt.executeUpdate("UPDATE test.tc_emp_table set emp_sal = 38000 where emp_no = 200"); System.out.println("\n Updated table tc_emp_table"); System.out.println(" User "+ sqlid +" has inherited trusted context-specific privileges"); } catch(Exception ex) { System.out.println(" Failed to inherit trusted context-specific privileges "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /************************************************************* * Switch to user authid under a trusted connection to drop * the objects created. **************************************************************/ newUser = authid; newPassword = authid_pwd; try { System.out.println("\n Attempt switch user to "+newUser+" ..."); con = pooledCon.getDB2Connection(cookie,newUser, newPassword, null, null, null, properties); stmt = con.createStatement(); System.out.println("\t Check who is currently connected to database, should be "+ newUser +" ..."); rs = stmt.executeQuery("values SYSTEM_USER"); rs.next(); sqlid = rs.getString(1); System.out.println("\tCurrent user connected to database = " + sqlid); if((sqlid.trim()).equalsIgnoreCase(newUser.trim())) { System.out.println(" Connected as "+newUser); System.out.println(" Success on switch user for "+newUser+ " by providing authentication information"); } else { System.out.println(" Switch user failed "); } rs.close(); } catch(Exception ex) { System.out.println(" Switch user failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Drop the table created try { stmt = con.createStatement(); System.out.println(" DROP the tables..."); System.out.println(" DROP TABLE test.tc_emp_table"); stmt.execute(" DROP TABLE test.tc_emp_table"); stmt.close(); } catch(Exception ex) { System.out.println(" Alter trusted context failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /************************************************************* * Switch to a new user who is not defined as a user of the * trusted context and this switch request made is not allowed **************************************************************/ newUser = user3; newPassword = user3_pwd; try { System.out.println("\n Attempt switch user to user "+newUser+" ..."); con = pooledCon.getDB2Connection(cookie,newUser, newPassword, null, null, null, properties); stmt = con.createStatement(); System.out.println("\t Check who is currently connected to database, should be "+ newUser +" ..."); rs = stmt.executeQuery("values SYSTEM_USER"); rs.next(); sqlid = rs.getString(1); System.out.println("\tCurrent user connected to database = " + sqlid); if((sqlid.trim()).equalsIgnoreCase(newUser.trim())) { System.out.println(" Connected as "+newUser); System.out.println(" Success on switch user for "+newUser); } else { System.out.println(" Switch user failed "); } } catch(Exception ex) { System.out.println(" Switch user failed "); System.out.println(" This is an Expected error!! "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } /************************************************ * Close open connections ***********************************************/ finally{ System.out.println("\n Close open connections"); con.close(); } /******************************************************* * ALTER TRUSTED CONTEXT ******************************************************/ // connect to the database try { con = DriverManager.getConnection(url,userid,password ); System.out.println(userid + " Connected to the database"); con.setAutoCommit(true); } catch (SQLException sqle) { System.out.println(" Connect to database failed "); System.out.println("Error Msg: "+ sqle.getMessage()); System.out.println("SQLState: "+sqle.getSQLState()); System.out.println("SQLError: "+sqle.getErrorCode()); } try { // Alter the trusted context to add new attributes stmt = con.createStatement(); // Disable the trusted context object System.out.println("\n Disable the trusted context object"); String st = "ALTER TRUSTED CONTEXT " + ctname + " ALTER DISABLE"; stmt.execute(st); System.out.println(st); System.out.println("\n Alter the trusted context "); st = "ALTER TRUSTED CONTEXT " + ctname + " ADD USE FOR PUBLIC WITH AUTHENTICATION"; stmt.execute(st); System.out.println(st); stmt.close(); } catch(Exception ex) { System.out.println(" Alter trusted context failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Drop the trusted context object and role try { stmt = con.createStatement(); // Drop the trusted context System.out.println("\n Drop the trusted context... "); System.out.println(" DROP TRUSTED CONTEXT " + ctname); stmt.execute(" DROP TRUSTED CONTEXT " + ctname); // Drop the role System.out.println("\n Drop the roles..."); System.out.println(" DROP ROLE tc_role "); stmt.execute(" DROP ROLE tc_role "); System.out.println(" DROP ROLE def_role "); stmt.execute(" DROP ROLE def_role "); stmt.close(); } catch(Exception ex) { System.out.println(" Alter trusted context failed "); if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } // Close the connection con.close(); } catch(Exception ex) { if (ex instanceof java.sql.SQLException) { System.out.println("error code: " + ((java.sql.SQLException)(ex)).getErrorCode()); } ex.printStackTrace(); } } // main } // TrustedContext