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