MySQL数据库基础练习系列47、权限管理系统

-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    email VARCHAR(100) COMMENT '邮箱',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    phone VARCHAR(20) COMMENT '电话号码'
);


-- 角色表
CREATE TABLE Roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL COMMENT '角色名称',
    description VARCHAR(255) COMMENT '角色描述'
);


-- 权限表
CREATE TABLE Permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '权限ID',
    permission_name VARCHAR(100) NOT NULL COMMENT '权限名称',
    description VARCHAR(255) COMMENT '权限描述'
);


-- 用户角色关联表
CREATE TABLE UserRoles (
    user_role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户角色关联ID',
    user_id INT NOT NULL COMMENT '用户ID',
    role_id INT NOT NULL COMMENT '角色ID',
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (role_id) REFERENCES Roles(role_id)
);


-- 角色权限关联表
CREATE TABLE RolePermissions (
    role_permission_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色权限关联ID',
    role_id INT NOT NULL COMMENT '角色ID',
    permission_id INT NOT NULL COMMENT '权限ID',
    FOREIGN KEY (role_id) REFERENCES Roles(role_id),
    FOREIGN KEY (permission_id) REFERENCES Permissions(permission_id)
);


-- 资源表（可选，用于更细粒度的权限控制）
CREATE TABLE Resources (
    resource_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '资源ID',
    resource_name VARCHAR(100) NOT NULL COMMENT '资源名称',
    resource_type VARCHAR(50) NOT NULL COMMENT '资源类型',
    description VARCHAR(255) COMMENT '资源描述'
);


-- 资源权限关联表（可选，用于定义资源级别的权限）
CREATE TABLE ResourcePermissions (
    resource_permission_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '资源权限关联ID',
    resource_id INT NOT NULL COMMENT '资源ID',
    permission_id INT NOT NULL COMMENT '权限ID',
    FOREIGN KEY (resource_id) REFERENCES Resources(resource_id),
    FOREIGN KEY (permission_id) REFERENCES Permissions(permission_id)
);

-- 用户表
INSERT INTO Users (username, password, name, email, gender, phone) VALUES
('孙悟空', '123', '孙悟空', 'sunwukong@example.com', '男', '13800138001'),
('林黛玉', '123', '林黛玉', 'lindaiyu@example.com', '女', '13900139002'),
('郭靖', '123', '郭靖', 'guojing@example.com', '男', '13700137003');


-- 角色表
INSERT INTO Roles (role_name, description) VALUES
('管理员', '拥有系统所有权限'),
('普通用户', '拥有基本使用权限'),
('游客', '拥有查看公开内容的权限');


-- 权限表
INSERT INTO Permissions (permission_name, description) VALUES
('创建用户', '允许创建新用户'),
('编辑用户', '允许编辑用户信息'),
('查看用户', '允许查看用户列表'),
('删除用户', '允许删除用户');


-- 角色权限关联表（先插入权限，再插入关联）
INSERT INTO RolePermissions (role_id, permission_id) VALUES
(1, 1), -- 管理员拥有创建用户权限
(1, 2), -- 管理员拥有编辑用户权限
(1, 3), -- 管理员拥有查看用户权限
(1, 4), -- 管理员拥有删除用户权限
(2, 3);  -- 普通用户拥有查看用户权限


-- 用户角色关联表（先插入角色，再插入关联）
INSERT INTO UserRoles (user_id, role_id) VALUES
(1, 1), -- 孙悟空是管理员
(2, 2), -- 林黛玉是普通用户
(3, 3);  -- 郭靖是游客


-- 资源表（可选）
INSERT INTO Resources (resource_name, resource_type, description) VALUES
('系统设置', 'config', '系统设置页面的访问权限'),
('用户列表', 'list', '用户列表页面的访问权限'),
('文章详情', 'detail', '文章详情页面的访问权限');


-- 资源权限关联表（可选，先插入权限和资源，再插入关联）
INSERT INTO ResourcePermissions (resource_id, permission_id) VALUES
(1, 1), -- 系统设置需要创建用户权限
(2, 2), -- 用户列表需要编辑用户权限
(2, 3), -- 用户列表也需要查看用户权限
(3, 3);  -- 文章详情需要查看用户权限