001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_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: 20898 $, $Date: 2009-11-19 16:36:00 +0100 (Do, 19. Nov 2009) $ 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 @Override 315 public String toString() { 316 return generateToString( this ); 317 } 318 } 319 320 static class Privilege { 321 int id; 322 323 String constraint; 324 325 @Override 326 public boolean equals( Object o ) { 327 if ( !( o instanceof Privilege ) ) { 328 return false; 329 } 330 Privilege p = (Privilege) o; 331 return id == p.id 332 && ( ( constraint == p.constraint ) || ( constraint != null && constraint.equals( p.constraint ) ) ); 333 } 334 335 @Override 336 public String toString() { 337 return generateToString( this ); 338 } 339 } 340 341 static class Right { 342 int id; 343 344 int right; 345 346 String constraint; 347 348 @Override 349 public boolean equals( Object o ) { 350 if ( !( o instanceof Right ) ) { 351 return false; 352 } 353 Right r = (Right) o; 354 return r.id == id && r.right == right 355 && ( ( constraint == r.constraint ) || ( constraint != null && constraint.equals( r.constraint ) ) ); 356 } 357 358 @Override 359 public String toString() { 360 return generateToString( this ); 361 } 362 } 363 364 private static void printUsage() { 365 System.out.println( "Usage:" ); 366 System.out.println( "java -cp deegree2.jar org.deegree.tools.security.SecDBCleaner <command> <options>" ); 367 System.out.println(); 368 System.out.println( "Commands:" ); 369 System.out.println( " checkRoles - checks for duplicate roles" ); 370 System.out.println( " checkLayers - checks for existance of layers" ); 371 System.out.println( " checkFeaturetypes - checks for existance of feature types" ); 372 System.out.println(); 373 System.out.println( "Options:" ); 374 System.out.println( " -d <jdbc URL> - database connection URL" ); 375 System.out.println( " -u <DB username> - database user name" ); 376 System.out.println( " -p <DB password> - database password" ); 377 System.out.println( " -s <service URL> - service URL against which to check for layers/feature types (only for checkLayers/checkFeaturetypes)" ); 378 System.out.println(); 379 System.out.println( "Commands are case insensitive." ); 380 System.exit( 0 ); 381 } 382 383 /** 384 * @param args 385 */ 386 public static void main( String[] args ) { 387 388 if ( args.length == 0 ) { 389 printUsage(); 390 } 391 392 String url = null, user = null, pass = "", service = null; 393 394 int pos = 1; 395 while ( args.length > pos ) { 396 if ( args[pos].equals( "-d" ) ) { 397 ++pos; 398 if ( args.length > pos ) { 399 url = args[pos]; 400 } 401 ++pos; 402 } else if ( args[pos].equals( "-u" ) ) { 403 ++pos; 404 if ( args.length > pos ) { 405 user = args[pos]; 406 } 407 ++pos; 408 } else if ( args[pos].equals( "-p" ) ) { 409 ++pos; 410 if ( args.length > pos ) { 411 pass = args[pos]; 412 } 413 ++pos; 414 } else if ( args[pos].equals( "-s" ) ) { 415 ++pos; 416 if ( args.length > pos ) { 417 service = args[pos]; 418 } 419 ++pos; 420 } else { 421 System.out.println( "Ignoring unknown parameter '" + args[pos++] + "'." ); 422 } 423 } 424 425 if ( url == null ) { 426 System.out.println( "Database URL must be given. PostGIS example: jdbc:postgresql://localhost/security" ); 427 printUsage(); 428 } 429 if ( user == null ) { 430 System.out.println( "Database user must be given." ); 431 printUsage(); 432 } 433 if ( service == null 434 && ( args[0].equalsIgnoreCase( "checklayers" ) || args[0].equalsIgnoreCase( "checkfeaturetypes" ) ) ) { 435 System.out.println( "Service must be given for checkLayers & checkFeaturetypes." ); 436 printUsage(); 437 } 438 439 SecDBCleaner cleaner = new SecDBCleaner( url, user, pass ); 440 if ( args[0].equalsIgnoreCase( "checkroles" ) ) { 441 cleaner.findDuplicateRoles(); 442 } else if ( args[0].equalsIgnoreCase( "checklayers" ) ) { 443 cleaner.checkLayers( service ); 444 } else if ( args[0].equalsIgnoreCase( "checkfeaturetypes" ) ) { 445 cleaner.checkFeaturetypes( service ); 446 } else { 447 System.out.println( "Unknown command '" + args[0] + "'" ); 448 printUsage(); 449 } 450 451 try { 452 cleaner.stmt.close(); 453 cleaner.conn.close(); 454 } catch ( SQLException e ) { 455 System.out.println( "Database access failed: " + e.getLocalizedMessage() ); 456 System.out.println( "Stack trace: " ); 457 e.printStackTrace(); 458 } 459 } 460 461 }