网站后台系统权限实现

后台系统网站权限实现，数据库结构

直接上图片：

数据库生成脚本：

MySql

/*系统菜单表*/
DROP TABLE IF EXISTS `SMenu`;
CREATE TABLE SMenu(
    MenuId INT NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
    ParentId INT NOT NULL DEFAULT 0 COMMENT '父ID',
    MenuName VARCHAR(32) NOT NULL DEFAULT '' COMMENT '菜单名称',
    Icon VARCHAR(32) NOT NULL DEFAULT '' COMMENT '图标',
    Url VARCHAR(32) NOT NULL DEFAULT '' COMMENT '链接',
    Sort INT NOT NULL DEFAULT 0 COMMENT '排序',
    PRIMARY KEY (MenuId)
) COMMENT = '系统菜单表';
/*系统菜单角色表*/
DROP TABLE IF EXISTS `SMenuRole`;
CREATE TABLE SMenuRole(
    MenuRoleId INT NOT NULL AUTO_INCREMENT COMMENT '菜单角色ID',
    MenuId INT NOT NULL DEFAULT 0 COMMENT '菜单ID',
    Name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '名称',
    Behavior VARCHAR(32) NOT NULL DEFAULT '' COMMENT '行为',
    PRIMARY KEY (MenuRoleId)
) COMMENT = '系统菜单角色表';
/*系统用户表*/
DROP TABLE IF EXISTS `SUser`;
CREATE TABLE SUser(
    UserId INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    RoleId INT NOT NULL DEFAULT 0 COMMENT '角色ID',
    Accounts VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户账号',
    Password VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户密码',
    Name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名称',
    PRIMARY KEY (UserId)
) COMMENT = '系统用户表';
/*系统角色表*/
DROP TABLE IF EXISTS `SRole`;
CREATE TABLE SRole(
    RoleId INT NOT NULL AUTO_INCREMENT COMMENT '角色ID',
    RoleName VARCHAR(32) NOT NULL DEFAULT '' COMMENT '角色名称',
    PRIMARY KEY (RoleId)
) COMMENT = '系统角色表';
/*系统角色明细表*/
DROP TABLE IF EXISTS `SRoleDetail`;
CREATE TABLE SRoleDetail(
    RoleDetailId INT NOT NULL AUTO_INCREMENT COMMENT '角色明细ID',
    RoleId INT NOT NULL DEFAULT 0 COMMENT '角色ID',
    MenuRoleId INT NOT NULL DEFAULT 0 COMMENT '菜单角色ID',
    PRIMARY KEY (RoleDetailId)
) COMMENT = '系统角色明细表';
/*系统日志表*/
DROP TABLE IF EXISTS `SLog`;
CREATE TABLE SLog(
    LogId INT NOT NULL AUTO_INCREMENT COMMENT '日志ID',
    Url VARCHAR(32) NOT NULL DEFAULT '' COMMENT '地址',
    Parameter VARCHAR(32) NOT NULL DEFAULT '' COMMENT '参数',
    OperatTime DATETIME NOT NULL COMMENT '时间',
    PRIMARY KEY (LogId)
) COMMENT = '系统日志表';

SqlServer

