Oracle 第七天 权限系统

用户实际需求

1. 所有的用户、角色可动态配置
2. 所有的系统菜单的权限要求具体到“增，删，改、查、打印、导出”这样的小权限的设计
3. 所有的权限基于角色来进行划分和判断
4. 一个用户可能属于多个角色
5. 系统菜单也能够动态的“增、删、改、查”


[img]http://dl.iteye.com/upload/attachment/0072/6826/5b8f2e1c-5172-314c-a7db-23eae87cc731.jpg[/img]


T_User表
CREATE
TABLE T_USERS
(
USER_ID VARCHAR2(16) NOT NULL,
PASSWORD CHAR(8),
USER_NAME VARCHAR2(16),
PRIMARY KEY (USER_ID)
)

T_ROLE 表
CREATE
TABLE T_ROLE
(
ROLE_ID VARCHAR2(16) NOT NULL,
ROLE_DESCR VARCHAR2(32),
PRIMARY KEY (ROLE_ID)
)

CREATE
TABLE T_USER_ROLE
(
USER_ID VARCHAR2(16) NOT NULL,
ROLE_ID VARCHAR2(16) NOT NULL,
PRIMARY KEY (USER_ID, ROLE_ID),
CONSTRAINT FK_USER_ID FOREIGN KEY (USER_ID) REFERENCES T_USERS (USER_ID)
ON
DELETE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE,
CONSTRAINT FK_ROLE_ID FOREIGN KEY (ROLE_ID) REFERENCES T_ROLE (ROLE_ID)
ON
DELETE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE
)


CREATE
TABLE T_SYS_MENU
(
MENU_ID VARCHAR2(16) NOT NULL,
MENU_DESCR VARCHAR2(32),
MENU_URL VARCHAR2(255),
MENU_PID VARCHAR2(16),
PRIMARY KEY (MENU_ID)
)

CREATE
TABLE T_PRIVILEGE
(
PRIVILEGE_ID VARCHAR2(3) NOT NULL,
PRIVILEGE_TYPE VARCHAR2(8),
PRIMARY KEY (PRIVILEGE_ID)
)

CREATE
TABLE T_MENU_PRIVILEGE
(
PKID VARCHAR2(16) NOT NULL,
ROLE_ID VARCHAR2(16),
MENU_ID VARCHAR2(16),
PRIVILEGE_ID VARCHAR2(16),
PRIMARY KEY (PKID),
CONSTRAINT FK_ROLE_TO_PRIV FOREIGN KEY (ROLE_ID) REFERENCES T_ROLE (ROLE_ID)
ON
DELETE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE,
CONSTRAINT FK_PRIV FOREIGN KEY (PRIVILEGE_ID) REFERENCES T_PRIVILEGE (PRIVILEGE_ID)
ON
DELETE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE,
CONSTRAINT FK_MENU_TO_PRIV FOREIGN KEY (MENU_ID) REFERENCES T_SYS_MENU (MENU_ID)
ON
DELETE
CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE
)

插入数据

insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Tonny', 'abcdefg ', null);
insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Sammy', 'abcdefg ', null);
insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Sally', 'abcdefg ', null);
insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Jim', 'abcdefg ', null);
insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Danzel', 'abcdefg ', null);
insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Andy', 'abcdefg ', null);
insert into T_USERS (USER_ID, PASSWORD, USER_NAME) values ('Shawn', 'abcdefg ', null);


insert into T_ROLE (ROLE_ID, ROLE_DESCR) values ('user', '普通用户');
insert into T_ROLE (ROLE_ID, ROLE_DESCR) values ('manager', '部门经理');
insert into T_ROLE (ROLE_ID, ROLE_DESCR) values ('gmanager', '老总');
insert into T_ROLE (ROLE_ID, ROLE_DESCR) values ('admin', 'IT管理员');


insert into T_USER_ROLE (USER_ID, ROLE_ID) values ('Danzel', 'admin');
insert into T_USER_ROLE (USER_ID, ROLE_ID) values ('Danzel', 'manager');
insert into T_USER_ROLE (USER_ID, ROLE_ID) values ('Jim', 'admin');
insert into T_USER_ROLE (USER_ID, ROLE_ID) values ('Sammy', 'user');
insert into T_USER_ROLE (USER_ID, ROLE_ID) values ('Tonny', 'user');


insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('101', '报表查询', null, '0');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('102', '月报', 'xxx.do', '101');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('103', '季报', 'xxx.do', '101');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('104', '年报', 'xxx.do', '101');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('105', '系统管理', null, '0');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('106', '用户管理', null, '105');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('107', '新增用户', 'addUser.do', '106');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('108', '删除用户', 'delUser.do', '106');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('109', '角色管理', null, '105');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('110', '新增角色', 'addRole.do', '109');
insert into T_SYS_MENU (MENU_ID, MENU_DESCR, MENU_URL, MENU_PID) values ('111', '删除角色', 'delRole.do', '109');


insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE) values ('101', '增加');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE) values ('106', '报表');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE) values ('105', '打印');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE) values ('104', '查询');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE) values ('103', '修改');
insert into T_PRIVILEGE (PRIVILEGE_ID, PRIVILEGE_TYPE) values ('102', '删除');


insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID) values ('1', 'user', '101', '106');
insert into T_MENU_PRIVILEGE (PKID, ROLE_ID, MENU_ID, PRIVILEGE_ID) values ('2', 'user', '102', '106');

递归查询菜单
SELECT * FROM T_SYS_MENU START WITH MENU_PID = 0 CONNECT BY PRIOR MENU_ID = MENU_PID ORDER BY MENU_ID;


[img]http://dl.iteye.com/upload/attachment/0072/6832/13cfde86-c97a-32de-92f1-748534705d04.jpg[/img]

已经用数据查询用句就将我们的这个“树”的层次关系理出来了，如果我们手上有一个控件叫dtree.js，那么一个循环就可以把这个树显示出来了

[img]http://dl.iteye.com/upload/attachment/0072/6923/22c23859-23d6-3b0e-9a4b-dd61dcbe4284.jpg[/img]


查询用户所能看到的菜单和所具有的权限
select distinct m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type from
t_menu_privilege mp,
t_sys_menu m,
t_privilege p,
t_user_role r
where
mp.privilege_id=p.privilege_id
and mp.role_id=r.role_id
and mp.menu_id=m.menu_id
and r.role_id='user'
order by m.menu_id;

结果示例图
[img]http://dl.iteye.com/upload/attachment/0072/6835/c0670a5a-3ac9-38fa-9cef-7fdebce76420.jpg[/img]

还可参考
RBAC（Role-Based Access Control，基于角色的访问控制）

[img]http://dl.iteye.com/upload/attachment/0072/6915/c95a04d7-02c3-3a3e-87f7-02ea7e4adadf.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/0072/6917/aea7b2b0-5677-3b4a-9ebb-9c1f7d28fec0.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/0072/6919/0483c6e8-eee7-37d2-b90c-2ad8bc18dbf2.jpg[/img]


[img]http://dl.iteye.com/upload/attachment/0072/6921/3bc516ac-533e-3fe0-8dff-e854226fb96e.jpg[/img]