Review Board 1.7.22


Fix for SQOOP-1224

Review Request #15222 - Created Nov. 5, 2013 and updated

Venkat Ranganathan
SQOOP-1224
Reviewers
Sqoop
sqoop-trunk
When we use wallet based authentication (which enables secure external authentication in Oracle), the Sqoop jobs fail.   The reason for this is that in a wallet based authentication, the username, password and DB information are hidden and only a service is exposed.   So, all queries to which we pass the owner as a parameter will fail.  This patch adds the ability to substitute the logged on Oracle user if the username is not provided (as in wallet based implementations), but can also be used for other SSO mechanisms
Added one test to validate the query to get the current logged on user.   All tests pass.  No new checkstyle violations introduced
src/java/org/apache/sqoop/manager/OracleManager.java
Revision f6f3afa New Change
[20] 106 lines
[+20] [+] public class OracleManager
107
     + "ALL_CONSTRAINTS.CONSTRAINT_NAME AND "
107
     + "ALL_CONSTRAINTS.CONSTRAINT_NAME AND "
108
     + "ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P' AND "
108
     + "ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P' AND "
109
     + "ALL_CONS_COLUMNS.TABLE_NAME = ? AND "
109
     + "ALL_CONS_COLUMNS.TABLE_NAME = ? AND "
110
     + "ALL_CONS_COLUMNS.OWNER = ?";
110
     + "ALL_CONS_COLUMNS.OWNER = ?";
111

    
   
111

   

    
   
112
  /**

    
   
113
   * Query to get the current user for the DB session.   Used in case of

    
   
114
   * wallet logins.

    
   
115
   */

    
   
116
  public static final String QUERY_GET_SESSIONUSER =

    
   
117
     "SELECT USER FROM DUAL";

    
   
118

   
112
  // driver class to ensure is loaded when making db connection.
119
  // driver class to ensure is loaded when making db connection.
113
  private static final String DRIVER_CLASS = "oracle.jdbc.OracleDriver";
120
  private static final String DRIVER_CLASS = "oracle.jdbc.OracleDriver";
114

    
   
121

   
115
  // Configuration key to use to set the session timezone.
122
  // Configuration key to use to set the session timezone.
116
  public static final String ORACLE_TIMEZONE_KEY = "oracle.sessionTimeZone";
123
  public static final String ORACLE_TIMEZONE_KEY = "oracle.sessionTimeZone";
