建设工程监管信息系统的系统权限管理子模块

一、概要

作为《建设工程监管信息系统》项目开发组的程序员，按要求完成：
数据库的创建；
数据表的创建；
数据的操作。

二、实施步骤和内容

《系统权限管理》子模块的E-R图如图2.1 所示，逻辑数据模型如图2.2 所示，物理数据模型如图2.3所示，数据表字段名定义见表2.3。请按以下设计完成数据库创建、数据表创建和数据操作任务：

三、代码

-- 创建用户表
CREATE TABLE T_user(
	User_id CHAR(4) NOT NULL COMMENT '用户id，主键',
	User_name CHAR(16) NOT NULL COMMENT'用户姓名',
	User_passwd CHAR(16) NOT NULL COMMENT'用户密码',
	Dept_id CHAR(3) NOT NULL COMMENT'所属部门',
	Telephone VARCHAR(16) COMMENT '联系电话',
	Address VARCHAR(32) COMMENT '联系地址',
	Handphone VARCHAR(16) COMMENT '手机号',
	Usb_no VARCHAR(64) NOT NULL COMMENT '密码锁号',
	Reserve VARCHAR(64) COMMENT '备注',
	PRIMARY KEY(User_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
 


-- 创建功能角色定义表
CREATE TABLE T_func_role_def(
	Func_role_id CHAR(3) NOT NULL COMMENT '功能角色id，主键',
	Func_role_name VARCHAR(32) NOT NULL COMMENT '角色名称',
	Reserve VARCHAR(64) COMMENT '备注',
	PRIMARY KEY(Func_role_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建一级功能权限表
CREATE TABLE T_func_item(
	Func_id CHAR(3) NOT NULL COMMENT '功能id，主键',
	Func_name VARCHAR(32) NOT NULL COMMENT '功能名称',
	Reserve VARCHAR(64) COMMENT '备注',
	PRIMARY KEY(Func_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 通过物理数据模型，再创建两张表，便于表跟表之间的关系

-- 用户---角色关联表 T_User_Role
CREATE TABLE T_User_Role(
	User_id CHAR(4) NOT NULL COMMENT '用户id',
	Func_role_id CHAR(3) NOT NULL COMMENT '角色id',
	PRIMARY KEY(User_id,Func_role_id) COMMENT '复合主键'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 角色----权限关联表T_Role_Func
CREATE TABLE T_Role_Func(
	Func_id CHAR(3) NOT NULL COMMENT '功能id',
	Func_role_id CHAR(3) NOT NULL COMMENT '角色id',
	PRIMARY KEY(Func_id,Func_role_id) COMMENT '复合主键'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 向T_user表插入数据
INSERT INTO T_user VALUES('id01','刘德华','123','KBB','5678900','湖南长沙','12345678963','ldh123','admin')
INSERT INTO T_user VALUES('id02','刘张三','123','CBB','5954900','湖南长沙','12398748963','lzs123','commom')

-- 向T_func_role_def表插入数据
INSERT INTO T_func_role_def VALUES('001','管理员','admin')
INSERT INTO T_func_role_def VALUES('002','普通角色','common')

-- 向T_func_item表插入数据
INSERT INTO T_func_item VALUES('1','增删改查','所有权限')
INSERT INTO T_func_item VALUES('2','查','查询权限')

-- 向`t_user_role`表插入数据
INSERT INTO T_user_role VALUES('id01','001');
INSERT INTO T_user_role VALUES('id02','002');

-- 向`t_role_func`表插入数据
INSERT INTO T_role_func VALUES('1','001');
INSERT INTO T_role_func VALUES('2','002');


-- 查询出所属部门为“KBB”的操作员的基本信息；
SELECT * FROM T_user WHERE Dept_id='KBB'

-- 查询出姓名为“刘德华”的操作员具有哪些功能权限；
SELECT Func_name FROM T_func_item WHERE Func_id = (
SELECT Func_id FROM T_role_func WHERE Func_role_id = (
SELECT Func_role_id FROM T_user_role WHERE User_id = (SELECT User_id FROM T_user WHERE User_name='刘德华')))

-- 查询出“普通角色”角色所拥有的功能；
SELECT Func_name FROM T_func_item WHERE Func_id = (
SELECT Func_id FROM T_role_func WHERE Func_role_id = (
SELECT Func_role_id FROM T_func_role_def WHERE Func_role_name='普通角色'))

-- 编写视图查询用户的姓名，密码和所属部门；
CREATE VIEW vw_userinfo
AS
SELECT User_name,User_passwd,Dept_id FROM T_user

SELECT * FROM vw_userinfo

-- 写存储过程，查询指定用户所具有的功能权限。
DELIMITER $$
CREATE PROCEDURE prcUserFunc(IN username CHAR(16),OUT result CHAR(16))
BEGIN
	SELECT Func_name FROM T_func_item WHERE Func_id = (
	SELECT Func_id FROM T_role_func WHERE Func_role_id = (
	SELECT Func_role_id FROM T_user_role WHERE User_id = (SELECT User_id FROM T_user WHERE User_name=username)));
END $$
DELIMITER ;

SET @func=NULL
CALL prcUserFunc('刘张三',@func)