    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/tools/security/SecDBCleaner.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.tools.security;
    037
    038    import static java.util.Arrays.asList;
    039    import static org.deegree.framework.util.CollectionUtils.containsAllEqual;
    040    import static org.deegree.framework.util.CollectionUtils.containsEqual;
    041    import static org.deegree.framework.util.JavaUtils.generateToString;
    042    import static org.deegree.framework.xml.XMLTools.getNodesAsQualifiedNames;
    043    import static org.deegree.framework.xml.XMLTools.getNodesAsStringList;
    044    import static org.deegree.ogcbase.CommonNamespaces.getNamespaceContext;
    045
    046    import java.io.IOException;
    047    import java.net.MalformedURLException;
    048    import java.net.URL;
    049    import java.sql.Connection;
    050    import java.sql.DriverManager;
    051    import java.sql.ResultSet;
    052    import java.sql.SQLException;
    053    import java.sql.Statement;
    054    import java.util.HashSet;
    055
    056    import org.deegree.datatypes.QualifiedName;
    057    import org.deegree.framework.xml.NamespaceContext;
    058    import org.deegree.framework.xml.XMLFragment;
    059    import org.deegree.framework.xml.XMLParsingException;
    060    import org.xml.sax.SAXException;
    061
    062    /**
    063     * <code>SecDBCleaner</code>
    064     *
    065     * @author <a href="mailto:schmitz@lat-lon.de">Andreas Schmitz</a>
    066     * @author last edited by: $Author: aschmitz $
    067     *
    068     * @version $Revision: 23585 $, $Date: 2010-04-13 14:07:18 +0200 (Di, 13. Apr 2010) $
    069     */
    070    public class SecDBCleaner {
    071
    072        private static final NamespaceContext nsContext = getNamespaceContext();
    073
    074        private Connection conn;
    075
    076        private Statement stmt;
    077
    078        /**
    079         * @param jdbc
    080         * @param user
    081         * @param pass
    082         */
    083        public SecDBCleaner( String jdbc, String user, String pass ) {
    084            try {
    085                Class.forName( "org.postgresql.Driver" );
    086            } catch ( ClassNotFoundException e ) {
    087                System.out.println( "PostgreSQL driver could not be loaded. Make sure it's on the classpath." );
    088            }
    089            try {
    090                Class.forName( "oracle.jdbc.driver.OracleDriver" );
    091            } catch ( ClassNotFoundException e ) {
    092                System.out.println( "Oracle driver could not be loaded. Make sure it's on the classpath." );
    093            }
    094            try {
    095                this.conn = DriverManager.getConnection( jdbc, user, pass );
    096                this.stmt = conn.createStatement();
    097            } catch ( SQLException e ) {
    098                System.out.println( "Database access failed: " + e.getLocalizedMessage() );
    099                System.out.println( "Stack trace: " );
    100                e.printStackTrace();
    101            }
    102        }
    103
    104        private void checkLayers( String originalService ) {
    105            String service = originalService;
    106            if ( service.indexOf( "?" ) == -1 ) {
    107                service = service + "?request=capabilities&service=WMS&version=1.1.1";
    108            }
    109            try {
    110                stmt.execute( "select id from sec_secured_object_types where name = 'Layer'" );
    111                ResultSet set = stmt.getResultSet();
    112                int id = 1;
    113                while ( set.next() ) {
    114                    id = set.getInt( "id" );
    115                }
    116                set.close();
    117
    118                URL url = new URL( service );
    119                XMLFragment doc = new XMLFragment( url );
    120                HashSet<String> layers = new HashSet<String>( getNodesAsStringList( doc.getRootElement(), "//Layer/Name",
    121                                                                                    nsContext ) );
    122
    123                stmt.execute( "select sec_securable_objects.id as id,sec_securable_objects.name as name from sec_secured_objects,sec_securable_objects where sec_secured_objects.fk_secured_object_types = "
    124                              + id + " and sec_secured_objects.id = sec_securable_objects.id" );
    125                set = stmt.getResultSet();
    126                while ( set.next() ) {
    127                    String lname = set.getString( "name" );
    128                    int lid = set.getInt( "id" );
    129                    if ( !layers.contains( lname )
    130                         && !( lname.indexOf( ":" ) != -1 && layers.contains( lname.substring( lname.lastIndexOf( "]" ) + 2 ) ) ) ) {
    131                        System.out.println( "The layer with name '" + lname + "' and id " + lid
    132                                            + " was not found in the WMS." );
    133                    }
    134                }
    135                set.close();
    136
    137            } catch ( SQLException e ) {
    138                System.out.println( "Database access failed: " + e.getLocalizedMessage() );
    139                System.out.println( "Stack trace: " );
    140                e.printStackTrace();
    141            } catch ( MalformedURLException e ) {
    142                System.out.println( "The service URL '" + service + "' was not valid." );
    143                System.out.println( "Stack trace: " );
    144                e.printStackTrace();
    145            } catch ( IOException e ) {
    146                System.out.println( "The service URL '" + service + "' was not valid." );
    147                System.out.println( "Stack trace: " );
    148                e.printStackTrace();
    149            } catch ( SAXException e ) {
    150                System.out.println( "The service URL '" + service + "' was not valid." );
    151                System.out.println( "Stack trace: " );
    152                e.printStackTrace();
    153            } catch ( XMLParsingException e ) {
    154                System.out.println( "The service URL '" + service + "' was not valid." );
    155                System.out.println( "Stack trace: " );
    156                e.printStackTrace();
    157            }
    158        }
    159
    160        private void checkFeaturetypes( String originalService ) {
    161            String service = originalService;
    162            if ( service.indexOf( "?" ) == -1 ) {
    163                service = service + "?request=GetCapabilities&service=WFS&version=1.1.0";
    164            }
    165            try {
    166                stmt.execute( "select id from sec_secured_object_types where name = 'Featuretype'" );
    167                ResultSet set = stmt.getResultSet();
    168                int id = 2;
    169                while ( set.next() ) {
    170                    id = set.getInt( "id" );
    171                }
    172                set.close();
    173
    174                URL url = new URL( service );
    175                XMLFragment doc = new XMLFragment( url );
    176
    177                QualifiedName[] qnames = getNodesAsQualifiedNames( doc.getRootElement(), "//wfs:FeatureType/wfs:Name",
    178                                                                   nsContext );
    179                HashSet<QualifiedName> types = new HashSet<QualifiedName>( asList( qnames ) );
    180
    181                stmt.execute( "select sec_securable_objects.id as id,sec_securable_objects.name as name from sec_secured_objects,sec_securable_objects where sec_secured_objects.fk_secured_object_types = "
    182                              + id + " and sec_secured_objects.id = sec_securable_objects.id" );
    183                set = stmt.getResultSet();
    184                while ( set.next() ) {
    185                    String name = set.getString( "name" );
    186                    QualifiedName fname = new QualifiedName( name );
    187                    QualifiedName fname2 = new QualifiedName( name.substring( name.lastIndexOf( "]" ) + 2 ) );
    188                    int fid = set.getInt( "id" );
    189                    if ( !containsEqual( types, fname ) && !containsEqual( types, fname2 ) ) {
    190                        System.out.println( "The feature type with name '" + name + "' and id " + fid
    191                                            + " was not found in the WFS." );
    192                    }
    193                }
    194                set.close();
    195            } catch ( SQLException e ) {
    196                System.out.println( "Database access failed: " + e.getLocalizedMessage() );
    197                System.out.println( "Stack trace: " );
    198                e.printStackTrace();
    199            } catch ( MalformedURLException e ) {
    200                System.out.println( "The service URL '" + service + "' was not valid." );
    201                System.out.println( "Stack trace: " );
    202                e.printStackTrace();
    203            } catch ( IOException e ) {
    204                System.out.println( "The service URL '" + service + "' was not valid." );
    205                System.out.println( "Stack trace: " );
    206                e.printStackTrace();
    207            } catch ( SAXException e ) {
    208                System.out.println( "The service URL '" + service + "' was not valid." );
    209                System.out.println( "Stack trace: " );
    210                e.printStackTrace();
    211            } catch ( XMLParsingException e ) {
    212                System.out.println( "The service URL '" + service + "' was not valid." );
    213                System.out.println( "Stack trace: " );
    214                e.printStackTrace();
    215            }
    216        }
    217
    218        private void findDuplicateRoles() {
    219            try {
    220                stmt.execute( "select id from sec_roles" );
    221                ResultSet set = stmt.getResultSet();
    222
    223                HashSet<Role> roles = new HashSet<Role>();
    224                while ( set.next() ) {
    225                    Role r = new Role();
    226                    r.id = set.getInt( "id" );
    227                    roles.add( r );
    228                }
    229                set.close();
    230
    231                for ( Role r : roles ) {
    232                    stmt.execute( "select name, title from sec_securable_objects where id = " + r.id );
    233                    set = stmt.getResultSet();
    234
    235                    while ( set.next() ) {
    236                        r.name = set.getString( "name" );
    237                        r.title = set.getString( "title" );
    238                    }
    239
    240                    set.close();
    241                }
    242
    243                for ( Role role : roles ) {
    244                    stmt.execute( "select fk_privileges,constraints from sec_jt_roles_privileges where fk_roles = "
    245                                  + role.id );
    246                    set = stmt.getResultSet();
    247
    248                    while ( set.next() ) {
    249                        Privilege p = new Privilege();
    250                        p.id = set.getInt( "fk_privileges" );
    251                        p.constraint = set.getString( "constraints" );
    252                        role.privileges.add( p );
    253                    }
    254
    255                    set.close();
    256
    257                    stmt.execute( "select fk_securable_objects,fk_rights,constraints from sec_jt_roles_secobjects where fk_roles = "
    258                                  + role.id );
    259                    set = stmt.getResultSet();
    260
    261                    while ( set.next() ) {
    262                        Right r = new Right();
    263                        r.id = set.getInt( "fk_securable_objects" );
    264                        r.right = set.getInt( "fk_rights" );
    265                        r.constraint = set.getString( "constraints" );
    266                        role.rights.add( r );
    267                    }
    268
    269                    set.close();
    270                }
    271
    272                HashSet<Role> newRoles = new HashSet<Role>();
    273
    274                for ( Role r : roles ) {
    275                    if ( containsEqual( newRoles, r ) ) {
    276                        inner: for ( Role o : newRoles ) {
    277                            if ( o.equals( r ) ) {
    278                                System.out.println( "Role with id " + r.id + " and name '" + r.name
    279                                                    + "' is a duplicate of " + o.id + " with name '" + o.name + "'." );
    280                                break inner;
    281                            }
    282                        }
    283                    } else {
    284                        newRoles.add( r );
    285                    }
    286                }
    287
    288            } catch ( SQLException e ) {
    289                System.out.println( "Database access failed: " + e.getLocalizedMessage() );
    290                System.out.println( "Stack trace: " );
    291                e.printStackTrace();
    292            }
    293        }
    294
    295        static class Role {
    296            int id;
    297
    298            String name, title;
    299
    300            HashSet<Privilege> privileges = new HashSet<Privilege>();
    301
    302            HashSet<Right> rights = new HashSet<Right>();
    303
    304            @Override
    305            public boolean equals( Object o ) {
    306                if ( !( o instanceof Role ) ) {
    307                    return false;
    308                }
    309                Role r = (Role) o;
    310                return containsAllEqual( privileges, r.privileges ) && containsAllEqual( r.privileges, privileges )
    311                       && containsAllEqual( rights, r.rights ) && containsAllEqual( r.rights, rights );
    312            }
    313
    314            /**
    315             * Implementation as proposed by Joshua Block in Effective Java (Addison-Wesley 2001), which supplies an even
    316             * distribution and is relatively fast. It is created from field <b>f</b> as follows:
    317             * <ul>
    318             * <li>boolean -- code = (f ? 0 : 1)</li>
    319             * <li>byte, char, short, int -- code = (int)f</li>
    320             * <li>long -- code = (int)(f ^ (f &gt;&gt;&gt;32))</li>
    321             * <li>float -- code = Float.floatToIntBits(f);</li>
    322             * <li>double -- long l = Double.doubleToLongBits(f); code = (int)(l ^ (l &gt;&gt;&gt; 32))</li>
    323             * <li>all Objects, (where equals(&nbsp;) calls equals(&nbsp;) for this field) -- code = f.hashCode(&nbsp;)</li>
    324             * <li>Array -- Apply above rules to each element</li>
    325             * </ul>
    326             * <p>
    327             * Combining the hash code(s) computed above: result = 37 * result + code;
    328             * </p>
    329             *
    330             * @return (int) ( result >>> 32 ) ^ (int) result;
    331             *
    332             * @see java.lang.Object#hashCode()
    333             */
    334            @Override
    335            public int hashCode() {
    336                // the 2nd millionth prime, :-)
    337                long code = 32452843;
    338                code = id * 37 + code;
    339                for ( Privilege p : privileges ) {
    340                    code = p.hashCode() * 37 + code;
    341                }
    342                for ( Right r : rights ) {
    343                    code = r.hashCode() * 37 + code;
    344                }
    345                return (int) ( code >>> 32 ) ^ (int) code;
    346            }
    347
    348            @Override
    349            public String toString() {
    350                return generateToString( this );
    351            }
    352        }
    353
    354        static class Privilege {
    355            int id;
    356
    357            String constraint;
    358
    359            @Override
    360            public boolean equals( Object o ) {
    361                if ( !( o instanceof Privilege ) ) {
    362                    return false;
    363                }
    364                Privilege p = (Privilege) o;
    365                return id == p.id
    366                       && ( ( constraint == p.constraint ) || ( constraint != null && constraint.equals( p.constraint ) ) );
    367            }
    368
    369            /**
    370             * Implementation as proposed by Joshua Block in Effective Java (Addison-Wesley 2001), which supplies an even
    371             * distribution and is relatively fast. It is created from field <b>f</b> as follows:
    372             * <ul>
    373             * <li>boolean -- code = (f ? 0 : 1)</li>
    374             * <li>byte, char, short, int -- code = (int)f</li>
    375             * <li>long -- code = (int)(f ^ (f &gt;&gt;&gt;32))</li>
    376             * <li>float -- code = Float.floatToIntBits(f);</li>
    377             * <li>double -- long l = Double.doubleToLongBits(f); code = (int)(l ^ (l &gt;&gt;&gt; 32))</li>
    378             * <li>all Objects, (where equals(&nbsp;) calls equals(&nbsp;) for this field) -- code = f.hashCode(&nbsp;)</li>
    379             * <li>Array -- Apply above rules to each element</li>
    380             * </ul>
    381             * <p>
    382             * Combining the hash code(s) computed above: result = 37 * result + code;
    383             * </p>
    384             *
    385             * @return (int) ( result >>> 32 ) ^ (int) result;
    386             *
    387             * @see java.lang.Object#hashCode()
    388             */
    389            @Override
    390            public int hashCode() {
    391                // the 2nd millionth prime, :-)
    392                long code = 32452843;
    393                code = id * 37 + code;
    394                if ( constraint != null ) {
    395                    code = constraint.hashCode() * 37 + code;
    396                }
    397                return (int) ( code >>> 32 ) ^ (int) code;
    398            }
    399
    400            @Override
    401            public String toString() {
    402                return generateToString( this );
    403            }
    404        }
    405
    406        static class Right {
    407            int id;
    408
    409            int right;
    410
    411            String constraint;
    412
    413            @Override
    414            public boolean equals( Object o ) {
    415                if ( !( o instanceof Right ) ) {
    416                    return false;
    417                }
    418                Right r = (Right) o;
    419                return r.id == id && r.right == right
    420                       && ( ( constraint == r.constraint ) || ( constraint != null && constraint.equals( r.constraint ) ) );
    421            }
    422
    423            /**
    424             * Implementation as proposed by Joshua Block in Effective Java (Addison-Wesley 2001), which supplies an even
    425             * distribution and is relatively fast. It is created from field <b>f</b> as follows:
    426             * <ul>
    427             * <li>boolean -- code = (f ? 0 : 1)</li>
    428             * <li>byte, char, short, int -- code = (int)f</li>
    429             * <li>long -- code = (int)(f ^ (f &gt;&gt;&gt;32))</li>
    430             * <li>float -- code = Float.floatToIntBits(f);</li>
    431             * <li>double -- long l = Double.doubleToLongBits(f); code = (int)(l ^ (l &gt;&gt;&gt; 32))</li>
    432             * <li>all Objects, (where equals(&nbsp;) calls equals(&nbsp;) for this field) -- code = f.hashCode(&nbsp;)</li>
    433             * <li>Array -- Apply above rules to each element</li>
    434             * </ul>
    435             * <p>
    436             * Combining the hash code(s) computed above: result = 37 * result + code;
    437             * </p>
    438             *
    439             * @return (int) ( result >>> 32 ) ^ (int) result;
    440             *
    441             * @see java.lang.Object#hashCode()
    442             */
    443            @Override
    444            public int hashCode() {
    445                // the 2nd millionth prime, :-)
    446                long code = 32452843;
    447                code = id * 37 + code;
    448                code = right * 37 + code;
    449                if ( constraint != null ) {
    450                    code = constraint.hashCode() * 37 + code;
    451                }
    452                return (int) ( code >>> 32 ) ^ (int) code;
    453            }
    454
    455            @Override
    456            public String toString() {
    457                return generateToString( this );
    458            }
    459        }
    460
    461        private static void printUsage() {
    462            System.out.println( "Usage:" );
    463            System.out.println( "java -cp deegree2.jar org.deegree.tools.security.SecDBCleaner <command> <options>" );
    464            System.out.println();
    465            System.out.println( "Commands:" );
    466            System.out.println( "  checkRoles         - checks for duplicate roles" );
    467            System.out.println( "  checkLayers        - checks for existance of layers" );
    468            System.out.println( "  checkFeaturetypes  - checks for existance of feature types" );
    469            System.out.println();
    470            System.out.println( "Options:" );
    471            System.out.println( "  -d <jdbc URL>      -  database connection URL" );
    472            System.out.println( "  -u <DB username>   -  database user name" );
    473            System.out.println( "  -p <DB password>   -  database password" );
    474            System.out.println( "  -s <service URL>   -  service URL against which to check for layers/feature types (only for checkLayers/checkFeaturetypes)" );
    475            System.out.println();
    476            System.out.println( "Commands are case insensitive." );
    477            System.exit( 0 );
    478        }
    479
    480        /**
    481         * @param args
    482         */
    483        public static void main( String[] args ) {
    484
    485            if ( args.length == 0 ) {
    486                printUsage();
    487            }
    488
    489            String url = null, user = null, pass = "", service = null;
    490
    491            int pos = 1;
    492            while ( args.length > pos ) {
    493                if ( args[pos].equals( "-d" ) ) {
    494                    ++pos;
    495                    if ( args.length > pos ) {
    496                        url = args[pos];
    497                    }
    498                    ++pos;
    499                } else if ( args[pos].equals( "-u" ) ) {
    500                    ++pos;
    501                    if ( args.length > pos ) {
    502                        user = args[pos];
    503                    }
    504                    ++pos;
    505                } else if ( args[pos].equals( "-p" ) ) {
    506                    ++pos;
    507                    if ( args.length > pos ) {
    508                        pass = args[pos];
    509                    }
    510                    ++pos;
    511                } else if ( args[pos].equals( "-s" ) ) {
    512                    ++pos;
    513                    if ( args.length > pos ) {
    514                        service = args[pos];
    515                    }
    516                    ++pos;
    517                } else {
    518                    System.out.println( "Ignoring unknown parameter '" + args[pos++] + "'." );
    519                }
    520            }
    521
    522            if ( url == null ) {
    523                System.out.println( "Database URL must be given. PostGIS example: jdbc:postgresql://localhost/security" );
    524                printUsage();
    525            }
    526            if ( user == null ) {
    527                System.out.println( "Database user must be given." );
    528                printUsage();
    529            }
    530            if ( service == null
    531                 && ( args[0].equalsIgnoreCase( "checklayers" ) || args[0].equalsIgnoreCase( "checkfeaturetypes" ) ) ) {
    532                System.out.println( "Service must be given for checkLayers & checkFeaturetypes." );
    533                printUsage();
    534            }
    535
    536            SecDBCleaner cleaner = new SecDBCleaner( url, user, pass );
    537            if ( args[0].equalsIgnoreCase( "checkroles" ) ) {
    538                cleaner.findDuplicateRoles();
    539            } else if ( args[0].equalsIgnoreCase( "checklayers" ) ) {
    540                cleaner.checkLayers( service );
    541            } else if ( args[0].equalsIgnoreCase( "checkfeaturetypes" ) ) {
    542                cleaner.checkFeaturetypes( service );
    543            } else {
    544                System.out.println( "Unknown command '" + args[0] + "'" );
    545                printUsage();
    546            }
    547
    548            try {
    549                cleaner.stmt.close();
    550                cleaner.conn.close();
    551            } catch ( SQLException e ) {
    552                System.out.println( "Database access failed: " + e.getLocalizedMessage() );
    553                System.out.println( "Stack trace: " );
    554                e.printStackTrace();
    555            }
    556        }
    557
    558    }