/*系统菜单表*/
CREATE TABLE SMenu(
    MenuId INT NOT NULL INDENTITY(1,1)   ,
    ParentId INT NOT NULL  DEFAULT 0  ,
    MenuName NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Icon NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Url NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Sort INT NOT NULL  DEFAULT 0  ,
    CONSTRAINT PK_SMenu PRIMARY KEY CLUSTERED (MenuId ASC ) ON [PRIMARY] 
) ;
--COMMENT ON TABLE SMenu IS '系统菜单表';
COMMENT ON COLUMN SMenu.MenuId IS '菜单ID';
COMMENT ON COLUMN SMenu.ParentId IS '父ID';
COMMENT ON COLUMN SMenu.MenuName IS '菜单名称';
COMMENT ON COLUMN SMenu.Icon IS '图标';
COMMENT ON COLUMN SMenu.Url IS '链接';
COMMENT ON COLUMN SMenu.Sort IS '排序';
/*系统菜单角色表*/
CREATE TABLE SMenuRole(
    MenuRoleId INT NOT NULL INDENTITY(1,1)   ,
    MenuId INT NOT NULL  DEFAULT 0  ,
    Name NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Behavior NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    CONSTRAINT PK_SMenuRole PRIMARY KEY CLUSTERED (MenuRoleId ASC ) ON [PRIMARY] 
) ;
--COMMENT ON TABLE SMenuRole IS '系统菜单角色表';
COMMENT ON COLUMN SMenuRole.MenuRoleId IS '菜单角色ID';
COMMENT ON COLUMN SMenuRole.MenuId IS '菜单ID';
COMMENT ON COLUMN SMenuRole.Name IS '名称';
COMMENT ON COLUMN SMenuRole.Behavior IS '行为';
/*系统用户表*/
CREATE TABLE SUser(
    UserId INT NOT NULL INDENTITY(1,1)   ,
    RoleId INT NOT NULL  DEFAULT 0  ,
    Accounts NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Password NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Name NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    CONSTRAINT PK_SUser PRIMARY KEY CLUSTERED (UserId ASC ) ON [PRIMARY] 
) ;
--COMMENT ON TABLE SUser IS '系统用户表';
COMMENT ON COLUMN SUser.UserId IS '用户ID';
COMMENT ON COLUMN SUser.RoleId IS '角色ID';
COMMENT ON COLUMN SUser.Accounts IS '用户账号';
COMMENT ON COLUMN SUser.Password IS '用户密码';
COMMENT ON COLUMN SUser.Name IS '用户名称';
/*系统角色表*/
CREATE TABLE SRole(
    RoleId INT NOT NULL INDENTITY(1,1)   ,
    RoleName NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    CONSTRAINT PK_SRole PRIMARY KEY CLUSTERED (RoleId ASC ) ON [PRIMARY] 
) ;
--COMMENT ON TABLE SRole IS '系统角色表';
COMMENT ON COLUMN SRole.RoleId IS '角色ID';
COMMENT ON COLUMN SRole.RoleName IS '角色名称';
/*系统角色明细表*/
CREATE TABLE SRoleDetail(
    RoleDetailId INT NOT NULL INDENTITY(1,1)   ,
    RoleId INT NOT NULL  DEFAULT 0  ,
    MenuRoleId INT NOT NULL  DEFAULT 0  ,
    CONSTRAINT PK_SRoleDetail PRIMARY KEY CLUSTERED (RoleDetailId ASC ) ON [PRIMARY] 
) ;
--COMMENT ON TABLE SRoleDetail IS '系统角色明细表';
COMMENT ON COLUMN SRoleDetail.RoleDetailId IS '角色明细ID';
COMMENT ON COLUMN SRoleDetail.RoleId IS '角色ID';
COMMENT ON COLUMN SRoleDetail.MenuRoleId IS '菜单角色ID';
/*系统日志表*/
CREATE TABLE SLog(
    LogId INT NOT NULL INDENTITY(1,1)   ,
    Url NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    Parameter NVARCHAR(32) NOT NULL  DEFAULT ''  ,
    OperatTime DATE NOT NULL    ,
    CONSTRAINT PK_SLog PRIMARY KEY CLUSTERED (LogId ASC ) ON [PRIMARY] 
) ;
--COMMENT ON TABLE SLog IS '系统日志表';
COMMENT ON COLUMN SLog.LogId IS '日志ID';
COMMENT ON COLUMN SLog.Url IS '地址';
COMMENT ON COLUMN SLog.Parameter IS '参数';
COMMENT ON COLUMN SLog.OperatTime IS '时间';

Oracle

