89d16fd95ebcbf22bbfca1d29bd4165b54b7ec42
[java-idp.git] / 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 AttributeExtractorAtt = "attributeExtractor";
77         final private static String DBDriverAtt = "dbDriver";
78         final private static String AEInstanceMethodAtt = "instance";
79         final private static String DBSubProtocolAtt = "dbSubProtocol";
80         final private static String DBHostAtt = "dbHost";
81         final private static String DBNameAtt = "dbName";
82         final private static String UserNameAtt = "userName";
83         final private static String PasswordAtt = "password";
84
85         public JDBCDataConnector(Element e) throws ResolutionPlugInException {
86
87                 super(e);
88
89                 NodeList propertiesNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Property");
90                 NodeList searchNode = e.getElementsByTagNameNS(AttributeResolver.resolverNamespace, "Search");
91
92                 String propertiesName = null;
93                 String propertiesValue = null;
94
95                 /**
96                  * Gets and sets the search parameter and the attribute extractor
97                  */
98                 searchVal = ((Element) searchNode.item(0)).getAttribute(QueryAtt);
99                 aeClassName = ((Element) searchNode.item(0)).getAttribute(AttributeExtractorAtt);
100
101                 if (searchVal == null || searchVal.equals("")) {
102                         log.error("Search requires a specified query field");
103                         throw new ResolutionPlugInException("mySQLDataConnection requires a \"Search\" specification");
104                 } else {
105                         log.debug("Search Query: (" + searchVal + ")");
106                 }
107
108                 /**
109                  * Assigns the property attribute name/value pairs to a hashtable
110                  */
111                 for (int i = 0; propertiesNode.getLength() > i; i++) {
112                         Element property = (Element) propertiesNode.item(i);
113                         propertiesName = property.getAttribute("name");
114                         propertiesValue = property.getAttribute("value");
115
116                         if (propertiesName != null
117                                 && !propertiesName.equals("")
118                                 && propertiesValue != null
119                                 && !propertiesValue.equals("")) {
120                                 env.put(propertiesName, propertiesValue);
121                                 log.debug("Property: (" + propertiesName + ")");
122                                 log.debug("   Value: (" + propertiesValue + ")");
123                         } else {
124                                 log.error("Property is malformed.");
125                                 throw new ResolutionPlugInException("Property is malformed.");
126                         }
127                 }
128         }
129
130         public Attributes resolve(Principal principal) throws ResolutionPlugInException {
131                 Connection conn = null;
132                 ResultSet rs = null;
133                 ResultSetMetaData rsmd = null;
134                 BasicAttributes attributes = new BasicAttributes();
135                 JDBCAttributeExtractor aeClassObj = null;
136
137                 log.debug("Resolving connector: (" + getId() + ")");
138                 log.debug(getId() + " resolving for principal: (" + principal.getName() + ")");
139
140                 //Replaces %PRINCIPAL% in the query string with its value
141                 log.debug("The query string before coverting %PRINCIPAL%: " + searchVal);
142                 searchVal = searchVal.replaceAll("%PRINCIPAL%", principal.getName());
143                 log.debug("The query string after converting %PRINCIPAL%: " + searchVal);
144
145                 try {
146                         //Loads the database driver
147                         loadDriver((String) env.get(DBDriverAtt));
148                 } catch (ClassNotFoundException e) {
149                         log.error("An ClassNotFoundException occured while loading database driver");
150                         throw new ResolutionPlugInException(
151                                 "An ClassNotFoundException occured while loading database driver: " + e.getMessage());
152                 } catch (IllegalAccessException e) {
153                         log.error("An IllegalAccessException occured while loading database driver");
154                         throw new ResolutionPlugInException(
155                                 "An IllegalAccessException occured while loading database driver: " + e.getMessage());
156                 } catch (InstantiationException e) {
157                         log.error("An InstantionException occured while loading database driver");
158                         throw new ResolutionPlugInException(
159                                 "An InstantiationException occured while loading database driver: " + e.getMessage());
160                 }
161
162                 try {
163                         //Makes the connection to the database
164                         conn =
165                                 connect(
166                                         (String) env.get(DBSubProtocolAtt),
167                                         (String) env.get(DBHostAtt),
168                                         (String) env.get(DBNameAtt),
169                                         (String) env.get(UserNameAtt),
170                                         (String) env.get(PasswordAtt));
171                 } catch (SQLException e) {
172                         log.error("An ERROR occured while connecting to database");
173                         throw new ResolutionPlugInException("An ERROR occured while connecting to the database: " + e.getMessage());
174                 }
175
176                 try {
177                         //Gets the results set for the query
178                         rs = executeQuery(conn, searchVal);
179                 } catch (SQLException e) {
180                         log.error("An ERROR occured while executing the query");
181                         throw new ResolutionPlugInException("An ERROR occured while executing the query: " + e.getMessage());
182                 }
183
184                 /**
185                  * If the user has supplied their own class for extracting the attributes from the 
186                  * result set, then their class will be run.  A BasicAttributes object is expected as
187                  * the result of the extraction.
188                  *
189                  * If the user has no supplied their own class for extracting the attributes then 
190                  * the default extraction is run, which is specified in DefaultAEAtt.
191                  */
192                 if (aeClassName == null || aeClassName.equals("")) {
193                         aeClassName = DefaultAE.class.getName();
194                 }
195
196                 try {
197                         Class aeClass = Class.forName(aeClassName);
198                         Method aeMethod = aeClass.getMethod(AEInstanceMethodAtt, null);
199
200                         //runs the "instance" method returning and instance of the object
201                         aeClassObj = (JDBCAttributeExtractor) (aeMethod.invoke(null, null));
202                         log.debug("Supplied attributeExtractor class loaded.");
203
204                 } catch (ClassNotFoundException e) {
205                         log.error("The supplied attribute extractor class could not be found");
206                         throw new ResolutionPlugInException(
207                                 "The supplied attribute extractor class could not be found: " + e.getMessage());
208                 } catch (NoSuchMethodException e) {
209                         log.error("The requested method does not exist");
210                         throw new ResolutionPlugInException("The requested method does not exist: " + e.getMessage());
211                 } catch (IllegalAccessException e) {
212                         log.error("Access is not permitted for invoking requested method");
213                         throw new ResolutionPlugInException(
214                                 "Access is not permitted for invoking requested method: " + e.getMessage());
215                 } catch (InvocationTargetException e) {
216                         log.error("An ERROR occured invoking requested method");
217                         throw new ResolutionPlugInException("An ERROR occured involking requested method: " + e.getMessage());
218                 }
219
220                 try {
221                         return aeClassObj.extractAttributes(rs);
222
223                 } catch (JDBCAttributeExtractorException e) {
224                         log.error("An ERROR occured while extracting attributes from result set");
225                         throw new ResolutionPlugInException(
226                                 "An ERROR occured while extracting attributes from result set: " + e.getMessage());
227                 } finally {
228                         try {
229                                 //release result set
230                                 rs.close();
231                                 log.debug("Result set released");
232                         } catch (SQLException e) {
233                                 log.error("An error occured while closing the result set: " + e);
234                                 throw new ResolutionPlugInException("An error occured while closing the result set: " + e);
235                         }
236
237                         try {
238                                 //close the connection
239                                 conn.close();
240                                 log.debug("Connection to database closed");
241                         } catch (SQLException e) {
242                                 log.error("An error occured while closing the database connection: " + e);
243                                 throw new ResolutionPlugInException("An error occured while closing the database connection: " + e);
244                         }
245                 }
246         }
247
248         /** 
249          * Loads the driver used to access the database
250          * @param driver The driver used to access the database
251          * @throws ResolutionPlugInException If there is a failure to load the driver
252          */
253         public void loadDriver(String driver)
254                 throws ClassNotFoundException, IllegalAccessException, InstantiationException {
255                 Class.forName(driver).newInstance();
256                 log.debug("Loading driver: " + driver);
257         }
258
259         /** 
260          * Makes a connection to the database
261          * @param subProtocal Specifies the sub protocal to use when connecting
262          * @param hostName  The host name for the database
263          * @param dbName The database to access
264          * @param userName The username to connect with
265          * @param password The password to connect with
266          * @return Connection objecet
267          * @throws SQLException If there is a failure to make a database connection
268          */
269         public Connection connect(String subProtocol, String hostName, String dbName, String userName, String password)
270                 throws SQLException {
271                 log.debug(
272                         "jdbc:" + subProtocol + "://" + hostName + "/" + dbName + "?user=" + userName + "&password=" + password);
273                 Connection conn =
274                         DriverManager.getConnection("jdbc:" + subProtocol + "://" + hostName + "/" + dbName, userName, password);
275                 log.debug("Connection with database established");
276
277                 return conn;
278         }
279
280         /**
281          * Execute the users query
282          * @param query The query the user wishes to execute
283          * @return The result of the users <code>query</code>
284          * @return null if an error occurs during execution
285          * @throws SQLException If an error occurs while executing the query
286         */
287         public ResultSet executeQuery(Connection conn, String query) throws SQLException {
288                 log.debug("Users Query: " + query);
289                 Statement stmt = conn.createStatement();
290                 return stmt.executeQuery(query);
291         }
292 }
293
294 /**
295  * The default attribute extractor. 
296  */
297
298 class DefaultAE implements JDBCAttributeExtractor {
299         private static DefaultAE _instance = null;
300         private static Logger log = Logger.getLogger(DefaultAE.class.getName());
301
302         // Constructor
303         protected DefaultAE() {
304         }
305
306         // Ensures that only one istance of the class at a time
307         public static DefaultAE instance() {
308                 if (_instance == null)
309                         return new DefaultAE();
310                 else
311                         return _instance;
312         }
313
314         /**
315          * Method of extracting the attributes from the supplied result set.
316          *
317          * @param ResultSet The result set from the query which contains the attributes
318          * @return BasicAttributes as objects containing all the attributes
319          * @throws JDBCAttributeExtractorException If there is a complication in retrieving the attributes
320          */
321         public BasicAttributes extractAttributes(ResultSet rs) throws JDBCAttributeExtractorException {
322                 String columnName = null;
323                 String columnType = null;
324                 int numRows = 0, numColumns = 0;
325                 ResultSetMetaData rsmd = null;
326                 BasicAttributes attributes = new BasicAttributes();
327                 Object columnValue = new Object();
328
329                 log.debug("Using default Attribute Extractor");
330
331                 try {
332                         rs.last();
333                         numRows = rs.getRow();
334                         rs.first();
335                 } catch (SQLException e) {
336                         log.error("An ERROR occured while determining result set row size");
337                         throw new JDBCAttributeExtractorException(
338                                 "An ERROR occured while determining result set row size: " + e.getMessage());
339                 }
340
341                 log.debug("The number of rows returned is: " + numRows);
342
343                 if (numRows > 1)
344                         throw new JDBCAttributeExtractorException("Query returned more than one result set.");
345
346                 try {
347                         rsmd = rs.getMetaData();
348                         numColumns = rsmd.getColumnCount();
349                         log.debug("Number of returned columns: " + numColumns);
350
351                         for (int i = 1; i <= numColumns; i++) {
352                                 columnName = rsmd.getColumnName(i);
353                                 columnType = rsmd.getColumnTypeName(i);
354                                 columnValue = rs.getObject(columnName);
355                                 log.debug(
356                                         "(" + i + ". ColumnType = " + columnType + ") " + columnName + " -> " + columnValue.toString());
357                                 attributes.put(new BasicAttribute(columnName, columnValue));
358                         }
359                 } catch (SQLException e) {
360                         log.error("An ERROR occured while retrieving result set meta data");
361                         throw new JDBCAttributeExtractorException(
362                                 "An ERROR occured while retrieving result set meta data: " + e.getMessage());
363                 }
364
365                 return attributes;
366         }
367 }