001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/security/drm/SQLRegistry.java $
002 /*----------------------------------------------------------------------------
003 This file is part of deegree, http://deegree.org/
004 Copyright (C) 2001-2009 by:
005 Department of Geography, University of Bonn
006 and
007 lat/lon GmbH
008
009 This library is free software; you can redistribute it and/or modify it under
010 the terms of the GNU Lesser General Public License as published by the Free
011 Software Foundation; either version 2.1 of the License, or (at your option)
012 any later version.
013 This library is distributed in the hope that it will be useful, but WITHOUT
014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
016 details.
017 You should have received a copy of the GNU Lesser General Public License
018 along with this library; if not, write to the Free Software Foundation, Inc.,
019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020
021 Contact information:
022
023 lat/lon GmbH
024 Aennchenstr. 19, 53177 Bonn
025 Germany
026 http://lat-lon.de/
027
028 Department of Geography, University of Bonn
029 Prof. Dr. Klaus Greve
030 Postfach 1147, 53001 Bonn
031 Germany
032 http://www.geographie.uni-bonn.de/deegree/
033
034 e-mail: info@deegree.org
035 ----------------------------------------------------------------------------*/
036 package org.deegree.security.drm;
037
038 import java.io.BufferedReader;
039 import java.io.IOException;
040 import java.io.InputStreamReader;
041 import java.io.Reader;
042 import java.io.StringReader;
043 import java.io.Writer;
044 import java.sql.Clob;
045 import java.sql.Connection;
046 import java.sql.PreparedStatement;
047 import java.sql.ResultSet;
048 import java.sql.ResultSetMetaData;
049 import java.sql.SQLException;
050 import java.sql.Statement;
051 import java.sql.Types;
052 import java.util.ArrayList;
053 import java.util.HashMap;
054 import java.util.LinkedList;
055 import java.util.List;
056 import java.util.Map;
057 import java.util.Properties;
058
059 import org.deegree.framework.log.ILogger;
060 import org.deegree.framework.log.LoggerFactory;
061 import org.deegree.framework.util.DataBaseIDGenerator;
062 import org.deegree.framework.util.StringPair;
063 import org.deegree.framework.util.StringTools;
064 import org.deegree.framework.xml.XMLTools;
065 import org.deegree.io.DBConnectionPool;
066 import org.deegree.io.IDGeneratorFactory;
067 import org.deegree.model.filterencoding.AbstractFilter;
068 import org.deegree.model.filterencoding.ComplexFilter;
069 import org.deegree.model.filterencoding.Filter;
070 import org.deegree.model.filterencoding.FilterConstructionException;
071 import org.deegree.security.GeneralSecurityException;
072 import org.deegree.security.drm.model.Group;
073 import org.deegree.security.drm.model.Privilege;
074 import org.deegree.security.drm.model.Right;
075 import org.deegree.security.drm.model.RightType;
076 import org.deegree.security.drm.model.Role;
077 import org.deegree.security.drm.model.SecurableObject;
078 import org.deegree.security.drm.model.SecuredObject;
079 import org.deegree.security.drm.model.Service;
080 import org.deegree.security.drm.model.User;
081 import org.w3c.dom.Document;
082 import org.w3c.dom.Element;
083
084 /**
085 * This is an implementation of a <code>Registry</code> using an SQL-Database (via JDBC) as backend.
086 *
087 * @author <a href="mailto:mschneider@lat-lon.de">Markus Schneider </a>
088 * @version $Revision: 18195 $
089 */
090 public final class SQLRegistry implements SecurityRegistry {
091
092 private static final ILogger LOG = LoggerFactory.getLogger( SQLRegistry.class );
093
094 private String dbDriver;
095
096 private String dbName;
097
098 private String dbUser;
099
100 private String dbPassword;
101
102 /** Exclusive connection for a transaction (only one at a time). */
103 private Connection transactionalConnection = null;
104
105 public void clean( SecurityTransaction transaction )
106 throws GeneralSecurityException {
107
108 PreparedStatement pstmt = null;
109 try {
110 BufferedReader reader = new BufferedReader(
111 new InputStreamReader(
112 SQLRegistry.class.getResourceAsStream( "clean.sql" ) ) );
113 StringBuffer sb = new StringBuffer( 5000 );
114 String line = null;
115 while ( ( line = reader.readLine() ) != null ) {
116 sb.append( line );
117 }
118 String tmp = sb.toString();
119 String[] commands = StringTools.toArray( tmp, ";", false );
120 for ( int i = 0; i < commands.length; i++ ) {
121 String command = commands[i].trim();
122 if ( !command.equals( "" ) ) {
123 pstmt = transactionalConnection.prepareStatement( command );
124 pstmt.executeUpdate();
125 closeStatement( pstmt );
126 pstmt = null;
127 }
128 }
129 } catch ( SQLException e ) {
130 LOG.logError( e.getMessage(), e );
131 closeStatement( pstmt );
132 abortTransaction( transaction );
133 throw new GeneralSecurityException( "SQLRegistry.clean() failed. Rollback performed. " + "Error message: "
134 + e.getMessage() );
135 } catch ( IOException e ) {
136 LOG.logError( e.getMessage(), e );
137 throw new GeneralSecurityException( "SQLRegistry.clean() failed. Problem reading sql command file. "
138 + "Error message: " + e.getMessage() );
139 }
140 }
141
142 /**
143 * Adds a new user account to the <code>Registry</code>.
144 *
145 * @param transaction
146 * @param name
147 * @throws GeneralSecurityException
148 * this is a <code>DuplicateException</code> if the group already existed
149 */
150 public User registerUser( SecurityTransaction transaction, String name, String password, String lastName,
151 String firstName, String emailAddress )
152 throws GeneralSecurityException {
153 try {
154 getUserByName( transaction, name );
155 throw new DuplicateException( "Registration of user '" + name + "' failed! A user with "
156 + "this name already exists." );
157 } catch ( UnknownException e ) {
158 // then it's no duplicate
159 }
160
161 User user = new User( getID( transaction, "SEC_SECURABLE_OBJECTS" ), name, password, firstName, lastName,
162 emailAddress, this );
163 PreparedStatement pstmt = null;
164
165 try {
166 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SECURABLE_OBJECTS (ID,NAME,TITLE) VALUES (?,?,?)" );
167 pstmt.setInt( 1, user.getID() );
168 pstmt.setString( 2, user.getName() );
169 pstmt.setString( 3, user.getTitle() );
170 pstmt.executeUpdate();
171 closeStatement( pstmt );
172 pstmt = null;
173
174 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_USERS (ID,PASSWORD,FIRSTNAME,LASTNAME,EMAIL) VALUES (?,?,?,?,?)" );
175 pstmt.setInt( 1, user.getID() );
176 pstmt.setString( 2, password );
177 pstmt.setString( 3, user.getFirstName() );
178 pstmt.setString( 4, user.getLastName() );
179 pstmt.setString( 5, user.getEmailAddress() );
180 pstmt.executeUpdate();
181 closeStatement( pstmt );
182 } catch ( SQLException e ) {
183 LOG.logError( e.getMessage(), e );
184 closeStatement( pstmt );
185 abortTransaction( transaction );
186 throw new GeneralSecurityException( "SQLRegistry.registerUser() failed. Rollback performed. "
187 + "Error message: " + e.getMessage() );
188 }
189 return user;
190 }
191
192 /**
193 * Removes an existing <code>User<code> from the <code>Registry</code> (including its relations).
194 *
195 * @param transaction
196 * @param user
197 * @throws GeneralSecurityException
198 */
199 public void deregisterUser( SecurityTransaction transaction, User user )
200 throws GeneralSecurityException {
201 PreparedStatement pstmt = null;
202
203 try {
204 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_GROUPS WHERE FK_USERS=?" );
205 pstmt.setInt( 1, user.getID() );
206 pstmt.executeUpdate();
207 pstmt.close();
208 pstmt = null;
209 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_ROLES WHERE FK_USERS=?" );
210 pstmt.setInt( 1, user.getID() );
211 pstmt.executeUpdate();
212 pstmt.close();
213 pstmt = null;
214 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_USERS WHERE ID=?" );
215 pstmt.setInt( 1, user.getID() );
216 pstmt.executeUpdate();
217 pstmt.close();
218 pstmt = null;
219 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_SECURABLE_OBJECTS=?" );
220 pstmt.setInt( 1, user.getID() );
221 pstmt.executeUpdate();
222 pstmt.close();
223 pstmt = null;
224 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_SECURABLE_OBJECTS WHERE ID=?" );
225 pstmt.setInt( 1, user.getID() );
226 pstmt.executeUpdate();
227 pstmt.close();
228 } catch ( SQLException e ) {
229 LOG.logError( e.getMessage(), e );
230 closeStatement( pstmt );
231 abortTransaction( transaction );
232 throw new GeneralSecurityException( "SQLRegistry.deregisterUser() failed. Rollback performed. "
233 + "Error message: " + e.getMessage() );
234 }
235 }
236
237 /**
238 * Updates the metadata (name, email, etc.) of a <code>User</code> in the <code>Registry</code>.
239 *
240 * @throws GeneralSecurityException
241 * this is a <code>DuplicateException</code> if a user with the new name already existed
242 */
243 public void updateUser( SecurityTransaction transaction, User user )
244 throws GeneralSecurityException {
245
246 PreparedStatement pstmt = null;
247
248 try {
249 pstmt = transactionalConnection.prepareStatement( "UPDATE SEC_SECURABLE_OBJECTS SET NAME=?,TITLE=? WHERE ID=?" );
250 pstmt.setString( 1, user.getName() );
251 pstmt.setString( 2, user.getTitle() );
252 pstmt.setInt( 3, user.getID() );
253 pstmt.executeUpdate();
254 closeStatement( pstmt );
255 pstmt = null;
256
257 pstmt = transactionalConnection.prepareStatement( "UPDATE SEC_USERS SET PASSWORD=?,FIRSTNAME=?,LASTNAME=?,EMAIL=? WHERE ID=?" );
258 pstmt.setString( 1, user.getPassword() );
259 pstmt.setString( 2, user.getFirstName() );
260 pstmt.setString( 3, user.getLastName() );
261 pstmt.setString( 4, user.getEmailAddress() );
262 pstmt.setInt( 5, user.getID() );
263 pstmt.executeUpdate();
264 closeStatement( pstmt );
265 } catch ( SQLException e ) {
266 LOG.logError( e.getMessage(), e );
267 closeStatement( pstmt );
268 abortTransaction( transaction );
269 throw new GeneralSecurityException( "SQLRegistry.registerUser() failed. Rollback performed. "
270 + "Error message: " + e.getMessage() );
271 }
272 }
273
274 /**
275 * Retrieves a <code>User</code> from the <code>Registry</code>.
276 *
277 * @param securityAccess
278 * @param name
279 * @throws GeneralSecurityException
280 * this is an <code>UnknownException</code> if the user is not known to the <code>Registry</code>
281 *
282 */
283 public User getUserByName( SecurityAccess securityAccess, String name )
284 throws GeneralSecurityException {
285 User user = null;
286 Connection con = acquireLocalConnection( securityAccess );
287 PreparedStatement pstmt = null;
288 ResultSet rs = null;
289
290 try {
291 pstmt = con.prepareStatement( "SELECT SEC_USERS.ID,SEC_USERS.PASSWORD,SEC_USERS.FIRSTNAME,SEC_USERS.LASTNAME,SEC_USERS.EMAIL "
292 + "FROM SEC_USERS,SEC_SECURABLE_OBJECTS "
293 + "WHERE SEC_USERS.ID=SEC_SECURABLE_OBJECTS.ID AND "
294 + "SEC_SECURABLE_OBJECTS.NAME=?" );
295 pstmt.setString( 1, name );
296 rs = pstmt.executeQuery();
297 if ( rs.next() ) {
298 user = new User( rs.getInt( 1 ), name, rs.getString( 2 ), rs.getString( 3 ), rs.getString( 4 ),
299 rs.getString( 5 ), this );
300 } else {
301 throw new UnknownException( "Lookup of user '" + name
302 + "' failed! A user with this name does not exist." );
303 }
304 } catch ( SQLException e ) {
305 LOG.logError( e.getMessage(), e );
306 throw new GeneralSecurityException( e );
307 } finally {
308 closeResultSet( rs );
309 closeStatement( pstmt );
310 releaseLocalConnection( securityAccess, con );
311 }
312
313 return user;
314 }
315
316 /**
317 * Retrieves a <code>User</code> from the <code>Registry</code>.
318 *
319 * @param securityAccess
320 * @param id
321 * @throws GeneralSecurityException
322 * this is an <code>UnknownException</code> if the user is not known to the <code>Registry</code>
323 */
324 public User getUserById( SecurityAccess securityAccess, int id )
325 throws GeneralSecurityException {
326 User user = null;
327 Connection con = acquireLocalConnection( securityAccess );
328 PreparedStatement pstmt = null;
329 ResultSet rs = null;
330
331 try {
332 pstmt = con.prepareStatement( "SELECT SEC_SECURABLE_OBJECTS.NAME,"
333 + "SEC_USERS.PASSWORD,SEC_USERS.FIRSTNAME,SEC_USERS.LASTNAME,"
334 + "SEC_USERS.EMAIL FROM SEC_USERS,SEC_SECURABLE_OBJECTS "
335 + "WHERE SEC_SECURABLE_OBJECTS.ID=? AND SEC_USERS.ID=SEC_SECURABLE_OBJECTS.ID" );
336 pstmt.setInt( 1, id );
337 rs = pstmt.executeQuery();
338 if ( rs.next() ) {
339 user = new User( id, rs.getString( 1 ), rs.getString( 2 ), rs.getString( 3 ), rs.getString( 4 ),
340 rs.getString( 5 ), this );
341 } else {
342 throw new UnknownException( "Lookup of user with id: " + id
343 + " failed! A user with this id does not exist." );
344 }
345 } catch ( SQLException e ) {
346 throw new GeneralSecurityException( e );
347 } finally {
348 closeResultSet( rs );
349 closeStatement( pstmt );
350 releaseLocalConnection( securityAccess, con );
351 }
352 return user;
353 }
354
355 /**
356 * Retrieves all <code>User</code> s from the <code>Registry</code>.
357 *
358 * @param securityAccess
359 * @throws GeneralSecurityException
360 */
361 public User[] getAllUsers( SecurityAccess securityAccess )
362 throws GeneralSecurityException {
363 ArrayList<User> users = new ArrayList<User>( 500 );
364 Connection con = acquireLocalConnection( securityAccess );
365 PreparedStatement pstmt = null;
366 ResultSet rs = null;
367
368 try {
369 pstmt = con.prepareStatement( "SELECT SEC_USERS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_USERS.PASSWORD,SEC_USERS.FIRSTNAME,SEC_USERS.LASTNAME,SEC_USERS.EMAIL "
370 + "FROM SEC_USERS,SEC_SECURABLE_OBJECTS "
371 + "WHERE SEC_USERS.ID=SEC_SECURABLE_OBJECTS.ID" );
372 rs = pstmt.executeQuery();
373 while ( rs.next() ) {
374 users.add( new User( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), rs.getString( 4 ),
375 rs.getString( 5 ), rs.getString( 6 ), this ) );
376 }
377 } catch ( SQLException e ) {
378 LOG.logError( e.getMessage(), e );
379 throw new GeneralSecurityException( e );
380 } finally {
381 closeResultSet( rs );
382 closeStatement( pstmt );
383 releaseLocalConnection( securityAccess, con );
384 }
385 return users.toArray( new User[users.size()] );
386 }
387
388 /**
389 * Retrieves all <code>Users</code> s from the <code>Registry</code> that are associated DIRECTLY (i.e. not via
390 * group memberships) with a given <code>Role</code>.
391 *
392 * @param securityAccess
393 * @param role
394 * @throws GeneralSecurityException
395 */
396 public User[] getUsersWithRole( SecurityAccess securityAccess, Role role )
397 throws GeneralSecurityException {
398 ArrayList<User> users = new ArrayList<User>( 500 );
399 Connection con = acquireLocalConnection( securityAccess );
400 PreparedStatement pstmt = null;
401 ResultSet rs = null;
402
403 try {
404 pstmt = con.prepareStatement( "SELECT SEC_USERS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_USERS.PASSWORD,SEC_USERS.FIRSTNAME,SEC_USERS.LASTNAME,"
405 + "SEC_USERS.EMAIL "
406 + "FROM SEC_USERS,SEC_SECURABLE_OBJECTS,SEC_JT_USERS_ROLES "
407 + "WHERE SEC_SECURABLE_OBJECTS.ID=SEC_USERS.ID AND SEC_JT_USERS_ROLES.FK_USERS=SEC_USERS.ID"
408 + " AND SEC_JT_USERS_ROLES.FK_ROLES=?" );
409 pstmt.setInt( 1, role.getID() );
410 rs = pstmt.executeQuery();
411 while ( rs.next() ) {
412 users.add( new User( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), rs.getString( 4 ),
413 rs.getString( 5 ), rs.getString( 6 ), this ) );
414 }
415 } catch ( SQLException e ) {
416 LOG.logError( e.getMessage(), e );
417 throw new GeneralSecurityException( e );
418 } finally {
419 closeResultSet( rs );
420 closeStatement( pstmt );
421 releaseLocalConnection( securityAccess, con );
422 }
423 return users.toArray( new User[users.size()] );
424 }
425
426 /**
427 * Retrieves all <code>User</code> s from the <code>Registry</code> that belong to the given <code>Group</code>
428 * DIRECTLY (i.e. not via inheritance).
429 *
430 * @param securityAccess
431 * @param group
432 * @throws GeneralSecurityException
433 */
434 public User[] getUsersInGroup( SecurityAccess securityAccess, Group group )
435 throws GeneralSecurityException {
436 ArrayList<User> users = new ArrayList<User>( 500 );
437 Connection con = acquireLocalConnection( securityAccess );
438 PreparedStatement pstmt = null;
439 ResultSet rs = null;
440
441 try {
442 pstmt = con.prepareStatement( "SELECT SEC_USERS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_USERS.PASSWORD,SEC_USERS.FIRSTNAME,SEC_USERS.LASTNAME,"
443 + "SEC_USERS.EMAIL "
444 + "FROM SEC_USERS,SEC_SECURABLE_OBJECTS,SEC_JT_USERS_GROUPS "
445 + "WHERE SEC_SECURABLE_OBJECTS.ID=SEC_USERS.ID AND SEC_JT_USERS_GROUPS.FK_USERS=SEC_USERS.ID"
446 + " AND SEC_JT_USERS_GROUPS.FK_GROUPS=?" );
447 pstmt.setInt( 1, group.getID() );
448 rs = pstmt.executeQuery();
449 while ( rs.next() ) {
450 users.add( new User( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), rs.getString( 4 ),
451 rs.getString( 5 ), rs.getString( 6 ), this ) );
452 }
453 } catch ( SQLException e ) {
454 LOG.logError( e.getMessage(), e );
455 throw new GeneralSecurityException( e );
456 } finally {
457 closeResultSet( rs );
458 closeStatement( pstmt );
459 releaseLocalConnection( securityAccess, con );
460 }
461 return users.toArray( new User[users.size()] );
462 }
463
464 /**
465 * Adds a new group account to the <code>Registry</code>.
466 *
467 *
468 * @param transaction
469 * @param name
470 * @throws GeneralSecurityException
471 * this is a <code>DuplicateException</code> if the group already existed
472 */
473 public Group registerGroup( SecurityTransaction transaction, String name, String title )
474 throws GeneralSecurityException {
475 try {
476 getGroupByName( transaction, name );
477 throw new DuplicateException( "Registration of group '" + name + "' failed! A group with "
478 + "this name already exists." );
479 } catch ( UnknownException e ) {
480 // then it's no duplicate
481 }
482
483 Group group = new Group( getID( transaction, "SEC_SECURABLE_OBJECTS" ), name, title, this );
484 PreparedStatement pstmt = null;
485
486 try {
487 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SECURABLE_OBJECTS (ID,NAME,TITLE) VALUES (?,?,?)" );
488 pstmt.setInt( 1, group.getID() );
489 pstmt.setString( 2, group.getName() );
490 pstmt.setString( 3, group.getTitle() );
491 pstmt.executeUpdate();
492 pstmt.close();
493 pstmt = null;
494
495 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_GROUPS (ID) VALUES (?)" );
496 pstmt.setInt( 1, group.getID() );
497 pstmt.executeUpdate();
498 pstmt.close();
499 } catch ( SQLException e ) {
500 LOG.logError( e.getMessage(), e );
501 closeStatement( pstmt );
502 abortTransaction( transaction );
503 throw new GeneralSecurityException( "SQLRegistry.registerGroup() failed. Rollback performed. "
504 + "Error message: " + e.getMessage() );
505 }
506 return group;
507 }
508
509 /**
510 * Removes an existing <code>Group</code> from the <code>Registry</code> (including its relations).
511 *
512 * @param transaction
513 * @param group
514 * @throws GeneralSecurityException
515 */
516 public void deregisterGroup( SecurityTransaction transaction, Group group )
517 throws GeneralSecurityException {
518 PreparedStatement pstmt = null;
519
520 try {
521 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_GROUPS WHERE FK_GROUPS=?" );
522 pstmt.setInt( 1, group.getID() );
523 pstmt.executeUpdate();
524 pstmt.close();
525 pstmt = null;
526 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_GROUPS WHERE FK_GROUPS=? OR FK_GROUPS_MEMBER=?" );
527 pstmt.setInt( 1, group.getID() );
528 pstmt.setInt( 2, group.getID() );
529 pstmt.executeUpdate();
530 pstmt.close();
531 pstmt = null;
532 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_ROLES WHERE FK_GROUPS=?" );
533 pstmt.setInt( 1, group.getID() );
534 pstmt.executeUpdate();
535 pstmt.close();
536 pstmt = null;
537 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_GROUPS WHERE ID=?" );
538 pstmt.setInt( 1, group.getID() );
539 pstmt.executeUpdate();
540 pstmt.close();
541 pstmt = null;
542 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_SECURABLE_OBJECTS=?" );
543 pstmt.setInt( 1, group.getID() );
544 pstmt.executeUpdate();
545 pstmt.close();
546 pstmt = null;
547 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_SECURABLE_OBJECTS WHERE ID=?" );
548 pstmt.setInt( 1, group.getID() );
549 pstmt.executeUpdate();
550 pstmt.close();
551 pstmt = null;
552 } catch ( SQLException e ) {
553 LOG.logError( e.getMessage(), e );
554 closeStatement( pstmt );
555 abortTransaction( transaction );
556 throw new GeneralSecurityException( "SQLRegistry.deregisterGroup() failed. Rollback performed. "
557 + "Error message: " + e.getMessage() );
558 }
559 }
560
561 /**
562 * Retrieves a <code>Group</code> from the <code>Registry</code>.
563 *
564 * @param securityAccess
565 * @param name
566 * @throws GeneralSecurityException
567 * this is an <code>UnknownException</code> if the group is not known to the <code>Registry</code>
568 */
569 public Group getGroupByName( SecurityAccess securityAccess, String name )
570 throws GeneralSecurityException {
571 Group group = null;
572 Connection con = acquireLocalConnection( securityAccess );
573 PreparedStatement pstmt = null;
574 ResultSet rs = null;
575
576 try {
577 pstmt = con.prepareStatement( "SELECT SEC_GROUPS.ID,SEC_SECURABLE_OBJECTS.TITLE "
578 + "FROM SEC_GROUPS,SEC_SECURABLE_OBJECTS "
579 + "WHERE SEC_GROUPS.ID=SEC_SECURABLE_OBJECTS.ID AND "
580 + "SEC_SECURABLE_OBJECTS.NAME=?" );
581 pstmt.setString( 1, name );
582 rs = pstmt.executeQuery();
583 if ( rs.next() ) {
584 group = new Group( rs.getInt( 1 ), name, rs.getString( 2 ), this );
585 } else {
586 throw new UnknownException( "Lookup of group '" + name
587 + "' failed! A group with this name does not exist." );
588 }
589 } catch ( SQLException e ) {
590 LOG.logError( e.getMessage(), e );
591 throw new GeneralSecurityException( e );
592 } finally {
593 closeResultSet( rs );
594 closeStatement( pstmt );
595 releaseLocalConnection( securityAccess, con );
596 }
597 return group;
598 }
599
600 /**
601 * Retrieves a <code>Group</code> from the <code>Registry</code>.
602 *
603 * @param securityAccess
604 * @param id
605 * @throws GeneralSecurityException
606 * this is an <code>UnknownException</code> if the group is not known to the <code>Registry</code>
607 */
608 public Group getGroupById( SecurityAccess securityAccess, int id )
609 throws GeneralSecurityException {
610 Group group = null;
611 Connection con = acquireLocalConnection( securityAccess );
612 PreparedStatement pstmt = null;
613 ResultSet rs = null;
614
615 try {
616 pstmt = con.prepareStatement( "SELECT SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
617 + "FROM SEC_GROUPS,SEC_SECURABLE_OBJECTS "
618 + "WHERE SEC_SECURABLE_OBJECTS.ID=? AND SEC_GROUPS.ID=SEC_SECURABLE_OBJECTS.ID" );
619 pstmt.setInt( 1, id );
620 rs = pstmt.executeQuery();
621 if ( rs.next() ) {
622 group = new Group( id, rs.getString( 1 ), rs.getString( 2 ), this );
623 } else {
624 throw new UnknownException( "Lookup of group with id: " + id
625 + " failed! A group with this id does not exist." );
626 }
627 } catch ( SQLException e ) {
628 LOG.logError( e.getMessage(), e );
629 throw new GeneralSecurityException( e );
630 } finally {
631 closeResultSet( rs );
632 closeStatement( pstmt );
633 releaseLocalConnection( securityAccess, con );
634 }
635 return group;
636 }
637
638 /**
639 * Retrieves all <code>Group</code> s from the <code>Registry</code>.
640 *
641 * @param securityAccess
642 * @throws GeneralSecurityException
643 */
644 public Group[] getAllGroups( SecurityAccess securityAccess )
645 throws GeneralSecurityException {
646 ArrayList<Group> groups = new ArrayList<Group>( 50 );
647 Connection con = acquireLocalConnection( securityAccess );
648 PreparedStatement pstmt = null;
649 ResultSet rs = null;
650
651 try {
652 pstmt = con.prepareStatement( "SELECT SEC_GROUPS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
653 + "FROM SEC_GROUPS,SEC_SECURABLE_OBJECTS "
654 + "WHERE SEC_GROUPS.ID=SEC_SECURABLE_OBJECTS.ID" );
655 rs = pstmt.executeQuery();
656 while ( rs.next() ) {
657 groups.add( new Group( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), this ) );
658 }
659 } catch ( SQLException e ) {
660 LOG.logError( e.getMessage(), e );
661 throw new GeneralSecurityException( e );
662 } finally {
663 closeResultSet( rs );
664 closeStatement( pstmt );
665 releaseLocalConnection( securityAccess, con );
666 }
667 return groups.toArray( new Group[groups.size()] );
668 }
669
670 /**
671 * Adds a new role to the <code>Registry</code>.
672 *
673 * @param transaction
674 * @param name
675 * @throws GeneralSecurityException
676 * this is a <code>DuplicateException</code> if the role already existed
677 */
678 public Role registerRole( SecurityTransaction transaction, String name )
679 throws GeneralSecurityException {
680 try {
681 getRoleByName( transaction, name );
682 throw new DuplicateException( "Registration of role '" + name + "' failed! A role with "
683 + "this name already exists." );
684 } catch ( UnknownException e ) {
685 // then it's no duplicate
686 }
687
688 Role role = new Role( getID( transaction, "SEC_SECURABLE_OBJECTS" ), name, this );
689 PreparedStatement pstmt = null;
690
691 try {
692 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SECURABLE_OBJECTS (ID,NAME,TITLE) VALUES (?,?,?)" );
693 pstmt.setInt( 1, role.getID() );
694 pstmt.setString( 2, role.getName() );
695 pstmt.setString( 3, role.getTitle() );
696 pstmt.executeUpdate();
697 pstmt.close();
698 pstmt = null;
699
700 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_ROLES (ID) VALUES (?)" );
701 pstmt.setInt( 1, role.getID() );
702 pstmt.executeUpdate();
703 pstmt.close();
704 pstmt = null;
705 } catch ( SQLException e ) {
706 LOG.logError( e.getMessage(), e );
707 closeStatement( pstmt );
708 abortTransaction( transaction );
709 throw new GeneralSecurityException( "SQLRegistry.registerRole() failed. Rollback performed. "
710 + "Error message: " + e.getMessage() );
711 }
712 return role;
713 }
714
715 /**
716 * Removes an existing <code>Role</code> from the <code>Registry</code> (including its relations).
717 *
718 * @param transaction
719 * @param role
720 * @throws GeneralSecurityException
721 */
722 public void deregisterRole( SecurityTransaction transaction, Role role )
723 throws GeneralSecurityException {
724 PreparedStatement pstmt = null;
725
726 try {
727 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_ROLES WHERE FK_ROLES=?" );
728 pstmt.setInt( 1, role.getID() );
729 pstmt.executeUpdate();
730 pstmt.close();
731 pstmt = null;
732 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_ROLES WHERE FK_ROLES=?" );
733 pstmt.setInt( 1, role.getID() );
734 pstmt.executeUpdate();
735 pstmt.close();
736 pstmt = null;
737 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_ROLES=? OR FK_SECURABLE_OBJECTS=?" );
738 pstmt.setInt( 1, role.getID() );
739 pstmt.setInt( 2, role.getID() );
740 pstmt.executeUpdate();
741 pstmt.close();
742 pstmt = null;
743 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_ROLES WHERE ID=?" );
744 pstmt.setInt( 1, role.getID() );
745 pstmt.executeUpdate();
746 pstmt.close();
747 pstmt = null;
748 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_SECURABLE_OBJECTS WHERE ID=?" );
749 pstmt.setInt( 1, role.getID() );
750 pstmt.executeUpdate();
751 pstmt.close();
752 pstmt = null;
753 } catch ( SQLException e ) {
754 LOG.logError( e.getMessage(), e );
755 closeStatement( pstmt );
756 abortTransaction( transaction );
757 throw new GeneralSecurityException( "SQLRegistry.deregisterRole() failed. Rollback performed. "
758 + "Error message: " + e.getMessage() );
759 }
760 }
761
762 /**
763 * Retrieves a <code>Role</code> from the <code>Registry</code>.
764 *
765 * @param securityAccess
766 * @param name
767 * @throws GeneralSecurityException
768 * this is an <code>UnknownException</code> if the role is not known to the <code>Registry</code>
769 */
770 public Role getRoleByName( SecurityAccess securityAccess, String name )
771 throws GeneralSecurityException {
772 Role role = null;
773 Connection con = acquireLocalConnection( securityAccess );
774 PreparedStatement pstmt = null;
775 ResultSet rs = null;
776
777 try {
778 pstmt = con.prepareStatement( "SELECT SEC_ROLES.ID " + "FROM SEC_ROLES,SEC_SECURABLE_OBJECTS "
779 + "WHERE SEC_ROLES.ID=SEC_SECURABLE_OBJECTS.ID AND "
780 + "SEC_SECURABLE_OBJECTS.NAME=?" );
781 pstmt.setString( 1, name );
782 rs = pstmt.executeQuery();
783 if ( rs.next() ) {
784 role = new Role( rs.getInt( 1 ), name, this );
785 } else {
786 throw new UnknownException( "Lookup of role '" + name
787 + "' failed! A role with this name does not exist." );
788 }
789 } catch ( SQLException e ) {
790 LOG.logError( e.getMessage(), e );
791 throw new GeneralSecurityException( e );
792 } finally {
793 closeResultSet( rs );
794 closeStatement( pstmt );
795 releaseLocalConnection( securityAccess, con );
796 }
797 return role;
798 }
799
800 /**
801 * Retrieves all <code>Roles</code> s from the <code>Registry</code> that have a certain namespace.
802 *
803 * @param securityAccess
804 * @param ns
805 * null for default namespace
806 * @throws GeneralSecurityException
807 */
808 public Role[] getRolesByNS( SecurityAccess securityAccess, String ns )
809 throws GeneralSecurityException {
810 ArrayList<Role> roles = new ArrayList<Role>( 100 );
811 Connection con = acquireLocalConnection( securityAccess );
812 PreparedStatement pstmt = null;
813 ResultSet rs = null;
814 try {
815 if ( ns != null && ( !ns.equals( "" ) ) ) {
816 pstmt = con.prepareStatement( "SELECT SEC_ROLES.ID,SEC_SECURABLE_OBJECTS.NAME "
817 + "FROM SEC_ROLES,SEC_SECURABLE_OBJECTS "
818 + "WHERE SEC_SECURABLE_OBJECTS.ID=SEC_ROLES.ID AND SEC_SECURABLE_OBJECTS.NAME LIKE ?" );
819 pstmt.setString( 1, ns + ":%" );
820 } else {
821 pstmt = con.prepareStatement( "SELECT SEC_ROLES.ID,SEC_SECURABLE_OBJECTS.NAME "
822 + "FROM SEC_ROLES,SEC_SECURABLE_OBJECTS "
823 + "WHERE SEC_SECURABLE_OBJECTS.ID=SEC_ROLES.ID AND "
824 + "SEC_SECURABLE_OBJECTS.NAME NOT LIKE '%:%'" );
825 }
826
827 rs = pstmt.executeQuery();
828 while ( rs.next() ) {
829 roles.add( new Role( rs.getInt( 1 ), rs.getString( 2 ), this ) );
830 }
831 } catch ( SQLException e ) {
832 LOG.logError( e.getMessage(), e );
833 throw new GeneralSecurityException( e );
834 } finally {
835 closeResultSet( rs );
836 closeStatement( pstmt );
837 releaseLocalConnection( securityAccess, con );
838 }
839 return roles.toArray( new Role[roles.size()] );
840 }
841
842 /**
843 * Retrieves a <code>Role</code> from the <code>Registry</code>.
844 *
845 * @param securityAccess
846 * @param id
847 * @throws GeneralSecurityException
848 * this is an <code>UnknownException</code> if the role is not known to the <code>Registry</code>
849 */
850 public Role getRoleById( SecurityAccess securityAccess, int id )
851 throws GeneralSecurityException {
852 Role role = null;
853 Connection con = acquireLocalConnection( securityAccess );
854 PreparedStatement pstmt = null;
855 ResultSet rs = null;
856
857 try {
858 pstmt = con.prepareStatement( "SELECT SEC_SECURABLE_OBJECTS.NAME "
859 + "FROM SEC_ROLES,SEC_SECURABLE_OBJECTS "
860 + "WHERE SEC_SECURABLE_OBJECTS.ID=? AND SEC_ROLES.ID=SEC_SECURABLE_OBJECTS.ID" );
861 pstmt.setInt( 1, id );
862 rs = pstmt.executeQuery();
863 if ( rs.next() ) {
864 role = new Role( id, rs.getString( 1 ), this );
865 } else {
866 throw new UnknownException( "Lookup of role with id: " + id
867 + " failed! A role with this id does not exist." );
868 }
869 } catch ( SQLException e ) {
870 LOG.logError( e.getMessage(), e );
871 throw new GeneralSecurityException( e );
872 } finally {
873 closeResultSet( rs );
874 closeStatement( pstmt );
875 releaseLocalConnection( securityAccess, con );
876 }
877 return role;
878 }
879
880 /**
881 * Retrieves all <code>Role</code> s from the <code>Registry</code>, except those that are only used internally
882 * (these have namespaces that begin with $).
883 *
884 * @param securityAccess
885 * @throws GeneralSecurityException
886 */
887 public Role[] getAllRoles( SecurityAccess securityAccess )
888 throws GeneralSecurityException {
889 ArrayList<Role> roles = new ArrayList<Role>( 50 );
890 Connection con = acquireLocalConnection( securityAccess );
891 PreparedStatement pstmt = null;
892 ResultSet rs = null;
893
894 try {
895 pstmt = con.prepareStatement( "SELECT SEC_ROLES.ID,SEC_SECURABLE_OBJECTS.NAME "
896 + "FROM SEC_ROLES,SEC_SECURABLE_OBJECTS "
897 + "WHERE SEC_ROLES.ID=SEC_SECURABLE_OBJECTS.ID AND "
898 + "SEC_SECURABLE_OBJECTS.NAME NOT LIKE '$%:%'" );
899 rs = pstmt.executeQuery();
900 while ( rs.next() ) {
901 roles.add( new Role( rs.getInt( 1 ), rs.getString( 2 ), this ) );
902 }
903 } catch ( SQLException e ) {
904 LOG.logError( e.getMessage(), e );
905 throw new GeneralSecurityException( e );
906 } finally {
907 closeResultSet( rs );
908 closeStatement( pstmt );
909 releaseLocalConnection( securityAccess, con );
910 }
911 return roles.toArray( new Role[roles.size()] );
912 }
913
914 /**
915 * Adds a new <code>SecuredObject</code> to the <code>Registry</code>.
916 *
917 * @param transaction
918 * @param type
919 * @param name
920 * @param title
921 * @throws GeneralSecurityException
922 * this is a <code>DuplicateException</code> if the object already existed
923 */
924 public SecuredObject registerSecuredObject( SecurityTransaction transaction, String type, String name, String title )
925 throws GeneralSecurityException {
926 try {
927 getSecuredObjectByName( transaction, name, type );
928 throw new DuplicateException( "Registration of secured object '" + name + "' with type '" + type
929 + "' failed! A secured object with this name and type " + "already exists." );
930 } catch ( UnknownException e ) {
931 // then it's no duplicate
932 }
933
934 PreparedStatement pstmt = null;
935 SecuredObject object = null;
936 ResultSet rs = null;
937
938 try {
939 // check for ID of object type (add type if necessary)
940 int typeId = 0;
941 pstmt = transactionalConnection.prepareStatement( "SELECT ID FROM SEC_SECURED_OBJECT_TYPES WHERE NAME=?" );
942 pstmt.setString( 1, type );
943 rs = pstmt.executeQuery();
944 if ( rs.next() ) {
945 typeId = rs.getInt( 1 );
946 rs.close();
947 rs = null;
948 pstmt.close();
949 pstmt = null;
950 } else {
951 typeId = getID( transaction, "SEC_SECURED_OBJECT_TYPES" );
952 rs.close();
953 rs = null;
954 pstmt.close();
955 pstmt = null;
956 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SECURED_OBJECT_TYPES (ID,NAME) VALUES (?,?)" );
957 pstmt.setInt( 1, typeId );
958 pstmt.setString( 2, type );
959 pstmt.executeUpdate();
960 pstmt.close();
961 pstmt = null;
962 }
963
964 // insert securable object part
965 object = new SecuredObject( getID( transaction, "SEC_SECURABLE_OBJECTS" ), typeId, name, title, this );
966 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SECURABLE_OBJECTS (ID,NAME,TITLE) VALUES (?,?,?)" );
967 pstmt.setInt( 1, object.getID() );
968 pstmt.setString( 2, object.getName() );
969 pstmt.setString( 3, object.getTitle() );
970 pstmt.executeUpdate();
971 pstmt.close();
972 pstmt = null;
973
974 // insert secured object
975 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SECURED_OBJECTS (ID, FK_SECURED_OBJECT_TYPES) VALUES (?,?)" );
976 pstmt.setInt( 1, object.getID() );
977 pstmt.setInt( 2, typeId );
978 pstmt.executeUpdate();
979 pstmt.close();
980 pstmt = null;
981 } catch ( SQLException e ) {
982 LOG.logError( e.getMessage(), e );
983 closeResultSet( rs );
984 closeStatement( pstmt );
985 abortTransaction( transaction );
986 throw new GeneralSecurityException( "SQLRegistry.registerSecuredObject() failed. Rollback performed. "
987 + "Error message: " + e.getMessage() );
988 }
989 return object;
990 }
991
992 /**
993 * Removes an existing <code>SecuredObject</code> from the <code>Registry</code> (including its associations).
994 *
995 * @param transaction
996 * @param object
997 * @throws GeneralSecurityException
998 */
999 public void deregisterSecuredObject( SecurityTransaction transaction, SecuredObject object )
1000 throws GeneralSecurityException {
1001 PreparedStatement pstmt = null;
1002
1003 try {
1004 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_SECURED_OBJECTS WHERE ID=?" );
1005 pstmt.setInt( 1, object.getID() );
1006 pstmt.executeUpdate();
1007 pstmt.close();
1008 pstmt = null;
1009 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_SECURABLE_OBJECTS=?" );
1010 pstmt.setInt( 1, object.getID() );
1011 pstmt.executeUpdate();
1012 pstmt.close();
1013 pstmt = null;
1014 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_SECURABLE_OBJECTS WHERE ID=?" );
1015 pstmt.setInt( 1, object.getID() );
1016 pstmt.executeUpdate();
1017 pstmt = null;
1018 } catch ( SQLException e ) {
1019 LOG.logError( e.getMessage(), e );
1020 closeStatement( pstmt );
1021 abortTransaction( transaction );
1022 throw new GeneralSecurityException( "SQLRegistry.deregisterSecuredObject() failed. Rollback performed. "
1023 + "Error message: " + e.getMessage() );
1024 }
1025 }
1026
1027 /**
1028 * Retrieves a <code>SecuredObject</code> from the <code>Registry</code>.
1029 *
1030 * @param securityAccess
1031 * @param name
1032 * @throws GeneralSecurityException
1033 * this is an <code>UnknownException</code> if the object is not known to the <code>Registry</code>
1034 */
1035 public SecuredObject getSecuredObjectByName( SecurityAccess securityAccess, String name, String type )
1036 throws GeneralSecurityException {
1037 SecuredObject object = null;
1038 Connection con = acquireLocalConnection( securityAccess );
1039 PreparedStatement pstmt = null;
1040 ResultSet rs = null;
1041
1042 try {
1043 pstmt = con.prepareStatement( "SELECT SEC_SECURED_OBJECTS.ID,SEC_SECURED_OBJECT_TYPES.ID, "
1044 + "SEC_SECURABLE_OBJECTS.TITLE "
1045 + "FROM SEC_SECURED_OBJECTS,SEC_SECURED_OBJECT_TYPES,SEC_SECURABLE_OBJECTS "
1046 + "WHERE SEC_SECURED_OBJECTS.FK_SECURED_OBJECT_TYPES=SEC_SECURED_OBJECT_TYPES.ID AND "
1047 + "SEC_SECURED_OBJECTS.ID=SEC_SECURABLE_OBJECTS.ID AND SEC_SECURABLE_OBJECTS.NAME = ? AND "
1048 + "SEC_SECURED_OBJECT_TYPES.NAME=?" );
1049 pstmt.setString( 1, name );
1050 pstmt.setString( 2, type );
1051
1052 if ( LOG.isDebug() ) {
1053 LOG.logDebug( "getSecuredObjectByName", pstmt );
1054 }
1055
1056 rs = pstmt.executeQuery();
1057 if ( rs.next() ) {
1058 object = new SecuredObject( rs.getInt( 1 ), rs.getInt( 2 ), name, rs.getString( 3 ), this );
1059 } else {
1060 throw new UnknownException( "Lookup of secured object '" + name + "' with type '" + type
1061 + "' failed! A secured object with this " + "name and type does not exist." );
1062 }
1063 } catch ( SQLException e ) {
1064 LOG.logError( e.getMessage(), e );
1065 throw new GeneralSecurityException( e );
1066 } finally {
1067 closeResultSet( rs );
1068 closeStatement( pstmt );
1069 releaseLocalConnection( securityAccess, con );
1070 }
1071 return object;
1072 }
1073
1074 /**
1075 * Retrieves a <code>SecuredObject</code> from the <code>Registry</code>.
1076 *
1077 * @param securityAccess
1078 * @param id
1079 * @throws GeneralSecurityException
1080 * this is an <code>UnknownException</code> if the object is not known to the <code>Registry</code>
1081 */
1082 public SecuredObject getSecuredObjectById( SecurityAccess securityAccess, int id )
1083 throws GeneralSecurityException {
1084 SecuredObject object = null;
1085 Connection con = acquireLocalConnection( securityAccess );
1086 PreparedStatement pstmt = null;
1087 ResultSet rs = null;
1088
1089 try {
1090 pstmt = con.prepareStatement( "SELECT SEC_SECURED_OBJECTS.FK_SECURED_OBJECT_TYPES,SEC_SECURABLE_OBJECTS.NAME,"
1091 + "SEC_SECURABLE_OBJECTS.TITLE "
1092 + "FROM SEC_SECURED_OBJECTS,SEC_SECURABLE_OBJECTS "
1093 + "WHERE SEC_SECURED_OBJECTS.ID=SEC_SECURABLE_OBJECTS.ID AND SEC_SECURABLE_OBJECTS.ID=?" );
1094 pstmt.setInt( 1, id );
1095 rs = pstmt.executeQuery();
1096 if ( rs.next() ) {
1097 object = new SecuredObject( id, rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), this );
1098 } else {
1099 throw new UnknownException( "Lookup of secured object with id: " + id
1100 + " failed! A secured object with this id does not exist." );
1101 }
1102 } catch ( SQLException e ) {
1103 LOG.logError( e.getMessage(), e );
1104 throw new GeneralSecurityException( e );
1105 } finally {
1106 closeResultSet( rs );
1107 closeStatement( pstmt );
1108 releaseLocalConnection( securityAccess, con );
1109 }
1110 return object;
1111 }
1112
1113 /**
1114 * Retrieves all <code>SecuredObject</code> s from the <code>Registry</code> that have a certain namespace.
1115 *
1116 * @param securityAccess
1117 * @param ns
1118 * null for default namespace
1119 * @param type
1120 * @throws GeneralSecurityException
1121 */
1122 public SecuredObject[] getSecuredObjectsByNS( SecurityAccess securityAccess, String ns, String type )
1123 throws GeneralSecurityException {
1124 ArrayList<SecuredObject> objects = new ArrayList<SecuredObject>( 100 );
1125 Connection con = acquireLocalConnection( securityAccess );
1126 PreparedStatement pstmt = null;
1127 ResultSet rs = null;
1128 try {
1129 if ( ns != null && ( !ns.equals( "" ) ) ) {
1130 pstmt = con.prepareStatement( "SELECT SEC_SECURED_OBJECTS.ID,SEC_SECURED_OBJECT_TYPES.ID, "
1131 + "SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1132 + "FROM SEC_SECURED_OBJECTS,SEC_SECURED_OBJECT_TYPES,SEC_SECURABLE_OBJECTS "
1133 + "WHERE SEC_SECURED_OBJECTS.FK_SECURED_OBJECT_TYPES=SEC_SECURED_OBJECT_TYPES.ID AND "
1134 + "SEC_SECURABLE_OBJECTS.ID=SEC_SECURED_OBJECTS.ID AND SEC_SECURED_OBJECT_TYPES.NAME=? "
1135 + "AND SEC_SECURABLE_OBJECTS.NAME LIKE ?" );
1136 pstmt.setString( 1, type );
1137 pstmt.setString( 2, ns + ":%" );
1138 } else {
1139 pstmt = con.prepareStatement( "SELECT SEC_SECURED_OBJECTS.ID,SEC_SECURED_OBJECT_TYPES.ID, "
1140 + "SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1141 + "FROM SEC_SECURED_OBJECTS,SEC_SECURED_OBJECT_TYPES,SEC_SECURABLE_OBJECTS "
1142 + "WHERE SEC_SECURED_OBJECTS.FK_SECURED_OBJECT_TYPES=SEC_SECURED_OBJECT_TYPES.ID AND "
1143 + "SEC_SECURABLE_OBJECTS.ID=SEC_SECURED_OBJECTS.ID AND SEC_SECURED_OBJECT_TYPES.NAME=? "
1144 + "AND SEC_SECURABLE_OBJECTS.NAME NOT LIKE '%:%'" );
1145 pstmt.setString( 1, type );
1146 }
1147 rs = pstmt.executeQuery();
1148 while ( rs.next() ) {
1149 objects.add( new SecuredObject( rs.getInt( 1 ), rs.getInt( 2 ), rs.getString( 3 ), rs.getString( 4 ),
1150 this ) );
1151 }
1152 } catch ( SQLException e ) {
1153 LOG.logError( e.getMessage(), e );
1154 throw new GeneralSecurityException( e );
1155 } finally {
1156 closeResultSet( rs );
1157 closeStatement( pstmt );
1158 releaseLocalConnection( securityAccess, con );
1159 }
1160 return objects.toArray( new SecuredObject[objects.size()] );
1161 }
1162
1163 /**
1164 * Retrieves all <code>SecuredObject</code> s with the given type from the <code>Registry</code>.
1165 *
1166 * @param securityAccess
1167 * @param type
1168 * @throws GeneralSecurityException
1169 */
1170 public SecuredObject[] getAllSecuredObjects( SecurityAccess securityAccess, String type )
1171 throws GeneralSecurityException {
1172 ArrayList<SecuredObject> objects = new ArrayList<SecuredObject>( 100 );
1173 Connection con = acquireLocalConnection( securityAccess );
1174 PreparedStatement pstmt = null;
1175 ResultSet rs = null;
1176
1177 try {
1178 pstmt = con.prepareStatement( "SELECT SEC_SECURED_OBJECTS.ID,SEC_SECURED_OBJECT_TYPES.ID, "
1179 + "SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1180 + "FROM SEC_SECURED_OBJECTS,SEC_SECURED_OBJECT_TYPES,SEC_SECURABLE_OBJECTS "
1181 + "WHERE SEC_SECURED_OBJECTS.FK_SECURED_OBJECT_TYPES=SEC_SECURED_OBJECT_TYPES.ID AND "
1182 + "SEC_SECURABLE_OBJECTS.ID=SEC_SECURED_OBJECTS.ID AND SEC_SECURED_OBJECT_TYPES.NAME=?" );
1183 pstmt.setString( 1, type );
1184 rs = pstmt.executeQuery();
1185 while ( rs.next() ) {
1186 objects.add( new SecuredObject( rs.getInt( 1 ), rs.getInt( 2 ), rs.getString( 3 ), rs.getString( 4 ),
1187 this ) );
1188 }
1189 } catch ( SQLException e ) {
1190 LOG.logError( e.getMessage(), e );
1191 throw new GeneralSecurityException( e );
1192 } finally {
1193 closeResultSet( rs );
1194 closeStatement( pstmt );
1195 releaseLocalConnection( securityAccess, con );
1196 }
1197 return objects.toArray( new SecuredObject[objects.size()] );
1198 }
1199
1200 /**
1201 * Adds a new <code>Privilege</code> to the <code>Registry</code>.
1202 *
1203 * @param transaction
1204 * @param name
1205 * @throws GeneralSecurityException
1206 * this is a <code>DuplicateException</code> if the <code>Privilege</code> already existed
1207 */
1208 public Privilege registerPrivilege( SecurityTransaction transaction, String name )
1209 throws GeneralSecurityException {
1210 try {
1211 getPrivilegeByName( transaction, name );
1212 throw new DuplicateException( "Registration of privilege '" + name + "' failed! A privilege with "
1213 + "this name already exists." );
1214 } catch ( UnknownException e ) {
1215 // then it's no duplicate
1216 }
1217
1218 int id = getID( transaction, "SEC_PRIVILEGES" );
1219 Privilege privilege = new Privilege( id, name );
1220 PreparedStatement pstmt = null;
1221
1222 try {
1223 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_PRIVILEGES (ID, NAME) VALUES (?,?)" );
1224 pstmt.setInt( 1, id );
1225 pstmt.setString( 2, name );
1226 pstmt.executeUpdate();
1227 pstmt.close();
1228 } catch ( SQLException e ) {
1229 LOG.logError( e.getMessage(), e );
1230 closeStatement( pstmt );
1231 abortTransaction( transaction );
1232 throw new GeneralSecurityException( "SQLRegistry.registerPrivilege() failed. Rollback performed. "
1233 + "Error message: " + e.getMessage() );
1234 }
1235 return privilege;
1236 }
1237
1238 /**
1239 * Removes an existing</code> Privilege</code> from the <code>Registry </code> (including its relations).
1240 *
1241 * @param transaction
1242 * @param privilege
1243 * @throws GeneralSecurityException
1244 */
1245 public void deregisterPrivilege( SecurityTransaction transaction, Privilege privilege )
1246 throws GeneralSecurityException {
1247 PreparedStatement pstmt = null;
1248
1249 try {
1250 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_PRIVILEGES WHERE FK_PRIVILEGES=?" );
1251 pstmt.setInt( 1, privilege.getID() );
1252 pstmt.executeUpdate();
1253 pstmt.close();
1254 pstmt = null;
1255 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_PRIVILEGES WHERE ID=?" );
1256 pstmt.setInt( 1, privilege.getID() );
1257 pstmt.executeUpdate();
1258 pstmt.close();
1259 } catch ( SQLException e ) {
1260 LOG.logError( e.getMessage(), e );
1261 closeStatement( pstmt );
1262 abortTransaction( transaction );
1263 throw new GeneralSecurityException( "SQLRegistry.deregisterPrivilege() failed. Rollback performed. "
1264 + "Error message: " + e.getMessage() );
1265 }
1266 }
1267
1268 /**
1269 * Retrieves a <code>Privilege</code> from the <code>Registry</code>.
1270 *
1271 * @param securityAccess
1272 * @param name
1273 * @throws GeneralSecurityException
1274 * this is an <code>UnknownException</code> if the privilege is not known to the <code>Registry</code>
1275 */
1276 public Privilege getPrivilegeByName( SecurityAccess securityAccess, String name )
1277 throws GeneralSecurityException {
1278 Privilege privilege = null;
1279 Connection con = acquireLocalConnection( securityAccess );
1280 PreparedStatement pstmt = null;
1281 ResultSet rs = null;
1282
1283 try {
1284 pstmt = con.prepareStatement( "SELECT ID FROM SEC_PRIVILEGES WHERE NAME=?" );
1285 pstmt.setString( 1, name );
1286 rs = pstmt.executeQuery();
1287 if ( rs.next() ) {
1288 privilege = new Privilege( rs.getInt( 1 ), name );
1289 } else {
1290 throw new UnknownException( "Lookup of privilege '" + name
1291 + "' failed! A privilege with this name does not exist." );
1292 }
1293 } catch ( SQLException e ) {
1294 LOG.logError( e.getMessage(), e );
1295 throw new GeneralSecurityException( e );
1296 } finally {
1297 closeResultSet( rs );
1298 closeStatement( pstmt );
1299 releaseLocalConnection( securityAccess, con );
1300 }
1301 return privilege;
1302 }
1303
1304 /**
1305 * Retrieves all <code>Privileges</code> s from the <code>Registry</code> that are associated DIRECTLY (i.e. not via
1306 * group memberships) with a given <code>Role</code>.
1307 *
1308 * @param securityAccess
1309 * @param role
1310 * @throws GeneralSecurityException
1311 */
1312 public Privilege[] getPrivilegesForRole( SecurityAccess securityAccess, Role role )
1313 throws GeneralSecurityException {
1314 ArrayList<Privilege> privileges = new ArrayList<Privilege>();
1315 Connection con = acquireLocalConnection( securityAccess );
1316 PreparedStatement pstmt = null;
1317 ResultSet rs = null;
1318
1319 try {
1320 pstmt = con.prepareStatement( "SELECT SEC_PRIVILEGES.ID,SEC_PRIVILEGES.NAME "
1321 + "FROM SEC_JT_ROLES_PRIVILEGES, SEC_PRIVILEGES WHERE "
1322 + "SEC_JT_ROLES_PRIVILEGES.FK_ROLES=? AND "
1323 + "SEC_JT_ROLES_PRIVILEGES.FK_PRIVILEGES=SEC_PRIVILEGES.ID" );
1324 pstmt.setInt( 1, role.getID() );
1325 rs = pstmt.executeQuery();
1326 while ( rs.next() ) {
1327 privileges.add( new Privilege( rs.getInt( 1 ), rs.getString( 2 ) ) );
1328 }
1329 } catch ( SQLException e ) {
1330 LOG.logError( e.getMessage(), e );
1331 throw new GeneralSecurityException( e );
1332 } finally {
1333 closeResultSet( rs );
1334 closeStatement( pstmt );
1335 releaseLocalConnection( securityAccess, con );
1336 }
1337 return privileges.toArray( new Privilege[privileges.size()] );
1338 }
1339
1340 /**
1341 * Sets all <code>Privilege</code> s that are associated with a given <code>Role</code>.
1342 *
1343 * @param transaction
1344 * @param role
1345 * @param privileges
1346 * @throws GeneralSecurityException
1347 */
1348 public void setPrivilegesForRole( SecurityTransaction transaction, Role role, Privilege[] privileges )
1349 throws GeneralSecurityException {
1350 PreparedStatement pstmt = null;
1351
1352 try {
1353 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_PRIVILEGES WHERE FK_ROLES=?" );
1354 pstmt.setInt( 1, role.getID() );
1355 pstmt.executeUpdate();
1356 pstmt.close();
1357 pstmt = null;
1358
1359 for ( int i = 0; i < privileges.length; i++ ) {
1360 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_ROLES_PRIVILEGES (FK_ROLES, FK_PRIVILEGES) VALUES (?,?)" );
1361 pstmt.setInt( 1, role.getID() );
1362 pstmt.setInt( 2, privileges[i].getID() );
1363 pstmt.executeUpdate();
1364 pstmt.close();
1365 pstmt = null;
1366 }
1367 } catch ( SQLException e ) {
1368 LOG.logError( e.getMessage(), e );
1369 closeStatement( pstmt );
1370 abortTransaction( transaction );
1371 throw new GeneralSecurityException( "SQLRegistry.setPrivilegesForRols() failed. Rollback performed. "
1372 + "Error message: " + e.getMessage() );
1373 }
1374 }
1375
1376 /**
1377 * Adds a new <code>Right</code> to the <code>Registry</code>.
1378 *
1379 * @param transaction
1380 * @param name
1381 * @throws GeneralSecurityException
1382 * this is a <code>DuplicateException</code> if the <code>Right</code> already existed
1383 */
1384 public RightType registerRightType( SecurityTransaction transaction, String name )
1385 throws GeneralSecurityException {
1386 try {
1387 getRightTypeByName( transaction, name );
1388 throw new DuplicateException( "Registration of right '" + name + "' failed! A right with "
1389 + "this name already exists." );
1390 } catch ( UnknownException e ) {
1391 // then it's no duplicate
1392 }
1393
1394 int id = getID( transaction, "SEC_RIGHTS" );
1395 RightType right = new RightType( id, name );
1396 PreparedStatement pstmt = null;
1397
1398 try {
1399 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_RIGHTS (ID, NAME) VALUES (?,?)" );
1400 pstmt.setInt( 1, id );
1401 pstmt.setString( 2, name );
1402 pstmt.executeUpdate();
1403 pstmt.close();
1404 } catch ( SQLException e ) {
1405 LOG.logError( e.getMessage(), e );
1406 closeStatement( pstmt );
1407 abortTransaction( transaction );
1408 throw new GeneralSecurityException( "SQLRegistry.registerRight() failed. Rollback "
1409 + "performed. Error message: " + e.getMessage() );
1410 }
1411 return right;
1412 }
1413
1414 /**
1415 * Removes an existing <code>RightType</code> from the <code>Registry</code> (including its relations).
1416 *
1417 * @param transaction
1418 * @param type
1419 * @throws GeneralSecurityException
1420 */
1421 public void deregisterRightType( SecurityTransaction transaction, RightType type )
1422 throws GeneralSecurityException {
1423 PreparedStatement pstmt = null;
1424
1425 try {
1426 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_RIGHTS=?" );
1427 pstmt.setInt( 1, type.getID() );
1428 pstmt.executeUpdate();
1429 pstmt.close();
1430 pstmt = null;
1431 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_RIGHTS WHERE ID=?" );
1432 pstmt.setInt( 1, type.getID() );
1433 pstmt.executeUpdate();
1434 pstmt.close();
1435 } catch ( SQLException e ) {
1436 LOG.logError( e.getMessage(), e );
1437 closeStatement( pstmt );
1438 abortTransaction( transaction );
1439 throw new GeneralSecurityException( "SQLRegistry.deregisterRight() failed. Rollback performed. "
1440 + "Error message: " + e.getMessage() );
1441 }
1442 }
1443
1444 /**
1445 * Retrieves a <code>Right</code> from the <code>Registry</code>.
1446 *
1447 * @param securityAccess
1448 * @param name
1449 * @throws GeneralSecurityException
1450 * this is an <code>UnknownException</code> if the <code>Right</code> is not known to the
1451 * <code>Registry</code>
1452 */
1453 public RightType getRightTypeByName( SecurityAccess securityAccess, String name )
1454 throws GeneralSecurityException {
1455 RightType right = null;
1456 Connection con = acquireLocalConnection( securityAccess );
1457 PreparedStatement pstmt = null;
1458 ResultSet rs = null;
1459
1460 try {
1461 pstmt = con.prepareStatement( "SELECT ID FROM SEC_RIGHTS WHERE NAME=?" );
1462 pstmt.setString( 1, name );
1463 rs = pstmt.executeQuery();
1464 if ( rs.next() ) {
1465 right = new RightType( rs.getInt( 1 ), name );
1466 } else {
1467 throw new UnknownException( "Lookup of right '" + name
1468 + "' failed! A right with this name does not exist." );
1469 }
1470 } catch ( SQLException e ) {
1471 LOG.logError( e.getMessage(), e );
1472 throw new GeneralSecurityException( e );
1473 } finally {
1474 closeResultSet( rs );
1475 closeStatement( pstmt );
1476 releaseLocalConnection( securityAccess, con );
1477 }
1478 return right;
1479 }
1480
1481 /**
1482 * Retrieves the <code>Rights</code> from the <code>Registry</code> that are associated with a given
1483 * <code>Role</code> and a <code>SecurableObject</code>.
1484 *
1485 * @param securityAccess
1486 * @param object
1487 * @param role
1488 * @throws GeneralSecurityException
1489 */
1490 public Right[] getRights( SecurityAccess securityAccess, SecurableObject object, Role role )
1491 throws GeneralSecurityException {
1492 ArrayList<Right> rights = new ArrayList<Right>();
1493 Connection con = acquireLocalConnection( securityAccess );
1494 PreparedStatement pstmt = null;
1495 ResultSet rs = null;
1496
1497 try {
1498 pstmt = con.prepareStatement( "SELECT SEC_RIGHTS.ID,SEC_RIGHTS.NAME,"
1499 + "SEC_JT_ROLES_SECOBJECTS.CONSTRAINTS FROM SEC_JT_ROLES_SECOBJECTS,"
1500 + "SEC_RIGHTS WHERE SEC_JT_ROLES_SECOBJECTS.FK_ROLES=? AND "
1501 + "SEC_JT_ROLES_SECOBJECTS.FK_SECURABLE_OBJECTS=? AND "
1502 + "SEC_JT_ROLES_SECOBJECTS.FK_RIGHTS=SEC_RIGHTS.ID" );
1503 pstmt.setInt( 1, role.getID() );
1504 pstmt.setInt( 2, object.getID() );
1505 rs = pstmt.executeQuery();
1506 ResultSetMetaData metadata = rs.getMetaData();
1507 int constraintType = metadata.getColumnType( 3 );
1508
1509 while ( rs.next() ) {
1510 Right right = null;
1511 RightType type = new RightType( rs.getInt( 1 ), rs.getString( 2 ) );
1512 String constraints = null;
1513 Object o = rs.getObject( 3 );
1514 if ( o != null ) {
1515 if ( constraintType == Types.CLOB ) {
1516 Reader reader = ( (Clob) o ).getCharacterStream();
1517 StringBuffer sb = new StringBuffer( 2000 );
1518 int c;
1519 try {
1520 while ( ( c = reader.read() ) > -1 ) {
1521 sb.append( (char) c );
1522 }
1523 reader.close();
1524 } catch ( IOException e ) {
1525 throw new GeneralSecurityException( "Error converting CLOB to constraint string: "
1526 + e.getMessage() );
1527 }
1528 constraints = sb.toString();
1529 } else {
1530 constraints = o.toString();
1531 }
1532 }
1533
1534 // check if the right has constraints
1535 if ( constraints != null && constraints.length() > 3 ) {
1536 right = new Right( object, type, buildFilter( constraints ) );
1537 } else {
1538 right = new Right( object, type, null );
1539 }
1540
1541 rights.add( right );
1542 }
1543 } catch ( SQLException e ) {
1544 LOG.logError( e.getMessage(), e );
1545 throw new GeneralSecurityException( e );
1546 } finally {
1547 closeResultSet( rs );
1548 closeStatement( pstmt );
1549 releaseLocalConnection( securityAccess, con );
1550 }
1551 return rights.toArray( new Right[rights.size()] );
1552 }
1553
1554 /**
1555 * Sets the <code>Rights</code> to be associated with a given <code>Role</code> and <code>SecurableObject</code>.
1556 *
1557 * @param transaction
1558 * @param object
1559 * @param role
1560 * @param rights
1561 * @throws GeneralSecurityException
1562 */
1563 public void setRights( SecurityTransaction transaction, SecurableObject object, Role role, Right[] rights )
1564 throws GeneralSecurityException {
1565 PreparedStatement pstmt = null;
1566
1567 try {
1568 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_ROLES=? AND FK_SECURABLE_OBJECTS=?" );
1569 pstmt.setInt( 1, role.getID() );
1570 pstmt.setInt( 2, object.getID() );
1571 pstmt.executeUpdate();
1572 pstmt.close();
1573
1574 for ( int i = 0; i < rights.length; i++ ) {
1575
1576 String constraints = null;
1577 if ( rights[i].getConstraints() != null ) {
1578 constraints = rights[i].getConstraints().to110XML().toString();
1579 }
1580 LOG.logDebug( "constraints to add: ", constraints );
1581 if ( transactionalConnection.getClass().getCanonicalName().equals( "oracle.jdbc.OracleConnection" ) ) {
1582 /* transactionalConnection instanceof OracleConnection */
1583 handleOracle( object, role, rights[i], constraints );
1584 } else {
1585 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_ROLES_SECOBJECTS (FK_ROLES, FK_SECURABLE_OBJECTS, FK_RIGHTS,CONSTRAINTS) VALUES (?,?,?,?)" );
1586 pstmt.setInt( 1, role.getID() );
1587 pstmt.setInt( 2, object.getID() );
1588 pstmt.setInt( 3, rights[i].getType().getID() );
1589 pstmt.setString( 4, constraints );
1590 pstmt.executeUpdate();
1591 pstmt.close();
1592 }
1593
1594 }
1595 } catch ( SQLException e ) {
1596 LOG.logError( e.getMessage(), e );
1597 closeStatement( pstmt );
1598 abortTransaction( transaction );
1599 throw new GeneralSecurityException( "SQLRegistry.setRights() failed. Rollback performed. "
1600 + "Error message: " + e.getMessage() );
1601 }
1602 }
1603
1604 private void handleOracle( SecurableObject object, Role role, Right right, String constraints )
1605 throws SQLException {
1606
1607 PreparedStatement pstmt;
1608 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_ROLES_SECOBJECTS (FK_ROLES, FK_SECURABLE_OBJECTS, FK_RIGHTS, CONSTRAINTS) VALUES (?,?,?, EMPTY_CLOB() )" );
1609 pstmt.setInt( 1, role.getID() );
1610 pstmt.setInt( 2, object.getID() );
1611 pstmt.setInt( 3, right.getType().getID() );
1612 pstmt.executeUpdate();
1613 pstmt.close();
1614 transactionalConnection.commit();
1615
1616 if ( constraints != null ) {
1617 pstmt = transactionalConnection.prepareStatement( "select CONSTRAINTS from SEC_JT_ROLES_SECOBJECTS where FK_ROLES = ? and FK_SECURABLE_OBJECTS = ? and FK_RIGHTS = ? FOR UPDATE" );
1618 pstmt.setInt( 1, role.getID() );
1619 pstmt.setInt( 2, object.getID() );
1620 pstmt.setInt( 3, right.getType().getID() );
1621 ResultSet rs = pstmt.executeQuery();
1622 rs.next();
1623
1624 Clob clob = rs.getClob( 1 );
1625 try {
1626 Writer writer = clob.setCharacterStream( 0 );
1627 // use that output stream to write character data to the Oracle data store
1628 writer.write( constraints.toCharArray() );
1629 // write data and commit
1630 writer.flush();
1631 writer.close();
1632 } catch ( IOException e ) {
1633 e.printStackTrace();
1634 }
1635 }
1636 pstmt.close();
1637 }
1638
1639 /**
1640 * Sets one <code>Right</code> to be associated with a given <code>Role</code> and all given
1641 * <code>SecurableObjects</code>.
1642 *
1643 * @param transaction
1644 * @param objects
1645 * @param role
1646 * @param right
1647 * @throws GeneralSecurityException
1648 */
1649 public void setRights( SecurityTransaction transaction, SecurableObject[] objects, Role role, Right right )
1650 throws GeneralSecurityException {
1651 PreparedStatement pstmt = null;
1652
1653 try {
1654 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_ROLES_SECOBJECTS WHERE FK_ROLES=? AND FK_RIGHTS=?" );
1655 pstmt.setInt( 1, role.getID() );
1656 pstmt.setInt( 2, right.getType().getID() );
1657 pstmt.executeUpdate();
1658 pstmt.close();
1659 pstmt = null;
1660
1661 for ( int i = 0; i < objects.length; i++ ) {
1662 String constraints = null;
1663 if ( right.getConstraints() != null ) {
1664 constraints = right.getConstraints().to110XML().toString();
1665 }
1666 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_ROLES_SECOBJECTS (FK_ROLES, FK_SECURABLE_OBJECTS, FK_RIGHTS, CONSTRAINTS) VALUES (?,?,?,?)" );
1667 pstmt.setInt( 1, role.getID() );
1668 pstmt.setInt( 2, objects[i].getID() );
1669 pstmt.setInt( 3, right.getType().getID() );
1670 pstmt.setString( 4, constraints );
1671 pstmt.executeUpdate();
1672 pstmt.close();
1673 pstmt = null;
1674 }
1675 } catch ( SQLException e ) {
1676 closeStatement( pstmt );
1677 abortTransaction( transaction );
1678 throw new GeneralSecurityException( "SQLRegistry.setRights() failed. Rollback performed. "
1679 + "Error message: " + e.getMessage() );
1680 }
1681 }
1682
1683 /**
1684 * Retrieves all <code>Group</code> s from the <code>Registry</code> that the given <code>User</code> is a DIRECT
1685 * (i.e. not via inheritance) member of.
1686 *
1687 * @param securityAccess
1688 * @param user
1689 * @throws GeneralSecurityException
1690 */
1691 public Group[] getGroupsForUser( SecurityAccess securityAccess, User user )
1692 throws GeneralSecurityException {
1693 ArrayList<Group> groups = new ArrayList<Group>( 100 );
1694 Connection con = acquireLocalConnection( securityAccess );
1695 PreparedStatement pstmt = null;
1696 ResultSet rs = null;
1697
1698 try {
1699 pstmt = con.prepareStatement( "SELECT SEC_GROUPS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1700 + "FROM SEC_SECURABLE_OBJECTS,SEC_GROUPS,SEC_JT_USERS_GROUPS WHERE "
1701 + "SEC_SECURABLE_OBJECTS.ID=SEC_GROUPS.ID AND "
1702 + "SEC_JT_USERS_GROUPS.FK_GROUPS=SEC_GROUPS.ID AND "
1703 + "SEC_JT_USERS_GROUPS.FK_USERS=?" );
1704 pstmt.setInt( 1, user.getID() );
1705 rs = pstmt.executeQuery();
1706 while ( rs.next() ) {
1707 groups.add( new Group( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), this ) );
1708 }
1709 } catch ( SQLException e ) {
1710 throw new GeneralSecurityException( e );
1711 } finally {
1712 closeResultSet( rs );
1713 closeStatement( pstmt );
1714 releaseLocalConnection( securityAccess, con );
1715 }
1716 return groups.toArray( new Group[groups.size()] );
1717 }
1718
1719 /**
1720 * Retrieves all <code>Groups</code> s from the <code>Registry</code> that are members of another <code>Group</code>
1721 * DIRECTLY (i.e. not via inheritance).
1722 *
1723 * @param securityAccess
1724 * @param group
1725 * @throws GeneralSecurityException
1726 */
1727 public Group[] getGroupsInGroup( SecurityAccess securityAccess, Group group )
1728 throws GeneralSecurityException {
1729 ArrayList<Group> groups = new ArrayList<Group>( 100 );
1730 Connection con = acquireLocalConnection( securityAccess );
1731 PreparedStatement pstmt = null;
1732 ResultSet rs = null;
1733
1734 try {
1735 pstmt = con.prepareStatement( "SELECT SEC_GROUPS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1736 + "FROM SEC_GROUPS,SEC_SECURABLE_OBJECTS,SEC_JT_GROUPS_GROUPS "
1737 + "WHERE SEC_SECURABLE_OBJECTS.ID=SEC_GROUPS.ID"
1738 + " AND SEC_JT_GROUPS_GROUPS.FK_GROUPS_MEMBER=SEC_GROUPS.ID"
1739 + " AND SEC_JT_GROUPS_GROUPS.FK_GROUPS=?" );
1740 pstmt.setInt( 1, group.getID() );
1741 rs = pstmt.executeQuery();
1742 while ( rs.next() ) {
1743 groups.add( new Group( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), this ) );
1744 }
1745 } catch ( SQLException e ) {
1746 throw new GeneralSecurityException( e );
1747 } finally {
1748 closeResultSet( rs );
1749 closeStatement( pstmt );
1750 releaseLocalConnection( securityAccess, con );
1751 }
1752 return groups.toArray( new Group[groups.size()] );
1753 }
1754
1755 /**
1756 * Retrieves all <code>Group</code> s from the <code>Registry</code> that the given <code>Group</code> is a DIRECT
1757 * member (i.e. not via inheritance) of.
1758 *
1759 * @param securityAccess
1760 * @param group
1761 * @throws GeneralSecurityException
1762 */
1763 public Group[] getGroupsForGroup( SecurityAccess securityAccess, Group group )
1764 throws GeneralSecurityException {
1765 ArrayList<Group> groups = new ArrayList<Group>( 100 );
1766 Connection con = acquireLocalConnection( securityAccess );
1767 PreparedStatement pstmt = null;
1768 ResultSet rs = null;
1769
1770 try {
1771 pstmt = con.prepareStatement( "SELECT SEC_GROUPS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1772 + "FROM SEC_SECURABLE_OBJECTS,SEC_GROUPS,SEC_JT_GROUPS_GROUPS WHERE "
1773 + "SEC_SECURABLE_OBJECTS.ID=SEC_GROUPS.ID AND "
1774 + "SEC_JT_GROUPS_GROUPS.FK_GROUPS=SEC_GROUPS.ID AND "
1775 + "SEC_JT_GROUPS_GROUPS.FK_GROUPS_MEMBER=?" );
1776 pstmt.setInt( 1, group.getID() );
1777 rs = pstmt.executeQuery();
1778 while ( rs.next() ) {
1779 groups.add( new Group( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), this ) );
1780 }
1781 } catch ( SQLException e ) {
1782 throw new GeneralSecurityException( e );
1783 } finally {
1784 closeResultSet( rs );
1785 closeStatement( pstmt );
1786 releaseLocalConnection( securityAccess, con );
1787 }
1788 return groups.toArray( new Group[groups.size()] );
1789 }
1790
1791 /**
1792 * Retrieves all <code>Group</code> s from the <code>Registry</code> that are associated with a given
1793 * <code>Role</code> DIRECTLY (i.e. not via inheritance).
1794 *
1795 * @param securityAccess
1796 * @param role
1797 * @throws GeneralSecurityException
1798 */
1799 public Group[] getGroupsWithRole( SecurityAccess securityAccess, Role role )
1800 throws GeneralSecurityException {
1801 ArrayList<Group> groups = new ArrayList<Group>( 100 );
1802 Connection con = acquireLocalConnection( securityAccess );
1803 PreparedStatement pstmt = null;
1804 ResultSet rs = null;
1805
1806 try {
1807 pstmt = con.prepareStatement( "SELECT SEC_GROUPS.ID,SEC_SECURABLE_OBJECTS.NAME,SEC_SECURABLE_OBJECTS.TITLE "
1808 + "FROM SEC_SECURABLE_OBJECTS,SEC_GROUPS,SEC_JT_GROUPS_ROLES WHERE "
1809 + "SEC_SECURABLE_OBJECTS.ID=SEC_GROUPS.ID AND "
1810 + "SEC_JT_GROUPS_ROLES.FK_GROUPS=SEC_GROUPS.ID AND "
1811 + "SEC_JT_GROUPS_ROLES.FK_ROLES=?" );
1812 pstmt.setInt( 1, role.getID() );
1813 rs = pstmt.executeQuery();
1814 while ( rs.next() ) {
1815 groups.add( new Group( rs.getInt( 1 ), rs.getString( 2 ), rs.getString( 3 ), this ) );
1816 }
1817 } catch ( SQLException e ) {
1818 throw new GeneralSecurityException( e );
1819 } finally {
1820 closeResultSet( rs );
1821 closeStatement( pstmt );
1822 releaseLocalConnection( securityAccess, con );
1823 }
1824 return groups.toArray( new Group[groups.size()] );
1825 }
1826
1827 /**
1828 * Retrieves all <code>Role</code> s from the <code>Registry</code> that are associated with a given
1829 * <code>User</code> DIRECTLY (i.e. not via group memberships).
1830 *
1831 * @param securityAccess
1832 * @param user
1833 * @throws GeneralSecurityException
1834 */
1835 public Role[] getRolesForUser( SecurityAccess securityAccess, User user )
1836 throws GeneralSecurityException {
1837 ArrayList<Role> roles = new ArrayList<Role>( 100 );
1838 Connection con = acquireLocalConnection( securityAccess );
1839 PreparedStatement pstmt = null;
1840 ResultSet rs = null;
1841
1842 try {
1843 pstmt = con.prepareStatement( "SELECT SEC_ROLES.ID,SEC_SECURABLE_OBJECTS.NAME "
1844 + "FROM SEC_SECURABLE_OBJECTS,SEC_ROLES,SEC_JT_USERS_ROLES WHERE "
1845 + "SEC_SECURABLE_OBJECTS.ID=SEC_ROLES.ID AND SEC_JT_USERS_ROLES.FK_ROLES=SEC_ROLES.ID "
1846 + "AND SEC_JT_USERS_ROLES.FK_USERS=?" );
1847 pstmt.setInt( 1, user.getID() );
1848 rs = pstmt.executeQuery();
1849 while ( rs.next() ) {
1850 roles.add( new Role( rs.getInt( 1 ), rs.getString( 2 ), this ) );
1851 }
1852 } catch ( SQLException e ) {
1853 throw new GeneralSecurityException( e );
1854 } finally {
1855 closeResultSet( rs );
1856 closeStatement( pstmt );
1857 releaseLocalConnection( securityAccess, con );
1858 }
1859 return roles.toArray( new Role[roles.size()] );
1860 }
1861
1862 /**
1863 * Retrieves all <code>Role</code> s from the <code>Registry</code> that are associated with a given
1864 * <code>Group</code> DIRECTLY (i.e. not via inheritance).
1865 *
1866 * @param securityAccess
1867 * @param group
1868 * @throws GeneralSecurityException
1869 */
1870 public Role[] getRolesForGroup( SecurityAccess securityAccess, Group group )
1871 throws GeneralSecurityException {
1872 ArrayList<Role> roles = new ArrayList<Role>( 100 );
1873 Connection con = acquireLocalConnection( securityAccess );
1874 PreparedStatement pstmt = null;
1875 ResultSet rs = null;
1876
1877 try {
1878 pstmt = con.prepareStatement( "SELECT SEC_ROLES.ID,SEC_SECURABLE_OBJECTS.NAME "
1879 + "FROM SEC_SECURABLE_OBJECTS,SEC_ROLES,SEC_JT_GROUPS_ROLES WHERE "
1880 + "SEC_SECURABLE_OBJECTS.ID=SEC_ROLES.ID AND SEC_JT_GROUPS_ROLES.FK_ROLES=SEC_ROLES.ID "
1881 + "AND SEC_JT_GROUPS_ROLES.FK_GROUPS=?" );
1882 pstmt.setInt( 1, group.getID() );
1883 rs = pstmt.executeQuery();
1884 while ( rs.next() ) {
1885 roles.add( new Role( rs.getInt( 1 ), rs.getString( 2 ), this ) );
1886 }
1887 } catch ( SQLException e ) {
1888 throw new GeneralSecurityException( e );
1889 } finally {
1890 closeResultSet( rs );
1891 closeStatement( pstmt );
1892 releaseLocalConnection( securityAccess, con );
1893 }
1894 return roles.toArray( new Role[roles.size()] );
1895 }
1896
1897 /**
1898 * Sets the <code>Group</code> s that a given <code>User</code> is member of DIRECTLY (i.e. not via inheritance).
1899 *
1900 * @param transaction
1901 * @param user
1902 * @param groups
1903 * @throws GeneralSecurityException
1904 */
1905 public void setGroupsForUser( SecurityTransaction transaction, User user, Group[] groups )
1906 throws GeneralSecurityException {
1907 PreparedStatement pstmt = null;
1908
1909 try {
1910 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_GROUPS WHERE FK_USERS=?" );
1911 pstmt.setInt( 1, user.getID() );
1912 pstmt.executeUpdate();
1913 pstmt.close();
1914 pstmt = null;
1915
1916 for ( int i = 0; i < groups.length; i++ ) {
1917 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_USERS_GROUPS (FK_USERS, FK_GROUPS) VALUES (?,?)" );
1918 pstmt.setInt( 1, user.getID() );
1919 pstmt.setInt( 2, groups[i].getID() );
1920 pstmt.executeUpdate();
1921 pstmt.close();
1922 pstmt = null;
1923 }
1924 } catch ( SQLException e ) {
1925 closeStatement( pstmt );
1926 abortTransaction( transaction );
1927 throw new GeneralSecurityException( "SQLRegistry.setGroupsForUser() failed. Rollback performed. "
1928 + "Error message: " + e.getMessage() );
1929 }
1930 }
1931
1932 /**
1933 * Sets the <code>Group</code> s that a given <code>Group</code> is member of DIRECTLY (i.e. not via inheritance).
1934 *
1935 * @param transaction
1936 * @param group
1937 * @param groups
1938 * @throws GeneralSecurityException
1939 */
1940 public void setGroupsForGroup( SecurityTransaction transaction, Group group, Group[] groups )
1941 throws GeneralSecurityException {
1942 PreparedStatement pstmt = null;
1943
1944 try {
1945 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_GROUPS WHERE FK_GROUPS_MEMBER=?" );
1946 pstmt.setInt( 1, group.getID() );
1947 pstmt.executeUpdate();
1948 pstmt.close();
1949 pstmt = null;
1950
1951 for ( int i = 0; i < groups.length; i++ ) {
1952 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_GROUPS_GROUPS (FK_GROUPS_MEMBER, FK_GROUPS) VALUES (?,?)" );
1953 pstmt.setInt( 1, group.getID() );
1954 pstmt.setInt( 2, groups[i].getID() );
1955 pstmt.executeUpdate();
1956 pstmt.close();
1957 pstmt = null;
1958 }
1959 } catch ( SQLException e ) {
1960 closeStatement( pstmt );
1961 abortTransaction( transaction );
1962 throw new GeneralSecurityException( "SQLRegistry.setGroupsForGroup() failed. Rollback performed. "
1963 + "Error message: " + e.getMessage() );
1964 }
1965 }
1966
1967 /**
1968 * Sets the <code>Group</code> s that a given <code>Role</code> is associated to DIRECTLY (i.e. not via
1969 * inheritance).
1970 *
1971 * @param transaction
1972 * @param role
1973 * @param groups
1974 * @throws GeneralSecurityException
1975 */
1976 public void setGroupsWithRole( SecurityTransaction transaction, Role role, Group[] groups )
1977 throws GeneralSecurityException {
1978 PreparedStatement pstmt = null;
1979
1980 try {
1981 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_ROLES WHERE FK_ROLES=?" );
1982 pstmt.setInt( 1, role.getID() );
1983 pstmt.executeUpdate();
1984 pstmt.close();
1985 pstmt = null;
1986
1987 for ( int i = 0; i < groups.length; i++ ) {
1988 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_GROUPS_ROLES (FK_GROUPS, FK_ROLES) VALUES (?,?)" );
1989 pstmt.setInt( 1, groups[i].getID() );
1990 pstmt.setInt( 2, role.getID() );
1991 pstmt.executeUpdate();
1992 pstmt.close();
1993 pstmt = null;
1994 }
1995 } catch ( SQLException e ) {
1996 closeStatement( pstmt );
1997 abortTransaction( transaction );
1998 throw new GeneralSecurityException( "SQLRegistry.setGroupsWithRole() failed. Rollback performed. "
1999 + "Error message: " + e.getMessage() );
2000 }
2001 }
2002
2003 /**
2004 * Sets the <code>User</code> s that a given <code>Role</code> is associated to DIRECTLY (i.e. not via
2005 * <code>Group</code> membership).
2006 *
2007 * @param transaction
2008 * @param role
2009 * @param users
2010 * @throws GeneralSecurityException
2011 */
2012 public void setUsersWithRole( SecurityTransaction transaction, Role role, User[] users )
2013 throws GeneralSecurityException {
2014
2015 PreparedStatement pstmt = null;
2016
2017 try {
2018 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_ROLES WHERE FK_ROLES=?" );
2019 pstmt.setInt( 1, role.getID() );
2020 pstmt.executeUpdate();
2021 pstmt.close();
2022 pstmt = null;
2023
2024 for ( int i = 0; i < users.length; i++ ) {
2025 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_USERS_ROLES (FK_USERS, FK_ROLES) VALUES (?,?)" );
2026 pstmt.setInt( 1, users[i].getID() );
2027 pstmt.setInt( 2, role.getID() );
2028 pstmt.executeUpdate();
2029 pstmt.close();
2030 pstmt = null;
2031 }
2032 } catch ( SQLException e ) {
2033 closeStatement( pstmt );
2034 abortTransaction( transaction );
2035 throw new GeneralSecurityException( "SQLRegistry.setUsersWithRole() failed. Rollback performed. "
2036 + "Error message: " + e.getMessage() );
2037 }
2038 }
2039
2040 /**
2041 * Sets the <code>User</code> s that are members of a given <code>Group</code> DIRECTLY (i.e. not via inheritance).
2042 *
2043 * @param transaction
2044 * @param group
2045 * @param users
2046 * @throws GeneralSecurityException
2047 */
2048 public void setUsersInGroup( SecurityTransaction transaction, Group group, User[] users )
2049 throws GeneralSecurityException {
2050 PreparedStatement pstmt = null;
2051
2052 try {
2053 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_GROUPS WHERE FK_GROUPS=?" );
2054 pstmt.setInt( 1, group.getID() );
2055 pstmt.executeUpdate();
2056 pstmt.close();
2057 pstmt = null;
2058
2059 for ( int i = 0; i < users.length; i++ ) {
2060 closeStatement( pstmt );
2061 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_USERS_GROUPS (FK_USERS, FK_GROUPS) VALUES (?,?)" );
2062 pstmt.setInt( 1, users[i].getID() );
2063 pstmt.setInt( 2, group.getID() );
2064 pstmt.executeUpdate();
2065 pstmt.close();
2066 pstmt = null;
2067 }
2068 } catch ( SQLException e ) {
2069 closeStatement( pstmt );
2070 abortTransaction( transaction );
2071 throw new GeneralSecurityException( "SQLRegistry.setUsersInGroup() failed. Rollback performed. "
2072 + "Error message: " + e.getMessage() );
2073 }
2074 }
2075
2076 /**
2077 * Sets the <code>Groups</code> s that are members of a given <code>Group</code> DIRECTLY (i.e. not via
2078 * inheritance).
2079 *
2080 * @param transaction
2081 * @param group
2082 * @param groups
2083 * @throws GeneralSecurityException
2084 */
2085 public void setGroupsInGroup( SecurityTransaction transaction, Group group, Group[] groups )
2086 throws GeneralSecurityException {
2087 PreparedStatement pstmt = null;
2088
2089 try {
2090 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_GROUPS WHERE FK_GROUPS=?" );
2091 pstmt.setInt( 1, group.getID() );
2092 pstmt.executeUpdate();
2093 pstmt.close();
2094 pstmt = null;
2095
2096 for ( int i = 0; i < groups.length; i++ ) {
2097 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_GROUPS_GROUPS (FK_GROUPS_MEMBER, FK_GROUPS) VALUES (?,?)" );
2098 pstmt.setInt( 1, groups[i].getID() );
2099 pstmt.setInt( 2, group.getID() );
2100 pstmt.executeUpdate();
2101 pstmt.close();
2102 pstmt = null;
2103 }
2104 } catch ( SQLException e ) {
2105 closeStatement( pstmt );
2106 abortTransaction( transaction );
2107 throw new GeneralSecurityException( "SQLRegistry.setGroupsInGroup() failed. Rollback performed. "
2108 + "Error message: " + e.getMessage() );
2109 }
2110 }
2111
2112 /**
2113 * Sets the <code>Role</code> s that a given <code>User</code> is directly associated to (i.e. not via
2114 * <code>Group</code> membership).
2115 *
2116 * @param transaction
2117 * @param user
2118 * @param roles
2119 * @throws GeneralSecurityException
2120 */
2121 public void setRolesForUser( SecurityTransaction transaction, User user, Role[] roles )
2122 throws GeneralSecurityException {
2123 PreparedStatement pstmt = null;
2124
2125 try {
2126 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_USERS_ROLES WHERE FK_USERS=?" );
2127 pstmt.setInt( 1, user.getID() );
2128 pstmt.executeUpdate();
2129 pstmt.close();
2130 pstmt = null;
2131
2132 for ( int i = 0; i < roles.length; i++ ) {
2133 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_USERS_ROLES (FK_USERS, FK_ROLES) VALUES (?,?)" );
2134 pstmt.setInt( 1, user.getID() );
2135 pstmt.setInt( 2, roles[i].getID() );
2136 pstmt.executeUpdate();
2137 pstmt.close();
2138 pstmt = null;
2139 }
2140 } catch ( SQLException e ) {
2141 closeStatement( pstmt );
2142 abortTransaction( transaction );
2143 throw new GeneralSecurityException( "SQLRegistry.setRolesForUser() failed. Rollback performed. "
2144 + "Error message: " + e.getMessage() );
2145 }
2146 }
2147
2148 /**
2149 * Sets the <code>Role</code> s that a given <code>Group</code> is associated to directly (i.e. not via
2150 * inheritance).
2151 *
2152 * @param transaction
2153 * @param group
2154 * @param roles
2155 * @throws GeneralSecurityException
2156 */
2157 public void setRolesForGroup( SecurityTransaction transaction, Group group, Role[] roles )
2158 throws GeneralSecurityException {
2159 PreparedStatement pstmt = null;
2160
2161 try {
2162 pstmt = transactionalConnection.prepareStatement( "DELETE FROM SEC_JT_GROUPS_ROLES WHERE FK_GROUPS=?" );
2163 pstmt.setInt( 1, group.getID() );
2164 pstmt.executeUpdate();
2165 pstmt.close();
2166 pstmt = null;
2167
2168 for ( int i = 0; i < roles.length; i++ ) {
2169 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_JT_GROUPS_ROLES (FK_GROUPS, FK_ROLES) VALUES (?,?)" );
2170 pstmt.setInt( 1, group.getID() );
2171 pstmt.setInt( 2, roles[i].getID() );
2172 pstmt.executeUpdate();
2173 pstmt.close();
2174 pstmt = null;
2175 }
2176 } catch ( SQLException e ) {
2177 closeStatement( pstmt );
2178 abortTransaction( transaction );
2179 throw new GeneralSecurityException( "SQLRegistry.setRolesForGroup() failed. Rollback performed. "
2180 + "Error message: " + e.getMessage() );
2181 }
2182 }
2183
2184 /**
2185 * Initializes the <code>SQLRegistry</code> -instance according to the contents of the submitted
2186 * <code>Properties</code>.
2187 *
2188 * @param properties
2189 * @throws GeneralSecurityException
2190 */
2191 public void initialize( Properties properties )
2192 throws GeneralSecurityException {
2193 this.dbDriver = properties.getProperty( "driver" );
2194 this.dbName = properties.getProperty( "url" );
2195 this.dbUser = properties.getProperty( "user" );
2196 this.dbPassword = properties.getProperty( "password" );
2197 }
2198
2199 /**
2200 * Signals the <code>SQLRegistry</code> that a new transaction begins.
2201 *
2202 * Only one transaction can be active at a time.
2203 *
2204 *
2205 * @param transaction
2206 * @throws GeneralSecurityException
2207 */
2208 public synchronized void beginTransaction( SecurityTransaction transaction )
2209 throws GeneralSecurityException {
2210 try {
2211 transactionalConnection = DBConnectionPool.getInstance().acquireConnection( dbDriver, dbName, dbUser,
2212 dbPassword );
2213 // transactionalConnection.setAutoCommit(false);
2214 } catch ( Exception e ) {
2215 throw new GeneralSecurityException( e );
2216 }
2217 }
2218
2219 /**
2220 * Signals the <code>SQLRegistry</code> that the current transaction ends, i.e. the changes made by the transaction
2221 * are made persistent.
2222 *
2223 * @param transaction
2224 * @throws GeneralSecurityException
2225 */
2226 public void commitTransaction( SecurityTransaction transaction )
2227 throws GeneralSecurityException {
2228 try {
2229 transactionalConnection.commit();
2230 } catch ( SQLException e ) {
2231 throw new GeneralSecurityException( "Committing of transaction failed: " + e.getMessage() );
2232 } finally {
2233 try {
2234 DBConnectionPool.getInstance().releaseConnection( transactionalConnection, dbDriver, dbName, dbUser,
2235 dbPassword );
2236 } catch ( Exception e ) {
2237 e.printStackTrace();
2238 }
2239 }
2240 }
2241
2242 /**
2243 * Signals the <code>SQLRegistry</code> that the current transaction shall be aborted. Changes made during the
2244 * transaction are undone.
2245 *
2246 * @param transaction
2247 * @throws GeneralSecurityException
2248 */
2249 public void abortTransaction( SecurityTransaction transaction )
2250 throws GeneralSecurityException {
2251 try {
2252 transactionalConnection.rollback();
2253 } catch ( SQLException e ) {
2254 throw new GeneralSecurityException( "Aborting of transaction failed: " + e.getMessage() );
2255 } finally {
2256 try {
2257 DBConnectionPool.getInstance().releaseConnection( transactionalConnection, dbDriver, dbName, dbUser,
2258 dbPassword );
2259 } catch ( Exception e ) {
2260 e.printStackTrace();
2261 }
2262 }
2263 }
2264
2265 /**
2266 * Acquires a new <code>Connection</code>. If the given securityAccess is the exclusive Read/Write-transaction
2267 * holder, the transactionalConnection is returned, else a new <code>Connection</code> is taken from the pool.
2268 *
2269 * @param securityAccess
2270 * @throws GeneralSecurityException
2271 */
2272 private Connection acquireLocalConnection( SecurityAccess securityAccess )
2273 throws GeneralSecurityException {
2274
2275 Connection con = null;
2276
2277 if ( securityAccess instanceof SecurityTransaction ) {
2278 con = transactionalConnection;
2279 } else {
2280 try {
2281 con = DBConnectionPool.getInstance().acquireConnection( dbDriver, dbName, dbUser, dbPassword );
2282 // con.setAutoCommit(false);
2283 } catch ( Exception e ) {
2284 throw new GeneralSecurityException( e );
2285 }
2286 }
2287 return con;
2288 }
2289
2290 /**
2291 * Releases a <code>Connection</code>. If the given securityAccess is the exclusive Read/Write-transaction holder,
2292 * nothing happens, else it is returned to the pool.
2293 *
2294 * @param securityAccess
2295 * @param con
2296 * @throws GeneralSecurityException
2297 */
2298 private void releaseLocalConnection( SecurityAccess securityAccess, Connection con )
2299 throws GeneralSecurityException {
2300
2301 if ( !( securityAccess instanceof SecurityTransaction ) ) {
2302 if ( con != null ) {
2303 try {
2304 DBConnectionPool.getInstance().releaseConnection( con, dbDriver, dbName, dbUser, dbPassword );
2305 } catch ( Exception e ) {
2306 throw new GeneralSecurityException( e );
2307 }
2308 }
2309 }
2310 }
2311
2312 /**
2313 * Closes the given <code>Statement</code> if it is not null.
2314 *
2315 * @param stmt
2316 * @throws GeneralSecurityException
2317 */
2318 private void closeStatement( Statement stmt )
2319 throws GeneralSecurityException {
2320 if ( stmt != null ) {
2321 try {
2322 stmt.close();
2323 } catch ( SQLException e ) {
2324 throw new GeneralSecurityException( e );
2325 }
2326 }
2327 }
2328
2329 /**
2330 * Closes the given <code>ResultSet</code> if it is not null.
2331 *
2332 * @param rs
2333 * @throws GeneralSecurityException
2334 */
2335 private void closeResultSet( ResultSet rs )
2336 throws GeneralSecurityException {
2337 if ( rs != null ) {
2338 try {
2339 rs.close();
2340 } catch ( SQLException e ) {
2341 throw new GeneralSecurityException( e );
2342 }
2343 }
2344 }
2345
2346 /**
2347 * Retrieves an unused PrimaryKey-value for the given table. The table must have its PrimaryKey in an Integer-field
2348 * named 'ID'.
2349 *
2350 * @param table
2351 */
2352 private int getID( SecurityTransaction transaction, String table )
2353 throws GeneralSecurityException {
2354 int id = 0;
2355 Connection con = acquireLocalConnection( transaction );
2356
2357 try {
2358 DataBaseIDGenerator idGenerator = IDGeneratorFactory.createIDGenerator( con, table, "ID" );
2359 Object o = idGenerator.generateUniqueId();
2360 if ( !( o instanceof Integer ) ) {
2361 throw new GeneralSecurityException( "Error generating new PrimaryKey for table '" + table + "'." );
2362 }
2363 id = ( (Integer) o ).intValue();
2364 } catch ( SQLException e ) {
2365 throw new GeneralSecurityException( e );
2366 } finally {
2367 releaseLocalConnection( transaction, con );
2368 }
2369 return id;
2370 }
2371
2372 /**
2373 * Tries to build a <code>ComplexFilter</code> from the given string representation.
2374 *
2375 * @param constraints
2376 * @throws GeneralSecurityException
2377 */
2378 private ComplexFilter buildFilter( String constraints )
2379 throws GeneralSecurityException {
2380 Filter filter = null;
2381 try {
2382 Document document = XMLTools.parse( new StringReader( constraints ) );
2383 Element element = document.getDocumentElement();
2384 filter = AbstractFilter.buildFromDOM( element, false );
2385 } catch ( FilterConstructionException e ) {
2386 throw new GeneralSecurityException( "The stored constraint is not a valid filter: " + e.getMessage() );
2387 } catch ( Exception e ) {
2388 throw new GeneralSecurityException( "Error parsing the stored constraint: " + e.getMessage() );
2389 }
2390 if ( !( filter instanceof ComplexFilter ) ) {
2391 throw new GeneralSecurityException( "The stored constraint is not of type 'ComplexFilter'." );
2392 }
2393 return (ComplexFilter) filter;
2394 }
2395
2396 /**
2397 * @param access
2398 * @param address
2399 * @return the service from the db
2400 * @throws GeneralSecurityException
2401 */
2402 public Service getServiceByAddress( SecurityAccess access, String address )
2403 throws GeneralSecurityException {
2404 Connection con = acquireLocalConnection( access );
2405 PreparedStatement pstmt = null;
2406 ResultSet rs = null;
2407
2408 Service service;
2409 try {
2410 pstmt = con.prepareStatement( "select sec_services.id,sec_services.address,sec_services.title,sec_services.type,sec_services_objects.name,sec_services_objects.title from "
2411 + " sec_services,sec_services_objects "
2412 + "where sec_services.id = sec_services_objects.serviceid and "
2413 + "sec_services.address = ?" );
2414 pstmt.setString( 1, address );
2415 rs = pstmt.executeQuery();
2416 String title = null;
2417 String type = null;
2418 LinkedList<StringPair> objects = new LinkedList<StringPair>();
2419 int id = 0;
2420 while ( rs.next() ) {
2421 id = rs.getInt( 1 );
2422 title = rs.getString( 3 );
2423 type = rs.getString( 4 );
2424 objects.add( new StringPair( rs.getString( 5 ), rs.getString( 6 ) ) );
2425 }
2426
2427 if ( id == 0 ) {
2428 throw new UnknownException( "Lookup of service '" + address
2429 + "' failed! A service with this address does not exist." );
2430 }
2431
2432 service = new Service( id, address, title, objects, type );
2433 } catch ( SQLException e ) {
2434 LOG.logError( e.getMessage(), e );
2435 throw new GeneralSecurityException( e );
2436 } finally {
2437 closeResultSet( rs );
2438 closeStatement( pstmt );
2439 releaseLocalConnection( access, con );
2440 }
2441
2442 return service;
2443 }
2444
2445 /**
2446 * @param transaction
2447 * @param address
2448 * @param title
2449 * @param objects
2450 * @param type
2451 * @return the new service object
2452 * @throws GeneralSecurityException
2453 */
2454 public Service registerService( SecurityTransaction transaction, String address, String title,
2455 List<StringPair> objects, String type )
2456 throws GeneralSecurityException {
2457 try {
2458 getServiceByAddress( transaction, address );
2459 throw new DuplicateException( "Registration of service '" + address + "' failed! A service with "
2460 + "this address already exists." );
2461 } catch ( UnknownException e ) {
2462 // then it's no duplicate
2463 }
2464
2465 int id = getID( transaction, "SEC_SERVICES" );
2466 Service service = new Service( id, address, title, objects, type );
2467
2468 PreparedStatement pstmt = null;
2469
2470 try {
2471 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SERVICES (ID,ADDRESS,TITLE,TYPE) VALUES (?,?,?,?)" );
2472 pstmt.setInt( 1, id );
2473 pstmt.setString( 2, address );
2474 pstmt.setString( 3, title );
2475 pstmt.setString( 4, type );
2476 pstmt.executeUpdate();
2477 closeStatement( pstmt );
2478 pstmt = null;
2479
2480 for ( StringPair pair : objects ) {
2481 pstmt = transactionalConnection.prepareStatement( "INSERT INTO SEC_SERVICES_OBJECTS (SERVICEID,NAME,TITLE) VALUES (?,?,?)" );
2482 pstmt.setInt( 1, id );
2483 pstmt.setString( 2, pair.first );
2484 pstmt.setString( 3, pair.second );
2485 pstmt.executeUpdate();
2486 closeStatement( pstmt );
2487 pstmt = null;
2488 }
2489 } catch ( SQLException e ) {
2490 LOG.logError( e.getMessage(), e );
2491 closeStatement( pstmt );
2492 abortTransaction( transaction );
2493 throw new GeneralSecurityException( "SQLRegistry.registerService() failed. Rollback performed. "
2494 + "Error message: " + e.getMessage() );
2495 }
2496
2497 return service;
2498 }
2499
2500 /**
2501 * @param transaction
2502 * @param service
2503 * @throws GeneralSecurityException
2504 */
2505 public void deregisterService( SecurityTransaction transaction, Service service )
2506 throws GeneralSecurityException {
2507 PreparedStatement pstmt = null;
2508
2509 try {
2510 pstmt = transactionalConnection.prepareStatement( "delete from sec_services where id = ?" );
2511 pstmt.setInt( 1, service.getId() );
2512 pstmt.executeUpdate();
2513 pstmt.close();
2514 pstmt = null;
2515 pstmt = transactionalConnection.prepareStatement( "delete from sec_services_objects where serviceid = ?" );
2516 pstmt.setInt( 1, service.getId() );
2517 pstmt.executeUpdate();
2518 pstmt.close();
2519 pstmt = null;
2520 } catch ( SQLException e ) {
2521 LOG.logError( e.getMessage(), e );
2522 closeStatement( pstmt );
2523 abortTransaction( transaction );
2524 throw new GeneralSecurityException( "SQLRegistry.deregisterService() failed. Rollback performed. "
2525 + "Error message: " + e.getMessage() );
2526 }
2527 }
2528
2529 public LinkedList<Service> getAllServices( SecurityAccess access )
2530 throws GeneralSecurityException {
2531 Connection con = acquireLocalConnection( access );
2532 PreparedStatement pstmt = null;
2533 ResultSet rs = null;
2534
2535 LinkedList<String> addresses = new LinkedList<String>();
2536 LinkedList<Service> services = new LinkedList<Service>();
2537 try {
2538 pstmt = con.prepareStatement( "select address from sec_services" );
2539 rs = pstmt.executeQuery();
2540 while ( rs.next() ) {
2541 addresses.add( rs.getString( 1 ) );
2542 }
2543 rs.close();
2544 pstmt.close();
2545 } catch ( SQLException e ) {
2546 LOG.logError( e.getMessage(), e );
2547 throw new GeneralSecurityException( e );
2548 } finally {
2549 closeResultSet( rs );
2550 closeStatement( pstmt );
2551 releaseLocalConnection( access, con );
2552 }
2553
2554 for ( String address : addresses ) {
2555 services.add( getServiceByAddress( access, address ) );
2556 }
2557
2558 return services;
2559 }
2560
2561 public void updateService( SecurityTransaction transaction, Service oldService, Service newService )
2562 throws GeneralSecurityException {
2563 Connection con = acquireLocalConnection( transaction );
2564 PreparedStatement pstmt = null;
2565
2566 Map<String, String> oldObjects = new HashMap<String, String>();
2567 for ( StringPair pair : oldService.getObjects() ) {
2568 oldObjects.put( pair.first, pair.second );
2569 }
2570 Map<String, String> newObjects = new HashMap<String, String>();
2571 for ( StringPair pair : newService.getObjects() ) {
2572 newObjects.put( pair.first, pair.second );
2573 }
2574
2575 try {
2576 for ( StringPair pair : newService.getObjects() ) {
2577 if ( !oldObjects.containsKey( pair.first ) ) {
2578 pstmt = con.prepareStatement( "insert into sec_services_objects (serviceid, name, title) values (?,?,?)" );
2579 pstmt.setInt( 1, oldService.getId() );
2580 pstmt.setString( 2, pair.first );
2581 pstmt.setString( 3, pair.second );
2582 pstmt.executeUpdate();
2583 }
2584 }
2585
2586 for ( StringPair pair : oldService.getObjects() ) {
2587 if ( !newObjects.containsKey( pair.first ) ) {
2588 pstmt = con.prepareStatement( "delete from sec_services_objects where name = ? and serviceid = ?" );
2589 pstmt.setString( 1, pair.first );
2590 pstmt.setInt( 2, oldService.getId() );
2591 pstmt.executeUpdate();
2592 } else {
2593 pstmt = con.prepareStatement( "update sec_services_objects set title = ? where name = ? and serviceid = ?" );
2594 pstmt.setString( 1, newObjects.get( pair.first ) );
2595 pstmt.setString( 2, pair.first );
2596 pstmt.setInt( 3, oldService.getId() );
2597 pstmt.executeUpdate();
2598 pstmt = con.prepareStatement( "update sec_securable_objects set title = ? where name = ?" );
2599 pstmt.setString( 1, newObjects.get( pair.first ) );
2600 pstmt.setString( 2, "[" + newService.getAddress() + "]:" + pair.first );
2601 pstmt.executeUpdate();
2602 }
2603 }
2604 } catch ( SQLException e ) {
2605 LOG.logError( e.getMessage(), e );
2606 throw new GeneralSecurityException( e );
2607 } finally {
2608 closeStatement( pstmt );
2609 releaseLocalConnection( transaction, con );
2610 }
2611 }
2612
2613 public void renameObject( SecurityTransaction transaction, Service service, String oldName, String newName )
2614 throws GeneralSecurityException {
2615 Connection con = acquireLocalConnection( transaction );
2616 PreparedStatement pstmt = null;
2617
2618 try {
2619 pstmt = con.prepareStatement( "update sec_services_objects set name = ? where name = ? and serviceid = ?" );
2620 pstmt.setString( 1, newName );
2621 pstmt.setString( 2, oldName );
2622 pstmt.setInt( 3, service.getId() );
2623 pstmt.executeUpdate();
2624 pstmt = con.prepareStatement( "update sec_securable_objects set name = ? where name = ?" );
2625 String prefix = "[" + service.getAddress() + "]:";
2626 pstmt.setString( 1, prefix + newName );
2627 pstmt.setString( 2, prefix + oldName );
2628 pstmt.executeUpdate();
2629 } catch ( SQLException e ) {
2630 LOG.logError( e.getMessage(), e );
2631 throw new GeneralSecurityException( e );
2632 } finally {
2633 closeStatement( pstmt );
2634 releaseLocalConnection( transaction, con );
2635 }
2636 }
2637
2638 public void editService( SecurityTransaction transaction, Service service, String newTitle, String newAddress )
2639 throws GeneralSecurityException {
2640 Connection con = acquireLocalConnection( transaction );
2641 PreparedStatement pstmt = null;
2642
2643 try {
2644 if ( newTitle != null ) {
2645 pstmt = con.prepareStatement( "update sec_services set title = ? where id = ?" );
2646 pstmt.setString( 1, newTitle );
2647 pstmt.setInt( 2, service.getId() );
2648 pstmt.executeUpdate();
2649 }
2650 if ( newAddress != null ) {
2651 pstmt = con.prepareStatement( "update sec_services set address = ? where id = ?" );
2652 pstmt.setString( 1, newAddress );
2653 pstmt.setInt( 2, service.getId() );
2654 pstmt.executeUpdate();
2655
2656 String oldPrefix = "[" + service.getAddress() + "]:";
2657 String newPrefix = "[" + newAddress + "]:";
2658 for ( StringPair pair : service.getObjects() ) {
2659 pstmt = con.prepareStatement( "update sec_securable_objects set name = ? where name = ?" );
2660 pstmt.setString( 1, newPrefix + pair.first );
2661 pstmt.setString( 2, oldPrefix + pair.first );
2662 pstmt.executeUpdate();
2663 }
2664 }
2665 } catch ( SQLException e ) {
2666 LOG.logError( e.getMessage(), e );
2667 throw new GeneralSecurityException( e );
2668 } finally {
2669 closeStatement( pstmt );
2670 releaseLocalConnection( transaction, con );
2671 }
2672 }
2673 }