[+20] [20] 165 lines
[+20] [+] protected Connection makeConnection() throws SQLException {
282

    
   
289

   
283
    String username = options.getUsername();
290
    String username = options.getUsername();
284
    String password = options.getPassword();
291
    String password = options.getPassword();
285
    String connectStr = options.getConnectString();
292
    String connectStr = options.getConnectString();
286

    
   
293

   

    
   
294

   
287
    connection = CACHE.getConnection(connectStr, username);
295
    connection = CACHE.getConnection(connectStr, username);
288
    if (null == connection) {
296
    if (null == connection) {
289
      // Couldn't pull one from the cache. Get a new one.
297
      // Couldn't pull one from the cache. Get a new one.
290
      LOG.debug("Creating a new connection for "
298
      LOG.debug("Creating a new connection for "
291
              + connectStr + ", using username: " + username);
299
              + connectStr + ", using username: " + username);
[+20] [20] 32 lines
[+20] protected Connection makeConnection() throws SQLException {
324
    setSessionTimeZone(connection);
332
    setSessionTimeZone(connection);
325

    
   
333

   
326
    return connection;
334
    return connection;
327
  }
335
  }
328

    
   
336

   

    
   
337
  public String getSessionUser(Connection conn) {

    
   
338
    Statement stmt = null;

    
   
339
    ResultSet rset = null;

    
   
340
    String user = null;

    
   
341
    try {

    
   
342
      stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,

    
   
343
              ResultSet.CONCUR_READ_ONLY);

    
   
344
      rset = stmt.executeQuery(QUERY_GET_SESSIONUSER);

    
   
345

   

    
   
346
      if (rset.next()) {

    
   
347
        user = rset.getString(1);

    
   
348
      }

    
   
349
      conn.commit();

    
   
350
    } catch (SQLException e) {

    
   
351
      try {

    
   
352
        conn.rollback();

    
   
353
      } catch (SQLException ex) {

    
   
354
        LoggingUtils.logAll(LOG, "Failed to rollback transaction", ex);

    
   
355
      }

    
   
356
    } finally {

    
   
357
      if (rset != null) {

    
   
358
        try {

    
   
359
          rset.close();

    
   
360
        } catch (SQLException ex) {

    
   
361
          LoggingUtils.logAll(LOG, "Failed to close resultset", ex);

    
   
362
        }

    
   
363
      }

    
   
364
      if (stmt != null) {

    
   
365
        try {

    
   
366
          stmt.close();

    
   
367
        } catch (SQLException ex) {

    
   
368
          LoggingUtils.logAll(LOG, "Failed to close statement", ex);

    
   
369
        }

    
   
370
      }

    
   
371
    }

    
   
372
    return user;

    
   
373
  }

    
   
374

   
329
  /**
375
  /**
330
   * Set session time zone.
376
   * Set session time zone.
331
   * @param conn      Connection object
377
   * @param conn      Connection object
332
   * @throws          SQLException instance
378
   * @throws          SQLException instance
333
   */
379
   */
[+20] [20] 341 lines
[+20] [+] public String[] listDatabases() {
675
  public String[] listTables() {
721
  public String[] listTables() {
676
    Connection conn = null;
722
    Connection conn = null;
677
    PreparedStatement pStmt = null;
723
    PreparedStatement pStmt = null;
678
    ResultSet rset = null;
724
    ResultSet rset = null;
679
    List<String> tables = new ArrayList<String>();
725
    List<String> tables = new ArrayList<String>();
680
    String tableOwner = this.options.getUsername();
726
    String tableOwner = null;
681

    
   
727

   
682

    
   
728

   
683
    try {
729
    try {
684
      conn = getConnection();
730
      conn = getConnection();

    
   
731
      tableOwner = getSessionUser(conn);
685
      pStmt = conn.prepareStatement(QUERY_LIST_TABLES,
732
      pStmt = conn.prepareStatement(QUERY_LIST_TABLES,
686
          ResultSet.TYPE_FORWARD_ONLY,
733
          ResultSet.TYPE_FORWARD_ONLY,
687
              ResultSet.CONCUR_READ_ONLY);
734
              ResultSet.CONCUR_READ_ONLY);
688
      pStmt.setString(1, tableOwner);
735
      pStmt.setString(1, tableOwner);
689

    
   
736

   
[+20] [20] 170 lines
[+20] [+] public String[] getColumnNamesForProcedure(String procedureName) {
860
    Connection conn = null;
907
    Connection conn = null;
861
    PreparedStatement pStmt = null;
908
    PreparedStatement pStmt = null;
862
    ResultSet rset = null;
909
    ResultSet rset = null;
863
    List<String> columns = new ArrayList<String>();
910
    List<String> columns = new ArrayList<String>();
864

    
   
911

   
865
    String tableOwner = this.options.getUsername();
912
    String tableOwner = null;
866
    String shortTableName = tableName;
913
    String shortTableName = tableName;
867
    int qualifierIndex = tableName.indexOf('.');
914
    int qualifierIndex = tableName.indexOf('.');
868
    if (qualifierIndex != -1) {
915
    if (qualifierIndex != -1) {
869
      tableOwner = tableName.substring(0, qualifierIndex);
916
      tableOwner = tableName.substring(0, qualifierIndex);
870
      shortTableName = tableName.substring(qualifierIndex + 1);
917
      shortTableName = tableName.substring(qualifierIndex + 1);
871
    }
918
    }
872

    
   
919

   
873
    try {
920
    try {
874
      conn = getConnection();
921
      conn = getConnection();
875

    
   
922

   

    
   
923
      if (tableOwner == null) {

    
   
924
        tableOwner = getSessionUser(conn);

    
   
925
      }

    
   
926

   
876
      pStmt = conn.prepareStatement(QUERY_COLUMNS_FOR_TABLE,
927
      pStmt = conn.prepareStatement(QUERY_COLUMNS_FOR_TABLE,
877
                  ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
928
                  ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
878

    
   
929

   
879
      pStmt.setString(1, tableOwner);
930
      pStmt.setString(1, tableOwner);
880

    
   
931

   
[+20] [20] 42 lines
[+20] public String[] getColumnNamesForProcedure(String procedureName) {
923
    Connection conn = null;
974
    Connection conn = null;
924
    PreparedStatement pStmt = null;
975
    PreparedStatement pStmt = null;
925
    ResultSet rset = null;
976
    ResultSet rset = null;
926
    List<String> columns = new ArrayList<String>();
977
    List<String> columns = new ArrayList<String>();
927

    
   
978

   
928
    String tableOwner = this.options.getUsername();
979
    String tableOwner = null;
929
    String shortTableName = tableName;
980
    String shortTableName = tableName;
930
    int qualifierIndex = tableName.indexOf('.');
981
    int qualifierIndex = tableName.indexOf('.');
931
    if (qualifierIndex != -1) {
982
    if (qualifierIndex != -1) {
932
      tableOwner = tableName.substring(0, qualifierIndex);
983
      tableOwner = tableName.substring(0, qualifierIndex);
933
      shortTableName = tableName.substring(qualifierIndex + 1);
984
      shortTableName = tableName.substring(qualifierIndex + 1);
934
    }
985
    }
935

    
   
986

   
936
    try {
987
    try {
937
      conn = getConnection();
988
      conn = getConnection();
938

    
   
989

   

    
   
990
      if (tableOwner == null) {

    
   
991
        tableOwner = getSessionUser(conn);

    
   
992
      }

    
   
993

   
939
      pStmt = conn.prepareStatement(QUERY_PRIMARY_KEY_FOR_TABLE,
994
      pStmt = conn.prepareStatement(QUERY_PRIMARY_KEY_FOR_TABLE,
940
                  ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
995
                  ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
941
      pStmt.setString(1, shortTableName);
996
      pStmt.setString(1, shortTableName);
942
      pStmt.setString(2, tableOwner);
997
      pStmt.setString(2, tableOwner);
943
      rset = pStmt.executeQuery();
998
      rset = pStmt.executeQuery();
944

    
   
999

   
945
      while (rset.next()) {
1000
      while (rset.next()) {
946
        columns.add(rset.getString(1));
1001
        columns.add(rset.getString(1));
947
      }
1002
      }
948
      conn.commit();
1003
      conn.commit();
949
    } catch (SQLException e) {
1004
    } catch (SQLException e) {
950
      try {
1005
      try {

    
   
1006
        if (conn != null) {
951
        conn.rollback();
1007
          conn.rollback();

    
   
1008
        }
952
      } catch (SQLException ex) {
1009
      } catch (SQLException ex) {
953
        LoggingUtils.logAll(LOG, "Failed to rollback transaction", ex);
1010
        LoggingUtils.logAll(LOG, "Failed to rollback transaction", ex);
954
      }
1011
      }
955
      LoggingUtils.logAll(LOG, "Failed to list columns", e);
1012
      LoggingUtils.logAll(LOG, "Failed to list columns", e);
956
    } finally {
1013
    } finally {
[+20] [20] 56 lines
src/test/com/cloudera/sqoop/manager/OracleManagerTest.java
Revision 078b447 New Change
 
  1. src/java/org/apache/sqoop/manager/OracleManager.java: Loading...
  2. src/test/com/cloudera/sqoop/manager/OracleManagerTest.java: Loading...