src/edu/internet2/middleware/shibboleth/aa/attrresolv/provider/JDBCDataConnector.java

   1 /*
   2  * Copyright (c) 2003 National Research Council of Canada
   3  *
   4  * Permission is hereby granted, free of charge, to any person
   5  * obtaining a copy of this software and associated documentation
   6  * files (the "Software"), to deal in the Software without
   7  * restriction, including without limitation the rights to use,
   8  * copy, modify, merge, publish, distribute, sublicense, and/or
   9  * sell copies of the Software, and to permit persons to whom the
  10  * Software is furnished to do so, subject to the following conditions:
  11  *
  12  * The above copyright notice and this permission notice shall be
  13  * included in all copies or substantial portions of the Software.
  14  *
  15  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  16  * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
  17  * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  18  * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
  19  * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
  20  * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
  21  * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
  22  * OTHER DEALINGS IN THE SOFTWARE.
  23  *
  24  */
  25
  26 package edu.internet2.middleware.shibboleth.aa.attrresolv.provider;
  27
  28 import java.lang.reflect.InvocationTargetException;
  29 import java.lang.reflect.Method;
  30 import java.security.Principal;
  31 import java.sql.Connection;
  32 import java.sql.DriverManager;
  33 import java.sql.ResultSet;
  34 import java.sql.ResultSetMetaData;
  35 import java.sql.SQLException;
  36 import java.sql.Statement;
  37 import java.util.Hashtable;
  38
  39 import javax.naming.directory.Attributes;
  40 import javax.naming.directory.BasicAttribute;
  41 import javax.naming.directory.BasicAttributes;
  42
  43 import org.apache.log4j.Logger;
  44 import org.w3c.dom.Element;
  45 import org.w3c.dom.NodeList;
  46
  47 import edu.internet2.middleware.shibboleth.aa.attrresolv.AttributeResolver;
  48 import edu.internet2.middleware.shibboleth.aa.attrresolv.DataConnectorPlugIn;
  49 import edu.internet2.middleware.shibboleth.aa.attrresolv.ResolutionPlugInException;
  50
  51 /*
  52  * Built at the Canada Institute for Scientific and Technical Information (CISTI
  53  * <ahref="http://www.cisti-icist.nrc-cnrc.gc.ca/">http://www.cisti-icist.nrc-cnrc.gc.ca/</a>,
  54  * the National Research Council Canada
  55  * (NRC <a href="http://www.nrc-cnrc.gc.ca/">http://www.nrc-cnrc.gc.ca/</a>)
  56  * by David Dearman, COOP student from Dalhousie University,
  57  * under the direction of Glen Newton, Head research (IT)
  58  * <ahref="mailto:glen.newton@nrc-cnrc.gc.ca">glen.newton@nrc-cnrc.gc.ca</a>.
  59  */
  60
  61 /**
  62  * Data Connector that uses JDBC to access user attributes stored in databases.
  63  *
  64  * @author David Dearman (dearman@cs.dal.ca)
  65  * @version 0.1 July 23, 2003
  66  */
  67
  68 public class JDBCDataConnector extends BaseResolutionPlugIn implements DataConnectorPlugIn {
  69
  70         private static Logger log = Logger.getLogger(JDBCDataConnector.class.getName());
  71         private Hashtable env = new Hashtable();
  72         private String searchVal = null;
  73         private String aeClassName = null;
  74
  75         final private static String QueryAtt = "query";
  76         final private static String SearchAtt = "Search";
  77         final private static String AttributeExtractorAtt = "attributeExtractor";
  78         final private static String NameAtt = "name";
  79         final private static String ValueAtt = "value";
  80         final private static String DBDriverAtt = "dbDriver";
  81         final private static String AEInstanceMethodAtt = "instance";
  82         final private static String PropertyAtt = "Property";
  83         final private static String DBSubProtocolAtt = "dbSubProtocol";
  84         final private static String DBHostAtt = "dbHost";
  85         final private static String DBNameAtt = "dbName";
  86         final private static String UserNameAtt = "userName";
  87         final private static String PasswordAtt = "password";
  88         final private static String DefaultAEAtt = "ca.nrc.cisti.shibboleth.attrresolv.provider.DefaultAE";
  89
  90         public JDBCDataConnector(Element e) throws ResolutionPlugInException {
  91
  92                 super(e);
  93
  94                 NodeList propertiesNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, PropertyAtt);
  95                 NodeList searchNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, SearchAtt);
  96
  97                 String propertiesName = null;
  98                 String propertiesValue = null;
  99
 100                 /**
 101                  * Gets and sets the search parameter and the attribute extractor
 102                  */
 103                 searchVal = ((Element) searchNode.item(0)).getAttribute(QueryAtt);
 104                 aeClassName = ((Element) searchNode.item(0)).getAttribute(AttributeExtractorAtt);
 105
 106                 if (searchVal == null || searchVal.equals("")) {
 107                         log.error("Search requires a specified query field");
 108                         throw new ResolutionPlugInException("mySQLDataConnection requires a \"Search\" specification");
 109                 } else {
 110                         log.debug("Search Query: (" + searchVal + ")");
 111                 }
 112
 113                 /**
 114                  * Assigns the property attribute name/value pairs to a hashtable
 115                  */
 116                 for (int i = 0; propertiesNode.getLength() > i; i++) {
 117                         Element property = (Element) propertiesNode.item(i);
 118                         propertiesName = property.getAttribute(NameAtt);
 119                         propertiesValue = property.getAttribute(ValueAtt);
 120
 121                         if (propertiesName != null
 122                                 && !propertiesName.equals("")
 123                                 && propertiesValue != null
 124                                 && !propertiesValue.equals("")) {
 125                                 env.put(propertiesName, propertiesValue);
 126                                 log.debug("Property: (" + propertiesName + ")");
 127                                 log.debug("   Value: (" + propertiesValue + ")");
 128                         } else {
 129                                 log.error("Property is malformed.");
 130                                 throw new ResolutionPlugInException("Property is malformed.");
 131                         }
 132                 }
 133         }
 134
 135         public Attributes resolve(Principal principal) throws ResolutionPlugInException {
 136                 Connection conn = null;
 137                 ResultSet rs = null;
 138                 ResultSetMetaData rsmd = null;
 139                 BasicAttributes attributes = new BasicAttributes();
 140                 JDBCAttributeExtractor aeClassObj = null;
 141
 142                 log.debug("Resolving connector: (" + getId() + ")");
 143                 log.debug(getId() + " resolving for principal: (" + principal.getName() + ")");
 144
 145                 //Replaces %PRINCIPAL% in the query string with its value
 146                 log.debug("The query string before coverting %PRINCIPAL%: " + searchVal);
 147                 searchVal = searchVal.replaceAll("%PRINCIPAL%", principal.getName());
 148                 log.debug("The query string after converting %PRINCIPAL%: " + searchVal);
 149
 150                 try {
 151                         //Loads the database driver
 152                         loadDriver((String) env.get(DBDriverAtt));
 153                 } catch (ClassNotFoundException e) {
 154                         log.error("An ClassNotFoundException occured while loading database driver");
 155                         throw new ResolutionPlugInException(
 156                                 "An ClassNotFoundException occured while loading database driver: " + e.getMessage());
 157                 } catch (IllegalAccessException e) {
 158                         log.error("An IllegalAccessException occured while loading database driver");
 159                         throw new ResolutionPlugInException(
 160                                 "An IllegalAccessException occured while loading database driver: " + e.getMessage());
 161                 } catch (InstantiationException e) {
 162                         log.error("An InstantionException occured while loading database driver");
 163                         throw new ResolutionPlugInException(
 164                                 "An InstantiationException occured while loading database driver: " + e.getMessage());
 165                 }
 166
 167                 try {
 168                         //Makes the connection to the database
 169                         conn =
 170                                 connect(
 171                                         (String) env.get(DBSubProtocolAtt),
 172                                         (String) env.get(DBHostAtt),
 173                                         (String) env.get(DBNameAtt),
 174                                         (String) env.get(UserNameAtt),
 175                                         (String) env.get(PasswordAtt));
 176                 } catch (SQLException e) {
 177                         log.error("An ERROR occured while connecting to database");
 178                         throw new ResolutionPlugInException("An ERROR occured while connecting to the database: " + e.getMessage());
 179                 }
 180
 181                 try {
 182                         //Gets the results set for the query
 183                         rs = executeQuery(conn, searchVal);
 184                 } catch (SQLException e) {
 185                         log.error("An ERROR occured while executing the query");
 186                         throw new ResolutionPlugInException("An ERROR occured while executing the query: " + e.getMessage());
 187                 }
 188
 189                 /**
 190                  * If the user has supplied their own class for extracting the attributes from the
 191                  * result set, then their class will be run.  A BasicAttributes object is expected as
 192                  * the result of the extraction.
 193                  *
 194                  * If the user has no supplied their own class for extracting the attributes then
 195                  * the default extraction is run, which is specified in DefaultAEAtt.
 196                  */
 197                 if (aeClassName == null || aeClassName.equals(""))
 198                         aeClassName = DefaultAEAtt;
 199
 200                 try {
 201                         Class aeClass = Class.forName(aeClassName);
 202                         Method aeMethod = aeClass.getMethod(AEInstanceMethodAtt, null);
 203
 204                         //runs the "instance" method returning and instance of the object
 205                         aeClassObj = (JDBCAttributeExtractor) (aeMethod.invoke(null, null));
 206                         log.debug("Supplied attributeExtractor class loaded.");
 207
 208                 } catch (ClassNotFoundException e) {
 209                         log.error("The supplied attribute extractor class could not be found");
 210                         throw new ResolutionPlugInException(
 211                                 "The supplied attribute extractor class could not be found: " + e.getMessage());
 212                 } catch (NoSuchMethodException e) {
 213                         log.error("The requested method does not exist");
 214                         throw new ResolutionPlugInException("The requested method does not exist: " + e.getMessage());
 215                 } catch (IllegalAccessException e) {
 216                         log.error("Access is not permitted for invoking requested method");
 217                         throw new ResolutionPlugInException(
 218                                 "Access is not permitted for invoking requested method: " + e.getMessage());
 219                 } catch (InvocationTargetException e) {
 220                         log.error("An ERROR occured invoking requested method");
 221                         throw new ResolutionPlugInException("An ERROR occured involking requested method: " + e.getMessage());
 222                 }
 223
 224                 try {
 225                         return aeClassObj.extractAttributes(rs);
 226
 227                 } catch (JDBCAttributeExtractorException e) {
 228                         log.error("An ERROR occured while extracting attributes from result set");
 229                         throw new ResolutionPlugInException(
 230                                 "An ERROR occured while extracting attributes from result set: " + e.getMessage());
 231                 } finally {
 232                         try {
 233                                 //release result set
 234                                 rs.close();
 235                                 log.debug("Result set released");
 236                         } catch (SQLException e) {
 237                                 log.error("An error occured while closing the result set: " + e);
 238                                 throw new ResolutionPlugInException("An error occured while closing the result set: " + e);
 239                         }
 240
 241                         try {
 242                                 //close the connection
 243                                 conn.close();
 244                                 log.debug("Connection to database closed");
 245                         } catch (SQLException e) {
 246                                 log.error("An error occured while closing the database connection: " + e);
 247                                 throw new ResolutionPlugInException("An error occured while closing the database connection: " + e);
 248                         }
 249                 }
 250         }
 251
 252         /**
 253          * Loads the driver used to access the database
 254          * @param driver The driver used to access the database
 255          * @throws ResolutionPlugInException If there is a failure to load the driver
 256          */
 257         public void loadDriver(String driver)
 258                 throws ClassNotFoundException, IllegalAccessException, InstantiationException {
 259                 Class.forName(driver).newInstance();
 260                 log.debug("Loading driver: " + driver);
 261         }
 262
 263         /**
 264          * Makes a connection to the database
 265          * @param subProtocal Specifies the sub protocal to use when connecting
 266          * @param hostName  The host name for the database
 267          * @param dbName The database to access
 268          * @param userName The username to connect with
 269          * @param password The password to connect with
 270          * @return Connection objecet
 271          * @throws SQLException If there is a failure to make a database connection
 272          */
 273         public Connection connect(String subProtocol, String hostName, String dbName, String userName, String password)
 274                 throws SQLException {
 275                 log.debug(
 276                         "jdbc:" + subProtocol + "://" + hostName + "/" + dbName + "?user=" + userName + "&password=" + password);
 277                 Connection conn =
 278                         DriverManager.getConnection("jdbc:" + subProtocol + "://" + hostName + "/" + dbName, userName, password);
 279                 log.debug("Connection with database established");
 280
 281                 return conn;
 282         }
 283
 284         /**
 285          * Execute the users query
 286          * @param query The query the user wishes to execute
 287          * @return The result of the users <code>query</code>
 288          * @return null if an error occurs during execution
 289          * @throws SQLException If an error occurs while executing the query
 290         */
 291         public ResultSet executeQuery(Connection conn, String query) throws SQLException {
 292                 log.debug("Users Query: " + query);
 293                 Statement stmt = conn.createStatement();
 294                 return stmt.executeQuery(query);
 295         }
 296 }
 297
 298 /**
 299  * The default attribute extractor.
 300  */
 301
 302 class DefaultAE implements JDBCAttributeExtractor {
 303         private static DefaultAE _instance = null;
 304         private static Logger log = Logger.getLogger(DefaultAE.class.getName());
 305
 306         // Constructor
 307         protected DefaultAE() {
 308         }
 309
 310         // Ensures that only one istance of the class at a time
 311         public static DefaultAE instance() {
 312                 if (_instance == null)
 313                         return new DefaultAE();
 314                 else
 315                         return _instance;
 316         }
 317
 318         /**
 319          * Method of extracting the attributes from the supplied result set.
 320          *
 321          * @param ResultSet The result set from the query which contains the attributes
 322          * @return BasicAttributes as objects containing all the attributes
 323          * @throws JDBCAttributeExtractorException If there is a complication in retrieving the attributes
 324          */
 325         public BasicAttributes extractAttributes(ResultSet rs) throws JDBCAttributeExtractorException {
 326                 String columnName = null;
 327                 String columnType = null;
 328                 int numRows = 0, numColumns = 0;
 329                 ResultSetMetaData rsmd = null;
 330                 BasicAttributes attributes = new BasicAttributes();
 331                 Object columnValue = new Object();
 332
 333                 log.debug("Using default Attribute Extractor");
 334
 335                 try {
 336                         rs.last();
 337                         numRows = rs.getRow();
 338                         rs.first();
 339                 } catch (SQLException e) {
 340                         log.error("An ERROR occured while determining result set row size");
 341                         throw new JDBCAttributeExtractorException(
 342                                 "An ERROR occured while determining result set row size: " + e.getMessage());
 343                 }
 344
 345                 log.debug("The number of rows returned is: " + numRows);
 346
 347                 if (numRows > 1)
 348                         throw new JDBCAttributeExtractorException("Query returned more than one result set.");
 349
 350                 try {
 351                         rsmd = rs.getMetaData();
 352                         numColumns = rsmd.getColumnCount();
 353                         log.debug("Number of returned columns: " + numColumns);
 354
 355                         for (int i = 1; i <= numColumns; i++) {
 356                                 columnName = rsmd.getColumnName(i);
 357                                 columnType = rsmd.getColumnTypeName(i);
 358                                 columnValue = rs.getObject(columnName);
 359                                 log.debug(
 360                                         "(" + i + ". ColumnType = " + columnType + ") " + columnName + " -> " + columnValue.toString());
 361                                 attributes.put(new BasicAttribute(columnName, columnValue));
 362                         }
 363                 } catch (SQLException e) {
 364                         log.error("An ERROR occured while retrieving result set meta data");
 365                         throw new JDBCAttributeExtractorException(
 366                                 "An ERROR occured while retrieving result set meta data: " + e.getMessage());
 367                 }
 368
 369                 return attributes;
 370         }
 371 }
 372
 373 /**
 374  * The interface for the attribute extractor.
 375  *
 376  * Built at the Canada Institute for Scientific and Technical Information (CISTI
 377  * <ahref="http://www.cisti-icist.nrc-cnrc.gc.ca/">http://www.cisti-icist.nrc-cnrc.gc.ca/</a>,
 378  * the National Research Council Canada
 379  * (NRC <a href="http://www.nrc-cnrc.gc.ca/">http://www.nrc-cnrc.gc.ca/</a>)
 380  *
 381  * by David Dearman, COOP student from Dalhousie University,
 382  * under the direction of Glen Newton, Head research (IT)
 383  * <ahref="mailto:glen.newton@nrc-cnrc.gc.ca">glen.newton@nrc-cnrc.gc.ca</a>.
 384  *
 385  * @author David Dearman (dearman@cs.dal.ca)
 386  * @version 1.0 July 24, 2003
 387  *
 388  */