基于角色的权限管理系统,使用指南

本后面系统是基于角色的权限管理系统

简单解释就是:

角色可以理解成一个公司里的“职务”(总经理,部门主管,员工)都是一种职称,每种职称都代表了一些权限,比如:主管可以管理员工,反之则不行。

 

我们以题库总负责人为例:

1、新建角色:题库总负责人


2、为角色分配权限


分配了“后台管理”是因为,该角色可以新建下属员工的。

 

 

3、新建用户



点击分配角色:


选择题库总负责人,后保存

 

这就完成。

 

备注:

1、不同的普通用户可以有同样的角色。所以,角色是一类人的行为概括。

一个用户可以有多个角色。

 附相关表:

--用户表
declare num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_USER';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_USER 
(
   ID                   NUMBER(19)           not null,
   USER_NAME            NVARCHAR2(50),
   PASSWORD             NVARCHAR2(50),
   CONTACT              NVARCHAR2(100),
   CREATE_TIME          TIMESTAMP,
   constraint PK_R_RESOURCE_USER primary key (ID)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/

comment on table T_RESOURCE_USER is '用户表';
comment on column T_RESOURCE_USER.USER_NAME is '用户名';
comment on column T_RESOURCE_USER.PASSWORD is '密码';
comment on column T_RESOURCE_USER.CONTACT is '联系方式';

DECLARE CNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) FCOUNT INTO CNT FROM (SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE OBJ.OBJECT_TYPE='SEQUENCE') 
WHERE OBJECT_NAME = 'SEQ_T_RESOURCE_USER';
IF CNT = 0 THEN
EXECUTE IMMEDIATE '
 create sequence SEQ_T_RESOURCE_USER
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000
increment by 1
cache 20 
';
END IF;
END;
/

--角色表

declare 
num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_ROLE';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_ROLE 
(
   ID                   NUMBER(19)           not null,
   NAME                 NVARCHAR2(50),
   IS_SYSTEM            NUMBER(1),
   CAN_OPERATE_ALL_TEXTBOOK NUMBER(1),
   constraint PK_R_RESOURCE_ROLE primary key (ID)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/
comment on table T_RESOURCE_ROLE is '角色表';

comment on column T_RESOURCE_ROLE.IS_SYSTEM is '是否内置(内置则不可修改与删除)';

comment on column T_RESOURCE_ROLE.CAN_OPERATE_ALL_TEXTBOOK is '是否可以操作所有教材目录';

DECLARE CNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) FCOUNT INTO CNT FROM (SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE OBJ.OBJECT_TYPE='SEQUENCE') 
WHERE OBJECT_NAME = 'SEQ_T_RESOURCE_ROLE';
IF CNT = 0 THEN
EXECUTE IMMEDIATE '
 create sequence SEQ_T_RESOURCE_ROLE
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000
increment by 1
cache 20 
';
END IF;
END;
/

