rbac5表设计方法及查询方法

Question

如何设计mysql rbac表，实现不同用户登录获取不同的一级二级菜单？
超级管理员：Jay [1,用户管理，1.1:新增用户，1.2:查询用户，1.3:删除用户；2,文章管理，2.1:新增文章，2.2:编辑文章，2.3:删除文章，2.4:查阅文章]
管理员：Lee [2,文章管理，2.1:新增文章，2.2:编辑文章，2.3:删除文章，2.4:查阅文章]
普通用户：Lucyay [2,文章管理，2.4:查阅文章]

如下建表如何修改，并insert上述用户，权限，及菜单逻辑数据？
使用php后台如何在登录成功后根据查询，不同用户显示不同的菜单，并将菜单渲染成layui左侧的动态菜单？

CREATE TABLE `a_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `email` varchar(30) NOT NULL DEFAULT '' COMMENT '邮箱',
  `is_admin` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是超级管理员 1表示是 0 表示不是',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 1：有效 0：无效',
  `updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后一次更新时间',
  `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '插入时间',
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `a_role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '角色名称',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 1：有效 0：无效',
  `updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后一次更新时间',
  `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '插入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表';

CREATE TABLE `a_access` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '' COMMENT '权限名称',
  `urls` varchar(1000) NOT NULL DEFAULT '' COMMENT 'json 数组',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 1：有效 0：无效',
  `updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后一次更新时间',
  `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '插入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限详情表';

CREATE TABLE `a_role_access` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL DEFAULT '0' COMMENT '角色id',
  `access_id` int(11) NOT NULL DEFAULT '0' COMMENT '权限id',
  `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '插入时间',
  PRIMARY KEY (`id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色权限表';

CREATE TABLE `a_user_role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `role_id` int(11) NOT NULL DEFAULT '0' COMMENT '角色ID',
  `created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '插入时间',
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户角色表';

Answer 1

引自强大而免费的微信小程序：皆我百晓生。

---

查询方法：

1. 根据用户ID查询用户信息：

SELECT * FROM a_user WHERE id = {user_id};

1. 查询用户所属的角色：

SELECT a_role.* FROM a_role
JOIN a_user_role ON a_user_role.role_id = a_role.id
WHERE a_user_role.uid = {user_id};

1. 查询角色拥有的权限：

SELECT a_access.* FROM a_access
JOIN a_role_access ON a_role_access.access_id = a_access.id
WHERE a_role_access.role_id = {role_id};

设计方法：

1. 在用户表（a_user）中添加一个字段（role_id），用于存储用户所属的角色ID。

ALTER TABLE a_user ADD COLUMN role_id int(11) NOT NULL DEFAULT '0' COMMENT '角色ID';

1. 在角色表（a_role）中添加一个字段（name），用于存储角色名称。

ALTER TABLE a_role ADD COLUMN name varchar(50) NOT NULL DEFAULT '' COMMENT '角色名称';

1. 在权限详情表（a_access）中添加一个字段（urls），用于存储权限的URL。

ALTER TABLE a_access ADD COLUMN urls varchar(1000) NOT NULL DEFAULT '' COMMENT 'json 数组';

1. 在角色权限表（a_role_access）中添加一个字段（access_id），用于存储权限ID。

ALTER TABLE a_role_access ADD COLUMN access_id int(11) NOT NULL DEFAULT '0' COMMENT '权限id';

1. 在用户角色表（a_user_role）中添加一个字段（role_id），用于存储角色ID。

ALTER TABLE a_user_role ADD COLUMN role_id int(11) NOT NULL DEFAULT '0' COMMENT '角色ID';

修改方法：

1. 插入超级管理员（Jay）的数据：

INSERT INTO a_user (name, email, is_admin, status, updated_time, created_time)
VALUES ('Jay', '[e-mail]', 1, 1, NOW(), NOW());

INSERT INTO a_role (name, status, updated_time, created_time)
VALUES ('超级管理员', 1, NOW(), NOW());

