//********************************************************************* // // Copyright (c) 2008 by Teradata Corporation. // All Rights Reserved // //********************************************************************* // // File: T21901JD.java // Purpose: Demonstrate the use of SET QUERY_BAND statement with PROXYUSER // // The program will: // - Connect as user guest/please. // - Create a statement. // - Remove any existing proxy user. // - Create a proxy user. // - Create a table owned by the proxy user. // - Revoke select, insert and update privileges on proxy user // from guest. // - Grant "connect through" privilege to guest for proxy user. // - Use QUERY_BAND/PROXYUSER to impersonate proxy user. // - Use the built-in function CURRENT_USER to show that proxy user // is active. // - Show that guest can insert data into proxy user's table. // - Show that guest can select data from proxy user's table. // - Remove ability for guest to impersonate the proxy user // via QUERY_BAND=NONE. // - Use the built-in function CURRENT_USER to show that proxy user // is no longer active. // - Show that guest can no longer insert data into proxy user's table. // - Show that guest can no longer select data from proxy user's table. // - Revoke "connect through" privilege from guest for proxy user. // - Remove proxy user's tables. // - Remove proxy user. // - Close the statement. // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.DriverManager // java.sql.ResultSet, java.sql.SQLException, java.sql.Statement, // // Version: Created for Teradata 13.0 // //********************************************************************* import java.sql.*; public class T21901JD { // Name of the user able to create, drop, manipulate tables and users and // who has ctcontrol rights public static String sUser = "guest"; public static String sPassword = "please"; // Name of the new user that will be created public static String sUserProxy = "guestTmp"; public static String sPasswordProxy = "pleaseTmp"; //name of new user's test table public static String sTmpTable = sUserProxy + ".table1"; public static void main(String [] args) throws ClassNotFoundException { // Creation of URL to be passed to the JDBC driver String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8"; try { System.out.println("\n Sample T21901JD: \n"); System.out.println(" Looking for the Teradata JDBC driver... "); // Loading the Teradata JDBC driver Class.forName("com.teradata.jdbc.TeraDriver"); System.out.println(" JDBC driver loaded. \n"); // Attempting to connect to Teradata System.out.println( " Attempting to connect to Teradata via the JDBC driver...\n"); // Creating a connection object Connection con = DriverManager.getConnection(url, sUser, sPassword); System.out.println(" Connection to Teradata established. \n"); try { //Creating a statement object from an active connection Statement stmt = con.createStatement(); System.out.println(" Statement object created. \n"); //cleanup a profile or user that may have been //leftover from a previous run System.out.println(" Cleaning up\n"); try { stmt.executeUpdate("revoke connect through " + sUser + " to permanent " + sUserProxy); } catch (SQLException ex1) { // ignored in cleanup System.out.println(" Ignoring exception: " + ex1); } try { stmt.execute("delete user " + sUserProxy); } catch (SQLException ex1) { // ignored in cleanup System.out.println(" Ignoring exception: " + ex1); } try { stmt.executeUpdate("drop user " + sUserProxy); } catch (SQLException ex1) { // ignored in cleanup System.out.println(" Ignoring exception: " + ex1); } try { //create proxy user stmt.executeUpdate("create user " + sUserProxy + " as perm=100000, password=" + sPasswordProxy); stmt.executeUpdate("grant all on " + sUserProxy + " to " + sUserProxy); stmt.executeUpdate("grant drop user on " + sUserProxy + " to " + sUser); System.out.println(" Proxy User created. \n"); try { //create tremporary user table stmt.execute( "create table " + sTmpTable + "(intval INTEGER)"); System.out.println(" Proxy User table created. \n"); System.out.println(" Revoke permissions on Proxy User\n"); stmt.execute("Revoke select, insert,update on " + sUserProxy + " from " + sUser); System.out.println( " Grant ability to connect as a Proxy User\n"); try { stmt.execute("grant connect through " + sUser + " to permanent " + sUserProxy + " without role"); } catch (SQLException ex) { if (ex.getErrorCode() == 5611) { System.out.println(" Test Failed: A Teradata User" + " with appropriate rights must run the command" + " \"Grant ctcontrol to " + sUser + "\" in order to" + " run this test successfully"); } throw ex; } try { System.out.println( " Use QUERY_BAND to switch to PROXY USER\n"); stmt.execute("SET QUERY_BAND ='PROXYUSER=" + sUserProxy + ";' FOR SESSION"); System.out.println( " Query Band has been set for proxy user"); //show CURRENT_USER ResultSet rs = stmt.executeQuery("sel CURRENT_USER"); System.out.print("\n CURRENT_USER is: "); while (rs.next()) { System.out.println(rs.getString(1)); } stmt.execute( "\n insert into " + sTmpTable + " values(1)"); System.out.println( " \n Data successfully inserted into table"); rs = stmt.executeQuery("select * from " + sTmpTable); System.out.print(" \n Results from select are: "); while (rs.next() != false) { System.out.print(rs.getString(1)); } System.out.println("\n"); System.out.println(" Switch back to original user\n"); stmt.execute("Set QUERY_BAND = NONE FOR SESSION\n"); //show value of CURRENT_USER rs = stmt.executeQuery("sel CURRENT_USER"); System.out.print(" CURRENT_USER is: "); while (rs.next()) { System.out.print(rs.getString(1)); } System.out.println("\n"); try { System.out.println( " Show that Proxy user data is now inaccessible"); //show that inserts fail System.out.println("\n Attempting to insert data" + " - this should fail"); stmt.execute( "insert into " + sTmpTable + " values(1)"); System.out.println(" Test failed - previous call" + " should have thrown exception\n"); throw new IllegalStateException ( "Insert attempt was expected to fail") ; } catch (SQLException ex) { System.out.println( " Insert failed as expected with error: " + ex); } try { //show that select also fails System.out.println("\n Attempting to select data" + " - this should fail"); rs = stmt.executeQuery("select * from " + sTmpTable); System.out.println(" Test failed - previous call" + " should have thrown exception\n"); throw new IllegalStateException ( "Select attempt was expected to fail") ; } catch (SQLException ex) { System.out.println( " Select failed as expected with error: " + ex); } System.out.println(); rs.close(); } finally { stmt.execute("Set QUERY_BAND = NONE FOR SESSION"); System.out.println(" Executing: revoke connect"); stmt.executeUpdate("revoke connect through " + sUser + " to permanent " + sUserProxy); } } finally { //drop the proxy user stmt.execute("delete user " + sUserProxy); stmt.execute("drop user " + sUserProxy); } } finally { //Close the statement stmt.close(); System.out.println("\n Statement object closed."); } } finally { //Close the connection System.out.println("\n Closing connection to Teradata..."); con.close(); System.out.println("\n Connection to Teradata closed."); } System.out.println("\n Sample T21901JD finished. \n"); } catch (SQLException ex) { // A SQLException was generated. Catch it and display // the error information. // Note that there could be multiple error objects chained // together. System.out.println(); System.out.println("*** SQLException caught ***"); while (ex != null) { System.out.println(" Error code: " + ex.getErrorCode()); System.out.println(" SQL State: " + ex.getSQLState()); System.out.println(" Message: " + ex.getMessage()); ex.printStackTrace(); System.out.println(); ex = ex.getNextException(); } throw new IllegalStateException ("Sample failed.") ; } } // End main } // End class T21901JD