|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
#!/bin/bash PREFIX= if [ -L "$0" ]; then PREFIX=`readlink -f $0` if [ $? -eq 0 ]; then PREFIX=`dirname $PREFIX` else PREFIX=`file $0` PREFIX=${PREFIX##*symbolic link to } PREFIX=`dirname $PREFIX` fi else PREFIX=`dirname $0` fi case $PREFIX in /*) ;; *) cd $PREFIX PREFIX=`pwd` ;; esac cp= for i in $PREFIX/lib/*.jar; do cp=$i:$cp done $PREFIX/java/bin/java \ -Djava.library.path=$PREFIX/swt -classpath $cp \ org.crazybus.login.Login & |
Category: CrazyBus
CrazyBus is a public transport management system I built with my classmates in 2007. Some of the documents are written in Chinese. It contains a Client/Server (C/S) system written in Java, and a Browser/Server (B/S) system written in JSP. The C/S module handles all the management tasks, while the B/S system handles users’ lookup requests and feedbacks and so on. The system uses Oracle 9i as database management system.
1.构造实现功能（发送消息）的存储过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE OR REPLACE PROCEDURE "ADMIN"."SEND_EMP_BIRTHDAY_MSG" is cursor emp_cursoris select eid, ename from admin.employee where to_char(sysdate, 'mm-dd') = to_char(birthdate, 'mm-dd'); eid number(5); ename varchar2(20); msgdesc varchar2(3000); begin msgdesc := ''; for rname in emp_cursor loop msgdesc := msgdesc || '\n员工编号： ' || rname.eid || ' 员工姓名： ' || rname.ename; end loop; if length(msgdesc) > 0 then msgdesc := '今天是以下员工的生日：\n' || msgdesc; insert into admin.messagevalues(admin.msgid_sequence.nextval, sysdate, '系统提示', 'admin', msgdesc, 1, 1, 0); dbms_output.put_line(msgdesc); end if; dbms_output.put_line(msgdesc); end; |
2.创建JOB：
|
1 2 3 4 5 |
variable job1 number; begin dbms_job.submit(:job1,'send_emp_birthday_msg;',sysdate,'sysdate+1'); --每天运行send_emp_birthday_msg过程一次 end; |
3. 运行JOB：
|
1 2 3 |
begin dbms_job.run(:job1); end; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/** * 查询信息 */ public void checkMessage(){ TimerTask tt = new TimerTask() { public void run() { String unreadMsgNo = new CheckMessage().getUnreadMsgNo (URL, USER, PASSWORD); if(unreadMsgNo.equals("0")){ jLabelMsgTip.setText("您没有新信息！"); } else{ jLabelMsgTip.setText("您有" + unreadMsgNo + "条新信息！"); } } }; Timer t = new Timer(); t.schedule(tt, 0, (long)1000*15);//每隔15秒执行一次 } |
VPD 即Virtual Private Databases，提供了对数据库的精密访问控制 (graind access control (FGAC) ），使用VPD,可以在数据记录集定义用户的访问权限。
通过VPD 策略，相当于用户操作数据库中的数据时隐式添加一些条件。比如我们要实现特定角色只能操作数据库表中的特定行的数据，这就可以用 VPD 策略实现。现在以车队长只能操作员工表中的属于他的车队的员工的数据信息为例具体说明：
1. 构造策略函数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE OR REPLACE FUNCTION "VPD"."FUN_MOTORCADE" ( p_schema in varchar2, p_table in varchar2 ) return varchar2 as l_retstr varchar2(100); eid number(5, 0); did number(5, 0); begin if lower(user)<> 'admin' then l_retstr := null; for user_rec in ( select eid, did from dpmanager where loginname = lower(user) ) loop eid := user_rec.eid; did := user_rec.did; end loop; l_retstr := 'did = ' || did ;--|| 'eid = ' || eid; return l_retstr; else l_retstr := null; return l_retstr; end if; end; |
2. 授予执行策略函数的权限：
|
1 |
grant execute on FUN_MOTORCADE to public; |
3. 向数据库表添加策略：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Begin Dbms_Rls.Add_Policy( Object_Schema =>'admin', --数据表(或视图)所在的Schema名称 Object_Name =>'employee', --数据表(或视图)的名称 Policy_Name =>'motorcade_ply', --POLICY的名称，主要用于将来对Policy的管理 Function_Schema =>'vpd', --返回Where子句的函数所在Schema名称 Policy_Function =>'fun_motorcade', --返回Where子句的函数名称 Statement_Types =>'Select,Insert,Update,Delete', --要使用该Policy的DML类型，如'Select,Insert,Update,Delete' Update_Check =>True, --仅适用于Statement_Type为'Insert,Update'，值为'True'或'False' Enable =>True --是否启用，值为'True'或'False' ); end; |
最开始不明白策略函数的作用，花了好几天上网查资料才知道原来函数的返回值便是你想往表上加的隐式约束。
比如，你向EMP表添加了下面的策略函数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR REPLACE FUNCTION "VPD"."TEST" ( p_schema in varchar2, p_table in varchar2 ) return varchar2 as l_retstr varchar2(100); begin if user = ‘admin’ then l_retstr := ‘1 = 1’; else l_retstr := ‘1 = 2’; end if; return l_retstr; end; |
现在，如果你以admin身份登录执行 ‘SELECT * FROM EMP’ , 则实际执行的是 ‘SELECT * FROM EMP WHERE 1 = 1’ , 其中 ‘1 = 1’ 这个条件是策略函数的返回值；如果你以其他身份登录执行 ‘SELECT * FROM EMP’ , 则实际执行的是 ‘SELECT * FROM EMP WHERE 1 = 2’ 。
Leave a CommentHere you can find the manual (in Chinese) for CrazyBus system.
Leave a CommentCrazyBus public transport management system is a system used to help public transport companies manage their daily affairs. The system contains several modules including employee management, department management, bus management, line and bus stop management and lookup, business achievement management, accident management, user management, ticket management, IC card management, and so on.
It contains a Client/Server (C/S) system written in Java, and a Browser/Server (B/S) system written in JSP. The C/S module handles all the management tasks, while the B/S system handles users’ lookup requests and feedbacks and so on. The system uses Oracle 9i as database management system.