INSERT INTO a_access (title, urls, status, updated_time, created_time)
VALUES ('用户管理', '1.1:新增用户,1.2:查询用户,1.3:删除用户', 1, NOW(), NOW());

INSERT INTO a_access (title, urls, status, updated_time, created_time)
VALUES ('文章管理', '2.1:新增文章,2.2:编辑文章,2.3:删除文章,2.4:查阅文章', 1, NOW(), NOW());

INSERT INTO a_role_access (role_id, access_id, created_time)
VALUES (1, 1, NOW());

INSERT INTO a_role_access (role_id, access_id, created_time)
VALUES (1, 2, NOW());

1. 插入管理员（Lee）的数据：

INSERT INTO a_user (name, email, is_admin, status, updated_time, created_time, role_id)
VALUES ('Lee', '[e-mail]', 0, 1, NOW(), NOW(), 2);

INSERT INTO a_role (name, status, updated_time, created_time)
VALUES ('管理员', 1, NOW(), NOW());

INSERT INTO a_role_access (role_id, access_id, created_time)
VALUES (2, 2, NOW());

INSERT INTO a_role_access (role_id, access_id, created_time)
VALUES (2, 3, NOW());

INSERT INTO a_role_access (role_id, access_id, created_time)
VALUES (2, 4, NOW());

1. 插入普通用户（Lucyay）的数据：

INSERT INTO a_user (name, email, is_admin, status, updated_time, created_time, role_id)
VALUES ('Lucyay', '[e-mail]', 0, 1, NOW(), NOW(), 3);

INSERT INTO a_role (name, status, updated_time, created_time)
VALUES ('普通用户', 1, NOW(), NOW());

INSERT INTO a_role_access (role_id, access_id, created_time)
VALUES (3, 4, NOW());

PHP后台实现根据用户登录信息获取菜单并渲染成layui左侧的动态菜单的方法示例：

1. 首先，在用户登录成功后，根据用户ID查询其所属角色，然后查询角色拥有的权限：

$user_id = $_SESSION['user_id'];

// 查询用户所属的角色
$query = "SELECT role_id FROM a_user WHERE id = $user_id;";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$role_id = $row['role_id'];

// 查询角色拥有的权限
$query = "SELECT a_access.* FROM a_access
          JOIN a_role_access ON a_role_access.access_id = a_access.id
          WHERE a_role_access.role_id = $role_id;";
$result = mysqli_query($conn, $query);
$permissions = array();
while ($row = mysqli_fetch_assoc($result)) {
    $permissions[] = $row;
}

1. 根据查询到的权限数据，构建菜单树的数据结构：

$menu = array();
foreach ($permissions as $permission) {
    $title = $permission['title'];
    $urls = explode(',', $permission['urls']);
    
    $menu_items = array();
    foreach ($urls as $url) {
        list($level1, $level2) = explode(':', $url);
        
        $menu_items[$level1][] = $level2;
    }
    
    $menu[$title] = $menu_items;
}

1. 使用layui的菜单组件，根据菜单树数据渲染动态菜单：

foreach ($menu as $title => $menu_items) {
    // 渲染一级菜单
    echo "<li class='layui-nav-item'>";
    echo "<a class='' href='javascript:;'>$title</a>";
    
    // 渲染二级菜单
    echo "<dl class='layui-nav-child'>";
    foreach ($menu_items as $level1 => $level2s) {
        echo "<dd class=''>";
        echo "<a href='javascript:;'>$level1</a>";
        
        echo "<dl class='layui-nav-child'>";
        foreach ($level2s as $level2) {
            echo "<dd class=''><a href='javascript:;'>$level2</a></dd>";
        }
        echo "</dl>";
        
        echo "</dd>";
    }
    echo "</dl>";
    
    echo "</li>";
}

注意：上述代码仅为示例，需要根据具体的开发环境和需求进行修改和适配。