小程序开发中的PHP权限管理与用户角色设定

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 用户注册时设定角色ID为2
INSERT INTO `user` (`username`, `password`, `role_id`) 
VALUES ('user1', '123456', '2');

// 管理员注册时设定角色ID为1
INSERT INTO `user` (`username`, `password`, `role_id`) 
VALUES ('admin1', 'admin123', '1');

// 获取用户角色对应的权限等级
$sql = "SELECT `level` FROM `permission` 
WHERE `id` IN (SELECT `permission_id` FROM `role_permission` WHERE `role_id` = ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$role_id]);
$permissions = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 根据权限等级判断用户是否有权限
foreach ($permissions as $permission) {
  if ($permission['level'] >= $required_level) {
    // 用户有权限，执行相应操作
  } else {
    // 用户无权限，提示无权操作
  }
}

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 管理员角色拥有所有权限
INSERT INTO `role_permission` (`role_id`, `permission_id`) 
VALUES (1, 1), (1, 2), (1, 3);

// 普通用户角色只有部分权限
INSERT INTO `role_permission` (`role_id`, `permission_id`) 
VALUES (2, 1), (2, 3);

// 根据用户ID获取角色ID
$sql = "SELECT `role_id` FROM `user` WHERE `id` = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
$role_id = $stmt->fetchColumn();

// 根据角色ID获取用户角色
$sql = "SELECT `name` FROM `role` WHERE `id` = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$role_id]);
$role = $stmt->fetchColumn();