--用户角色关联表 (无序列)
declare 
num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_USER_ROLE';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_USER_ROLE 
(
   ID                   NUMBER(19)           not null,
   ROLE_ID              NUMBER(19)           not null,
   constraint PK_R_RESOURCE_USER_ROLE primary key (ID, ROLE_ID)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/
comment on table T_RESOURCE_USER_ROLE is '用户角色关联表';
comment on column T_RESOURCE_USER_ROLE.ID is '用户ID';
comment on column T_RESOURCE_USER_ROLE.ROLE_ID is '角色ID
';

--权限表
declare 
num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_PRIVILEGE';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_PRIVILEGE 
(
   ID                   NUMBER(19)           not null,
   PARENT_ID            NUMBER(19),
   NAME                 NVARCHAR2(50),
   URL                  NVARCHAR2(100),
   constraint PK_R_RESOURCE_PRIVILEGE primary key (ID)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/
comment on table T_RESOURCE_PRIVILEGE is
'权限表';

comment on column T_RESOURCE_PRIVILEGE.ID is
'唯一标志';

comment on column T_RESOURCE_PRIVILEGE.PARENT_ID is
'所属父级';

comment on column T_RESOURCE_PRIVILEGE.NAME is
'名称';

comment on column T_RESOURCE_PRIVILEGE.URL is
'地址';

DECLARE CNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) FCOUNT INTO CNT FROM (SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE OBJ.OBJECT_TYPE='SEQUENCE') 
WHERE OBJECT_NAME = 'SEQ_T_RESOURCE_PRIVILEGE';
IF CNT = 0 THEN
EXECUTE IMMEDIATE '
 create sequence SEQ_T_RESOURCE_PRIVILEGE
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000
increment by 1
cache 20 
';
END IF;
END;
/


--角色权限关联表 (无序列)
declare 
num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_ROLE_PRIVILEGE';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_ROLE_PRIVILEGE 
(
   ID                   NUMBER(19)           not null,
   PRIVILEGE_ID         NUMBER(19)           not null,
   constraint PK_R_RESOURCE_ROLE_PRIVILEGE primary key (ID, PRIVILEGE_ID)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/
comment on table T_RESOURCE_ROLE_PRIVILEGE is '角色权限表';


--角色教材目录权限表
declare 
num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_ROLE_TEXTBOOK';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_ROLE_TEXTBOOK 
(
   ID                   NUMBER(19)           not null,
   PROP_ID              NUMBER(19)           not null,
   TYPE                 NUMBER(19)           not null,
   constraint PK_R_RESOURCE_ROLE_TEXTBOOK primary key (ID, PROP_ID, TYPE)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/
comment on table T_RESOURCE_ROLE_TEXTBOOK is '角色所管理的教材目录相关信息';
comment on column T_RESOURCE_ROLE_TEXTBOOK.ID is '所属角色';
comment on column T_RESOURCE_ROLE_TEXTBOOK.TYPE is '年级、学科、版本、册';



--日志信息
declare 
num number;
begin
    select count(1) into num from User_Tables t where t.TABLE_NAME = 'T_RESOURCE_NOTE_BACK';
    if num = 0 then 
       execute immediate 
'create table T_RESOURCE_NOTE_BACK 
(
   ID                   NUMBER(19)           not null,
   USER_ID              NUMBER(19),
   TYPE                 NUMBER(1),
   TITLE                NVARCHAR2(255),
   OLD_CONTENT          CLOB,
   CONTENT              CLOB,
   CREATE_TIME          TIMESTAMP,
   constraint PK_R_RESOURCE_NOTE_BACK primary key (ID)
)
';
    else dbms_output.put_line(''); 
    end if;
end;
/
comment on table T_RESOURCE_NOTE_BACK is
'日志信息';

comment on column T_RESOURCE_NOTE_BACK.TYPE is
'添加、更新、删除';

comment on column T_RESOURCE_NOTE_BACK.TITLE is
'标题(添加了什么,修改了什么,删除了什么)';

comment on column T_RESOURCE_NOTE_BACK.OLD_CONTENT is
'修改前内容';

comment on column T_RESOURCE_NOTE_BACK.CONTENT is
'内容(以表格的方式对比)freemarker模板';

comment on column T_RESOURCE_NOTE_BACK.CREATE_TIME is
'创建时间';

DECLARE CNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) FCOUNT INTO CNT FROM (SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE OBJ.OBJECT_TYPE='SEQUENCE') 
WHERE OBJECT_NAME = 'SEQ_T_RESOURCE_NOTE_BACK';
IF CNT = 0 THEN
EXECUTE IMMEDIATE '
 create sequence SEQ_T_RESOURCE_NOTE_BACK
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000
increment by 1
cache 20 
';
END IF;
END;
--日志索引
/
DECLARE CNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) FCOUNT INTO CNT FROM (SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE OBJ.OBJECT_TYPE='INDEX') 
WHERE OBJECT_NAME = 'INDEX_NOTE_BACK_USER_ID';
IF CNT = 0 THEN
EXECUTE IMMEDIATE 'create index INDEX_NOTE_BACK_USER_ID on T_RESOURCE_NOTE_BACK (USER_ID ASC) tablespace UECHNDB_IDX';
END IF;
END;
/







优秀到卓越
分享到:
共 0 条  此列表为空  当前1/1页

© 2014 究问社区 copyRight 豫ICP备13003319号-1