/*系统菜单表*/
DROP TABLE SMenu;
CREATE TABLE SMenu(
    MenuId INT NOT NULL,
    ParentId INT DEFAULT 0 NOT NULL,
    MenuName NVARCHAR2(32) DEFAULT '' NOT NULL,
    Icon NVARCHAR2(32) DEFAULT '' NOT NULL,
    Url NVARCHAR2(32) DEFAULT '' NOT NULL,
    Sort INT DEFAULT 0 NOT NULL,
    PRIMARY KEY (MenuId)
);
COMMENT ON TABLE SMenu IS '系统菜单表';
COMMENT ON COLUMN SMenu.MenuId IS '菜单ID';
COMMENT ON COLUMN SMenu.ParentId IS '父ID';
COMMENT ON COLUMN SMenu.MenuName IS '菜单名称';
COMMENT ON COLUMN SMenu.Icon IS '图标';
COMMENT ON COLUMN SMenu.Url IS '链接';
COMMENT ON COLUMN SMenu.Sort IS '排序';
COMMENT ON TABLE SMenu IS '系统菜单表';
/*系统菜单角色表*/
DROP TABLE SMenuRole;
CREATE TABLE SMenuRole(
    MenuRoleId INT NOT NULL,
    MenuId INT DEFAULT 0 NOT NULL,
    Name NVARCHAR2(32) DEFAULT '' NOT NULL,
    Behavior NVARCHAR2(32) DEFAULT '' NOT NULL,
    PRIMARY KEY (MenuRoleId)
);
COMMENT ON TABLE SMenuRole IS '系统菜单角色表';
COMMENT ON COLUMN SMenuRole.MenuRoleId IS '菜单角色ID';
COMMENT ON COLUMN SMenuRole.MenuId IS '菜单ID';
COMMENT ON COLUMN SMenuRole.Name IS '名称';
COMMENT ON COLUMN SMenuRole.Behavior IS '行为';
COMMENT ON TABLE SMenuRole IS '系统菜单角色表';
/*系统用户表*/
DROP TABLE SUser;
CREATE TABLE SUser(
    UserId INT NOT NULL,
    RoleId INT DEFAULT 0 NOT NULL,
    Accounts NVARCHAR2(32) DEFAULT '' NOT NULL,
    Password NVARCHAR2(32) DEFAULT '' NOT NULL,
    Name NVARCHAR2(32) DEFAULT '' NOT NULL,
    PRIMARY KEY (UserId)
);
COMMENT ON TABLE SUser IS '系统用户表';
COMMENT ON COLUMN SUser.UserId IS '用户ID';
COMMENT ON COLUMN SUser.RoleId IS '角色ID';
COMMENT ON COLUMN SUser.Accounts IS '用户账号';
COMMENT ON COLUMN SUser.Password IS '用户密码';
COMMENT ON COLUMN SUser.Name IS '用户名称';
COMMENT ON TABLE SUser IS '系统用户表';
/*系统角色表*/
DROP TABLE SRole;
CREATE TABLE SRole(
    RoleId INT NOT NULL,
    RoleName NVARCHAR2(32) DEFAULT '' NOT NULL,
    PRIMARY KEY (RoleId)
);
COMMENT ON TABLE SRole IS '系统角色表';
COMMENT ON COLUMN SRole.RoleId IS '角色ID';
COMMENT ON COLUMN SRole.RoleName IS '角色名称';
COMMENT ON TABLE SRole IS '系统角色表';
/*系统角色明细表*/
DROP TABLE SRoleDetail;
CREATE TABLE SRoleDetail(
    RoleDetailId INT NOT NULL,
    RoleId INT DEFAULT 0 NOT NULL,
    MenuRoleId INT DEFAULT 0 NOT NULL,
    PRIMARY KEY (RoleDetailId)
);
COMMENT ON TABLE SRoleDetail IS '系统角色明细表';
COMMENT ON COLUMN SRoleDetail.RoleDetailId IS '角色明细ID';
COMMENT ON COLUMN SRoleDetail.RoleId IS '角色ID';
COMMENT ON COLUMN SRoleDetail.MenuRoleId IS '菜单角色ID';
COMMENT ON TABLE SRoleDetail IS '系统角色明细表';
/*系统日志表*/
DROP TABLE SLog;
CREATE TABLE SLog(
    LogId INT NOT NULL,
    Url NVARCHAR2(32) DEFAULT '' NOT NULL,
    Parameter NVARCHAR2(32) DEFAULT '' NOT NULL,
    OperatTime DATE NOT NULL,
    PRIMARY KEY (LogId)
);
COMMENT ON TABLE SLog IS '系统日志表';
COMMENT ON COLUMN SLog.LogId IS '日志ID';
COMMENT ON COLUMN SLog.Url IS '地址';
COMMENT ON COLUMN SLog.Parameter IS '参数';
COMMENT ON COLUMN SLog.OperatTime IS '时间';
COMMENT ON TABLE SLog IS '系统日志表';

---

版权属于：zgcwkj

本文链接：https://zgcwkj.com/archives/128.html

转载声明：请注明本文章的标题及内容的出处和声明，谢谢