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