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