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 }