Source code: mill/a3/AuthTools.java
1 package mill.a3;
2
3 import java.sql.*;
4 import java.util.*;
5 import java.io.*;
6
7 import mill.db.DBconnect;
8 import mill.tools.StringTools;
9 import mill.tools.RsetTools;
10
11 public class AuthTools
12 {
13
14 public static boolean checkRigthOnUser(DBconnect db_,
15 long id_auth_user_check, long id_auth_user_owner)
16 throws AuthException
17 {
18 PreparedStatement ps = null;
19 ResultSet rs = null;
20 try
21 {
22 ps = db_.conn.prepareStatement(
23
24 "select null "+
25 "from AUTH_USER a, main_user_info b, v$_read_list_firm z1 "+
26 "where a.id_user = b.id_user and "+
27 " a.id_auth_user=? and "+
28 " b.id_firm = z1.id_firm and "+
29 " z1.id_auth_user = ? "
30 );
31
32 ps.setLong(1, id_auth_user_check);
33 ps.setLong(2, id_auth_user_owner);
34 rs = ps.executeQuery();
35
36 return rs.next();
37 }
38 catch(Exception e){
39 throw new AuthException( e.toString() );
40 }
41 finally{
42 if (rs != null)
43 {
44 try{
45 rs.close();
46 rs = null;
47 }catch(Exception e01){}
48 }
49 if (ps != null)
50 {
51 try{
52 ps.close();
53 ps = null;
54 }catch(Exception e02){}
55 }
56 }
57 }
58
59 public static long addRole(DBconnect ora_, String role_name)
60 throws AuthException
61 {
62 PreparedStatement ps = null;
63 try
64 {
65 long id = ora_.getSequenceNextValue("seq_auth_access_group");
66 if (id == -1)
67 return id;
68
69 String sql_ =
70 "insert into auth_access_group "+
71 "( ID_ACCESS_GROUP, NAME_ACCESS_GROUP ) "+
72 "( select ?, ? from dual ) ";
73
74 ps = ora_.conn.prepareStatement( sql_ );
75
76 ps.setLong(1, id);
77 ps.setString(2, StringTools.toDB(role_name) );
78 int i = ps.executeUpdate();
79 return id;
80 }
81 catch(Exception e){
82 throw new AuthException( e.toString() );
83 }
84 finally{
85 if (ps != null)
86 {
87 try{
88 ps.close();
89 ps = null;
90 }catch(Exception e02){}
91 }
92 }
93 }
94
95 public static long checkWithCreateRole(DBconnect ora_, String role_name)
96 throws AuthException
97 {
98
99 long id_group = getIDRole(ora_, role_name);
100 if (id_group == -1)
101 id_group = addRole(ora_, role_name);
102
103 return id_group;
104
105 }
106
107 public static long getUserRole(DBconnect ora_, long id_auth_user,
108 long id_role)
109 throws AuthException
110 {
111 PreparedStatement ps = null;
112 ResultSet rs = null;
113 try
114 {
115 ps = ora_.conn.prepareStatement(
116 "select id_relate_accgroup from auth_relate_accgroup \n"+
117 "where id_auth_user = ? and id_access_group = ? \n"
118 );
119 ps.setLong(1, id_auth_user);
120 ps.setLong(2, id_role);
121 rs = ps.executeQuery();
122
123 long result = -1;
124 if ( rs.next())
125 result = RsetTools.getRsetLong(rs, "id_relate_accgroup");
126
127 return result;
128 }
129 catch(Exception e){
130 throw new AuthException( e.toString() );
131 }
132 finally{
133 if (rs != null)
134 {
135 try{
136 rs.close();
137 rs = null;
138 }catch(Exception e01){}
139 }
140 if (ps != null)
141 {
142 try{
143 ps.close();
144 ps = null;
145 }catch(Exception e02){}
146 }
147 }
148 }
149
150 public static long getUserRole(DBconnect ora_, long id_user,
151 String role_name)
152 throws AuthException
153 {
154 long id_role = getIDRole(ora_, role_name);
155 if (id_role == -1)
156 return -1;
157
158 return getUserRole(ora_, id_user, id_role);
159 }
160
161 public static boolean bindUserRole(DBconnect ora_, long id_auth_user,
162 String role_name)
163 throws AuthException
164 {
165 long id_role = checkWithCreateRole(ora_, role_name);
166 return bindUserRole(ora_, id_auth_user, id_role);
167 }
168
169 public static boolean bindUserRole(DBconnect ora_, long id_auth_user,
170 long id_role)
171 throws AuthException
172 {
173 PreparedStatement ps = null;
174 try
175 {
176 ps = ora_.conn.prepareStatement(
177 "insert into auth_relate_accgroup "+
178 "(id_relate_accgroup, id_auth_user, id_access_group) "+
179 "(select seq_auth_relate_accgroup.nextval, ?, ? from dual ) "
180 );
181 ps.setLong(1, id_auth_user);
182 ps.setLong(2, id_role);
183 int i = ps.executeUpdate();
184 }
185 catch(Exception e) {
186 throw new AuthException(e.toString());
187 }
188 finally{
189 if (ps != null)
190 {
191 try{
192 ps.close();
193 ps = null;
194 }catch(Exception e02){}
195 }
196 }
197 return true;
198 }
199
200 public static long addUserAuth(DBconnect ora_, long id_user,
201 long id_firm, long id_service, long id_road,
202 String username, String password,
203 boolean isFirm, boolean isService, boolean isRoad)
204 throws AuthException
205 {
206
207 PreparedStatement ps = null;
208 try
209 {
210 long id_auth_user =
211 ora_.getSequenceNextValue( "seq_auth_user" );
212
213 ps = ora_.conn.prepareStatement(
214 "insert into auth_user "+
215 "(id_auth_user, id_user, id_firm, \n"+
216 "user_login, user_password, \n"+
217 "is_service, is_road, is_use_current_firm, \n"+
218 "is_root, id_road, id_service) \n"+
219 "(select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual )"
220 );
221
222 ps.setLong(1, id_auth_user);
223 ps.setLong(2, id_user);
224 ps.setLong(3, id_firm);
225 ps.setString(4, StringTools.toDB(username) );
226 ps.setString(5, StringTools.toDB(password) );
227 ps.setInt(6, isService?1:0);
228 ps.setInt(7, isRoad?1:0);
229 ps.setInt(8, isFirm?1:0);
230 ps.setInt(9, 0);
231 ps.setLong(10, id_road);
232 ps.setLong(11, id_service);
233
234 int i = ps.executeUpdate();
235
236 return id_auth_user;
237 }
238 catch(Exception e){
239 throw new AuthException( e.toString() );
240 }
241 finally{
242 if (ps != null)
243 {
244 try{
245 ps.close();
246 ps = null;
247 }catch(Exception e02){}
248 }
249 }
250 }
251
252
253 public static int setUserAuthRole(DBconnect ora_, long id_user,
254 String firm_name, String service_name, String road_name,
255 String username, String password,
256 boolean isFirm, boolean isService, boolean isRoad,
257 String role_name)
258 throws AuthException
259 {
260
261 long id_firm = getIDFirm(ora_, firm_name);
262 if (id_firm == -1)
263 return -1;
264
265 long id_service = getIDService(ora_, service_name);
266 if (id_service == -1)
267 return -2;
268
269 long id_road = getIDRoad(ora_, road_name);
270 if (id_road == -1)
271 return -3;
272
273 long id_auth_user = addUserAuth(ora_, id_user,
274 id_firm, id_service, id_road,
275 username, password,
276 isFirm, isService, isRoad);
277
278 if (!bindUserRole(ora_, id_auth_user, role_name))
279 return -4;
280
281 return 0;
282 }
283
284 public static void setRelateServiceFirm(DBconnect ora_, long id_service, long id_firm)
285 throws AuthException
286 {
287 PreparedStatement ps = null;
288 try{
289 if (!getRelateServiceFirm(ora_, id_firm, id_service))
290 {
291 ps = ora_.conn.prepareStatement(
292 "insert into main_relate_service_firm "+
293 "(id_rel_service, id_service, id_firm) "+
294 "(select seq_main_relate_service_firm.nextval, ?, ? from dual )"
295 );
296 ps.setLong(1, id_service);
297 ps.setLong(2, id_firm);
298 int i = ps.executeUpdate();
299 }
300 }
301 catch(Exception e){
302 throw new AuthException( e.toString() );
303 }
304 finally{
305 if (ps != null)
306 {
307 try{
308 ps.close();
309 ps = null;
310 }catch(Exception e02){}
311 }
312 }
313
314 }
315
316 public static void setRelateServiceFirm(DBconnect ora_, String service_name, String firm_name)
317 throws AuthException
318 {
319 setRelateServiceFirm(ora_, service_name, firm_name, false, false);
320 }
321
322 public static void setRelateServiceFirm(DBconnect ora_, String service_name, String firm_name, boolean add_service, boolean add_firm)
323 throws AuthException
324 {
325 long id_firm = getIDFirm(ora_, firm_name);
326 if ((id_firm == -1) && (add_firm))
327 id_firm = addNewFirm(ora_, firm_name);
328 else
329 return;
330
331 long id_service = getIDService(ora_, service_name);
332 if ((id_service == -1) && (add_service))
333 id_service = addNewService(ora_, service_name);
334 else
335 return;
336
337 setRelateServiceFirm(ora_, id_service, id_firm);
338 }
339
340 public static long addNewUser(DBconnect ora_, String first_name,
341 String last_name, String middle_name, String firm_name,
342 String service_name, String email, String address,
343 String phone)
344 throws AuthException
345 {
346
347 setRelateServiceFirm(ora_, service_name, firm_name, true, true);
348
349 return addNewUser(ora_, first_name, last_name, middle_name,
350 getIDFirm(ora_, firm_name), email, address, phone);
351 }
352
353
354 public static long addNewUser(DBconnect ora_, String first_name,
355 String last_name, String middle_name, long id_firm,
356 String email, String address, String phone)
357 throws AuthException
358 {
359 PreparedStatement ps = null;
360 try{
361 long id =
362 ora_.getSequenceNextValue( "seq_main_user_info" );
363
364 ps = ora_.conn.prepareStatement(
365 "insert into main_user_info \n"+
366 "(id_user, first_name, last_name, middle_name, \n"+
367 " id_firm, email, address, telephone, \n"+
368 " date_start_work ) \n"+
369 "(select ?, ?, ?, ?, ?, ?, ?, ?, sysdate from dual ) "
370 );
371 ps.setLong(1, id);
372 ps.setString(2, StringTools.toDB( first_name, 50));
373 ps.setString(3, StringTools.toDB(last_name, 50));
374 ps.setString(4, StringTools.toDB(middle_name, 50));
375 ps.setLong(5, id_firm);
376 ps.setString(6, StringTools.toDB(email, 30));
377 ps.setString(7, StringTools.toDB(address, 50));
378 ps.setString(8, StringTools.toDB(phone, 25));
379 int i = ps.executeUpdate();
380 return id;
381 }
382 catch(Exception e){
383 throw new AuthException( e.toString() );
384 }
385 finally{
386 if (ps != null)
387 {
388 try{
389 ps.close();
390 ps = null;
391 }catch(Exception e02){}
392 }
393 }
394 }
395
396
397 public static long addNewFirm(DBconnect ora_, String nameFirm)
398 throws AuthException
399 {
400 PreparedStatement ps = null;
401 try{
402 long ID =
403 ora_.getSequenceNextValue( "seq_main_list_firm" );
404
405 ps = ora_.conn.prepareStatement(
406 "insert into main_list_firm "+
407 "(id_firm, full_name, short_name) "+
408 "(select ?, ?, ? from dual )"
409 );
410 ps.setLong(1, ID);
411 ps.setString(2, StringTools.toDB(nameFirm, 500));
412 ps.setString(3, StringTools.toDB(nameFirm, 125));
413 int i = ps.executeUpdate();
414 return ID;
415 }
416 catch(Exception e){
417 throw new AuthException( e.toString() );
418 }
419 finally{
420 if (ps != null)
421 {
422 try{
423 ps.close();
424 ps = null;
425 }catch(Exception e02){}
426 }
427 }
428 }
429
430 public static long addNewService(DBconnect ora_, String nameService)
431 throws AuthException
432 {
433 PreparedStatement ps = null;
434 try{
435 long ID =
436 ora_.getSequenceNextValue( "seq_main_list_service" );
437
438 ps = ora_.conn.prepareStatement(
439 "insert into main_list_service "+
440 "(id_service, full_name_service, short_name_service) "+
441 "(select ?, ?, ? from dual ) "
442 );
443 ps.setLong(1, ID);
444 ps.setString(2, StringTools.toDB(nameService, 100));
445 ps.setString(3, StringTools.toDB(nameService, 11));
446 int i = ps.executeUpdate();
447 return ID;
448 }
449 catch(Exception e){
450 throw new AuthException( e.toString() );
451 }
452 finally{
453 if (ps != null)
454 {
455 try{
456 ps.close();
457 ps = null;
458 }catch(Exception e02){}
459 }
460 }
461 }
462
463 public static long addNewRoad(DBconnect ora_, String nameRoad)
464 throws AuthException
465 {
466 PreparedStatement ps = null;
467 try{
468 long ID =
469 ora_.getSequenceNextValue( "seq_main_list_road" );
470
471 ps = ora_.conn.prepareStatement(
472 "insert into main_list_road \n"+
473 "(id_road, full_name_road, name_road) \n"+
474 "(select ?, ?, ? from dual ) "
475 );
476 ps.setLong(1, ID);
477 ps.setString(2, StringTools.toDB(nameRoad, 100));
478 ps.setString(3, StringTools.toDB(nameRoad, 10));
479 int i = ps.executeUpdate();
480 return ID;
481 }
482 catch(Exception e){
483 throw new AuthException( e.toString() );
484 }
485 finally{
486 if (ps != null)
487 {
488 try{
489 ps.close();
490 ps = null;
491 }catch(Exception e02){}
492 }
493 }
494 }
495
496 public static long getIDFirm(DBconnect ora_, String nameFirm)
497 throws AuthException
498 {
499 PreparedStatement ps = null;
500 ResultSet rs = null;
501 try
502 {
503 ps = ora_.conn.prepareStatement(
504 "select id_firm from main_list_firm "+
505 "where short_name = ? "
506 );
507 ps.setString(1, StringTools.toDB(nameFirm) );
508 rs = ps.executeQuery();
509 long id = -1;
510 if (rs.next())
511 id = RsetTools.getRsetLong(rs, "id_firm", -1);
512
513 return id;
514 }
515 catch(Exception e){
516 throw new AuthException( e.toString() );
517 }
518 finally{
519 if (rs != null)
520 {
521 try{
522 rs.close();
523 rs = null;
524 }catch(Exception e02){}
525 }
526 if (ps != null)
527 {
528 try{
529 ps.close();
530 ps = null;
531 }catch(Exception e02){}
532 }
533 }
534 }
535
536 public static long getIDService(DBconnect ora_, String nameService)
537 throws AuthException
538 {
539 PreparedStatement ps = null;
540 ResultSet rs = null;
541 try
542 {
543 ps = ora_.conn.prepareStatement(
544 "select id_service from main_list_service \n"+
545 "where short_name_service = ? \n"
546 );
547 ps.setString(1, StringTools.toDB(nameService) );
548 rs = ps.executeQuery();
549 long id = -1;
550 if (rs.next())
551 id = RsetTools.getRsetLong(rs, "id_service", -1);
552
553 return id;
554 }
555 catch(Exception e){
556 throw new AuthException( e.toString() );
557 }
558 finally{
559 if (rs != null)
560 {
561 try{
562 rs.close();
563 rs = null;
564 }catch(Exception e02){}
565 }
566 if (ps != null)
567 {
568 try{
569 ps.close();
570 ps = null;
571 }catch(Exception e02){}
572 }
573 }
574 }
575
576 public static long getIDRoad(DBconnect ora_, String nameRoad)
577 throws AuthException
578 {
579 PreparedStatement ps = null;
580 ResultSet rs = null;
581 try
582 {
583 ps = ora_.conn.prepareStatement(
584 "select id_road from main_list_road "+
585 "where name_road = ? "
586 );
587 ps.setString(1, StringTools.toDB(nameRoad) );
588 rs = ps.executeQuery();
589 long id = -1;
590 if (rs.next())
591 id = RsetTools.getRsetLong(rs, "id_road", -1);
592
593 return id;
594 }
595 catch(Exception e){
596 throw new AuthException( e.toString() );
597 }
598 finally{
599 if (rs != null)
600 {
601 try{
602 rs.close();
603 rs = null;
604 }catch(Exception e02){}
605 }
606 if (ps != null)
607 {
608 try{
609 ps.close();
610 ps = null;
611 }catch(Exception e02){}
612 }
613 }
614 }
615
616
617 public static boolean getRelateServiceFirm(DBconnect ora_,
618 long id_firm, long id_service)
619 throws AuthException
620 {
621 PreparedStatement ps = null;
622 ResultSet rs = null;
623 try
624 {
625 ps = ora_.conn.prepareStatement(
626 "select null count_rec from main_relate_service_firm \n"+
627 "where id_firm = ? and id_service = ? \n"
628 );
629 ps.setLong(1, id_firm);
630 ps.setLong(2, id_service);
631 rs = ps.executeQuery();
632
633 return rs.next();
634 }
635 catch(Exception e){
636 throw new AuthException( e.toString() );
637 }
638 finally{
639 if (rs != null)
640 {
641 try{
642 rs.close();
643 rs = null;
644 }catch(Exception e01){}
645 }
646 if (ps != null)
647 {
648 try{
649 ps.close();
650 ps = null;
651 }catch(Exception e02){}
652 }
653 }
654 }
655
656 public static long getIDRole(DBconnect ora_, String role_name)
657 throws AuthException
658 {
659 PreparedStatement ps = null;
660 ResultSet rs = null;
661 try
662 {
663 ps = ora_.conn.prepareStatement(
664 "select id_access_group from auth_access_group "+
665 "where name_access_group = ? "
666 );
667 ps.setString(1, StringTools.toDB(role_name) );
668 rs = ps.executeQuery();
669 long id = -1;
670 if (rs.next())
671 id = RsetTools.getRsetLong(rs, "id_access_group", -1);
672
673 return id;
674 }
675 catch(Exception e){
676 throw new AuthException( e.toString() );
677 }
678 finally{
679 if (rs != null)
680 {
681 try{
682 rs.close();
683 rs = null;
684 }catch(Exception e02){}
685 }
686 if (ps != null)
687 {
688 try{
689 ps.close();
690 ps = null;
691 }catch(Exception e02){}
692 }
693 }
694 }
695
696 }