source: main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java@ 25092

Last change on this file since 25092 was 25092, checked in by sjm84, 12 years ago

Adding in security capabilities for Greenstone 3
File size: 11.5 KB
Line 
1/*
2 * DerbyWrapper.java
3 * Copyright (C) 2008 New Zealand Digital Library, http://www.nzdl.org
4 *
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 */
19package org.greenstone.gsdl3.util;
20
21import javax.swing.*;
22import java.io.File;
23import java.sql.Connection;
24import java.sql.DriverManager;
25import java.sql.ResultSet;
26import java.sql.SQLException;
27import java.sql.SQLWarning;
28import java.sql.Statement;
29import java.util.ArrayList;
30import java.util.HashMap;
31import java.util.Properties;
32
33public class DerbyWrapper
34{
35 static final String PROTOCOL = "jdbc:derby:";
36 static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
37 static final String USERSDB = "usersDB";
38 static final String USERS = "users";
39 static final String ROLES = "roles";
40 private Connection conn = null;
41 private Statement state = null;
42 private String protocol_str;
43
44 public DerbyWrapper()
45 {
46 }
47
48 public DerbyWrapper(String dbpath)
49 {
50 connectDatabase(dbpath, false);
51 }
52
53 public void connectDatabase(String dbpath, boolean create_database)
54 {
55 try
56 {
57 Class.forName(DRIVER).newInstance();
58 //System.out.println("Loaded the embedded driver.");
59 protocol_str = PROTOCOL + dbpath;
60 if (create_database)
61 {
62 conn = DriverManager.getConnection(protocol_str + ";create=true");
63 }
64 else
65 {
66 conn = DriverManager.getConnection(protocol_str);
67 }
68 state = conn.createStatement();
69 }
70 catch (Throwable e)
71 {
72 System.out.println("exception thrown:");
73 if (e instanceof SQLException)
74 {
75 printSQLError((SQLException) e);
76 }
77 else
78 {
79 e.printStackTrace();
80 }
81 }
82 }
83
84 public void closeDatabase()
85 {
86 state = null;
87 conn = null;
88 boolean gotSQLExc = false;
89 try
90 {
91 DriverManager.getConnection(protocol_str + ";shutdown=true");
92 }
93 catch (SQLException se)
94 {
95 gotSQLExc = true;
96 }
97 if (!gotSQLExc)
98 {
99 System.out.println("Database did not shut down normally");
100 }
101 }
102
103 public void createDatabase()
104 {
105 try
106 {
107 conn.setAutoCommit(false);
108 state.execute("create table users (username varchar(40) not null, password varchar(40) not null, accountstatus varchar(10), comment varchar(100), primary key(username))");
109 state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))");
110 //ystem.out.println("table users created successfully!");
111 state.execute("insert into " + USERS + " values ('admin', 'admin', 'true', 'change the password for this account as soon as possible')");
112 state.execute("insert into " + ROLES + " values ('admin', 'administrator')");
113 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')");
114 conn.commit();
115 }
116 catch(Exception ex)
117 {
118 ex.printStackTrace();
119 }
120 }
121
122 public UserQueryResult listAllUser() throws SQLException
123 {
124 UserQueryResult userQueryResult = new UserQueryResult();
125 String sql_list_all_user = "SELECT username, password, accountstatus, comment FROM " + USERS;
126
127 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String,String>>();
128 ResultSet rs = state.executeQuery(sql_list_all_user);
129 while (rs.next())
130 {
131 HashMap<String, String> user = new HashMap<String, String>();
132 user.put("username", rs.getString("username"));
133 user.put("password", rs.getString("password"));
134 user.put("as", rs.getString("accountstatus"));
135 user.put("comment", rs.getString("comment"));
136
137 users.add(user);
138 }
139
140 for(HashMap<String, String> user : users)
141 {
142 ResultSet gs = state.executeQuery("SELECT role FROM roles WHERE username = '" + user.get("username") + "'");
143 String group = "";
144 while(gs.next())
145 {
146 if(!group.equals(""))
147 {
148 group += ",";
149 }
150 group += gs.getString("role");
151 }
152 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"));
153 }
154
155 if (userQueryResult.getSize() == 0)
156 {
157 System.out.println("couldn't find any users");
158 return null;
159 }
160 else
161 {
162 return userQueryResult;
163 }
164 }
165
166 public String addUser(String username, String password, String groups, String accountstatus, String comment)
167 {
168 try
169 {
170 conn.setAutoCommit(false);
171 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "')";
172 state.execute(sql_insert_user);
173
174 String[] groupArray = groups.split(",");
175 for(String g : groupArray)
176 {
177 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
178 state.execute(sql_insert_group);
179 }
180
181 conn.commit();
182 }
183 catch (Throwable e)
184 {
185 System.out.println("exception thrown:");
186 if (e instanceof SQLException)
187 {
188 printSQLError((SQLException) e);
189 }
190 else
191 {
192 e.printStackTrace();
193 }
194 closeDatabase();
195 System.out.println("Error:" + e.getMessage());
196 return "Error:" + e.getMessage();
197 }
198
199 return "succeed";
200 }
201
202 public String deleteUser(String del_username)
203 {
204 try
205 {
206 conn.setAutoCommit(false);
207 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
208 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'";
209 state.execute(sql_delete_user);
210 state.execute(sql_delete_groups);
211 conn.commit();
212 }
213 catch (Throwable e)
214 {
215 System.out.println("exception thrown:");
216 if (e instanceof SQLException)
217 {
218 printSQLError((SQLException) e);
219 }
220 else
221 {
222 e.printStackTrace();
223 }
224 closeDatabase();
225 return "Error:" + e.getMessage();
226 }
227 return "succeed";
228 }
229
230 public boolean deleteAllUser() throws SQLException
231 {
232 conn.setAutoCommit(false);
233 try
234 {
235 state.execute("delete from " + USERS);
236 state.execute("delete from " + ROLES);
237 conn.commit();
238 }
239 catch (Throwable e)
240 {
241 System.out.println("exception thrown:");
242 if (e instanceof SQLException)
243 {
244 printSQLError((SQLException) e);
245 }
246 else
247 {
248 e.printStackTrace();
249 }
250 closeDatabase();
251 return false;
252 }
253 return true;
254 }
255
256 public UserQueryResult findUser(String username, String password) throws SQLException
257 {
258 UserQueryResult userQueryResult = new UserQueryResult();
259
260 conn.setAutoCommit(false);
261 String sql_find_user = "SELECT username, password, accountstatus, comment FROM " + USERS;
262 String append_sql = "";
263
264 if (username != null)
265 {
266 append_sql = " WHERE username = '" + username + "'";
267 }
268 if (password != null)
269 {
270 if (append_sql.equals(""))
271 {
272 append_sql = " WHERE password = '" + password + "'";
273 }
274 else
275 {
276 append_sql += " and password = '" + password + "'";
277 }
278 }
279 if (!append_sql.equals(""))
280 {
281 sql_find_user += append_sql;
282 }
283
284 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String,String>>();
285 ResultSet rs = state.executeQuery(sql_find_user);
286 while (rs.next())
287 {
288 HashMap<String, String> user = new HashMap<String, String>();
289 user.put("username", rs.getString("username"));
290 user.put("password", rs.getString("password"));
291 user.put("as", rs.getString("accountstatus"));
292 user.put("comment", rs.getString("comment"));
293
294 users.add(user);
295 }
296 conn.commit();
297
298 for(HashMap<String, String> user : users)
299 {
300 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
301
302 String group = "";
303 while(gs.next())
304 {
305 if(!group.equals(""))
306 {
307 group += ",";
308 }
309 group += gs.getString("role");
310 }
311
312 System.out.println("GROUP = " + group);
313
314 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"));
315 }
316
317 if (userQueryResult.getSize() > 0)
318 {
319 return userQueryResult;
320 }
321 else
322 {
323 System.out.println("couldn't find the user");
324 return null;
325 }
326 }
327
328 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment)
329 {
330 try
331 {
332 conn.setAutoCommit(false);
333 String sql_modify_user_info = "update " + USERS + " set ";
334 if (new_password != null && !new_password.equals(""))
335 {
336 sql_modify_user_info += "password='" + new_password + "'";
337 }
338
339 if (accountstatus != null && comment != null)
340 {
341 sql_modify_user_info += ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
342 }
343 sql_modify_user_info += " where username='" + username + "'";
344 System.out.println(sql_modify_user_info);
345 state.execute(sql_modify_user_info);
346
347 String sql_delete_groups = "delete from " + ROLES + " where username='" + username + "'";
348 state.execute(sql_delete_groups);
349
350 String[] groupsArray = groups.split(",");
351 for(String g : groupsArray)
352 {
353 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
354 state.execute(sql_insert_group);
355 }
356
357 conn.commit();
358 }
359 catch (Throwable e)
360 {
361 System.out.println("exception thrown:");
362 if (e instanceof SQLException)
363 {
364 printSQLError((SQLException) e);
365 }
366 else
367 {
368 e.printStackTrace();
369 }
370 closeDatabase();
371 return "Error:" + e.getMessage();
372 }
373 return "succeed";
374 }
375
376 public void db2txt() throws SQLException
377 {
378 UserQueryResult userQueryResult = new UserQueryResult();
379 String sql_list_all_user = "select username, password, accountstatus, comment from " + USERS;
380 ResultSet rs = state.executeQuery(sql_list_all_user);
381
382 while (rs.next())
383 {
384 String returned_username = rs.getString("username");
385 System.out.println("[" + returned_username + "]");
386 String returned_comment = rs.getString("comment");
387 System.out.println("<comment>" + returned_comment);
388 String returned_accountstatus = rs.getString("accountstatus");
389 System.out.println("<enabled>" + returned_accountstatus);
390 ResultSet groupsSet = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + returned_username + "'");
391 String returned_groups = "";
392 while(groupsSet.next())
393 {
394 if(!returned_groups.equals(""))
395 {
396 returned_groups += ",";
397 }
398 returned_groups += groupsSet.getString("role");
399 }
400 System.out.println("<groups>" + returned_groups);
401 String returned_password = rot13(rs.getString("password"));
402 System.out.println("<password>" + returned_password);
403 System.out.println("<username>" + returned_username);
404 System.out.println("");
405 System.out.println("----------------------------------------------------------------------");
406 }
407 conn.commit();
408 closeDatabase();
409 }
410
411 static void printSQLError(SQLException e)
412 {
413 while (e != null)
414 {
415 System.out.println(e.toString());
416 e = e.getNextException();
417 }
418 }
419
420 //Simply use rot-13 to encrypt and decrypt the password
421 public String rot13(String password)
422 {
423 String out_password = "";
424 for (int i = 0; i < password.length(); i++)
425 {
426 char c = password.charAt(i);
427 if (c >= 'a' && c <= 'm')
428 c += 13;
429 else if (c >= 'n' && c <= 'z')
430 c -= 13;
431 else if (c >= 'A' && c <= 'M')
432 c += 13;
433 else if (c >= 'A' && c <= 'Z')
434 c -= 13;
435 out_password += c;
436 }
437 return out_password;
438 }
439}
Note: See TracBrowser for help on using the repository browser.