Oracle 第七天 权限系统

用户实际需求

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





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;




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





查询用户所能看到的菜单和所具有的权限
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;

结果示例图


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