網站後台系統權限實現

@zgcwkj  2019年08月21日

分類：

代碼 網站 

標簽：

none 

後台系統網站權限實現，數據庫結構

直接上圖片：

20190821140135.png

數據庫生成腳本：

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://www.zgcwkj.com/archives/128.html

轉載聲明：請注明本文章的標題及內容的出處和聲明，謝謝

評論已關閉

  1. 玩機屋

    大佬，大佬?

Top