001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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 >>>32))</li>
321 * <li>float -- code = Float.floatToIntBits(f);</li>
322 * <li>double -- long l = Double.doubleToLongBits(f); code = (int)(l ^ (l >>> 32))</li>
323 * <li>all Objects, (where equals( ) calls equals( ) for this field) -- code = f.hashCode( )</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 >>>32))</li>
376 * <li>float -- code = Float.floatToIntBits(f);</li>
377 * <li>double -- long l = Double.doubleToLongBits(f); code = (int)(l ^ (l >>> 32))</li>
378 * <li>all Objects, (where equals( ) calls equals( ) for this field) -- code = f.hashCode( )</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 >>>32))</li>
430 * <li>float -- code = Float.floatToIntBits(f);</li>
431 * <li>double -- long l = Double.doubleToLongBits(f); code = (int)(l ^ (l >>> 32))</li>
432 * <li>all Objects, (where equals( ) calls equals( ) for this field) -- code = f.hashCode( )</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 }