最简单基于RBAC权限系统数据库结构设计        包括如下几个表    1. 用户表      -- Table "t_user" DDL    CREATE TABLE `t_user` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `username` varchar(100) DEFAULT NULL,    `password` varchar(100) DEFAULT NULL,    `name` varchar(100) DEFAULT NULL,    `status` int(11) DEFAULT ‘0‘,    `department_id` int(11) DEFAULT NULL,    `desc` varchar(255) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;      2. 权限表      -- Table "t_permission" DDL    CREATE TABLE `t_permission` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `name` varchar(100) DEFAULT NULL,    `desc` varchar(255) DEFAULT NULL,    `status` int(11) DEFAULT NULL,    `is_menu_display` int(11) DEFAULT NULL,    `menu_url` varchar(255) DEFAULT NULL,    `main_menu_id` int(11) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;      3. 角色表      -- Table "t_role" DDL    CREATE TABLE `t_role` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `name` varchar(100) DEFAULT NULL,    `desc` varchar(200) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    4. 用户角色表      -- Table "t_user_role" DDL    CREATE TABLE `t_user_role` (    `user_id` int(11) NOT NULL DEFAULT ‘0‘,    `role_id` int(11) NOT NULL DEFAULT ‘0‘,    PRIMARY KEY (`user_id`,`role_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    5. 角色权限表      -- Table "t_role_permission" DDL    CREATE TABLE `t_role_permission` (    `role_id` int(11) NOT NULL DEFAULT ‘0‘,    `permission_id` int(11) NOT NULL DEFAULT ‘0‘,    PRIMARY KEY (`role_id`,`permission_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    6. 菜单表      -- Table "t_menu" DDL    CREATE TABLE `t_menu` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `name` varchar(100) DEFAULT NULL,    `desc` varchar(255) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;    7. 部门表      -- Table "t_department" DDL    CREATE TABLE `t_department` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `name` varchar(100) DEFAULT NULL,    `desc` varchar(255) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;        涉及的主要sql:      1. 获取某个用户的权限列表:       select * from t_permission where id in (select distinct permission_id  from t_role_permission where role_id in ( select role_id  from t_user_role where user_id=1))    2. 主菜单列表：(菜单)    select * from t_menu where id in (  select distinct main_menu_id from t_permission where id in (1,2,3,4))        3. 某个主菜单下的子菜单列表：       select * from t_permission where id in (1,2,3,4) and   main_menu_id=1       select * from t_permission where id in (1,2,3,4) and   main_menu_id=2

INSERT INTO `t_department` VALUES (‘1‘, ‘技术部‘, ‘技术部‘);  INSERT INTO `t_department` VALUES (‘2‘, ‘财务部‘, ‘财务部‘);  INSERT INTO `t_department` VALUES (‘3‘, ‘设计部‘, ‘设计部‘);  INSERT INTO `t_menu` VALUES (‘1‘, ‘系统管理‘, ‘系统管理‘);  INSERT INTO `t_menu` VALUES (‘2‘, ‘财务管理‘, ‘财务管理‘);  INSERT INTO `t_menu` VALUES (‘3‘, ‘部门管理‘, ‘部门管理‘);  INSERT INTO `t_menu` VALUES (‘4‘, ‘日志管理‘, ‘日志管理‘);  INSERT INTO `t_menu` VALUES (‘5‘, ‘角色管理‘, ‘角色管理‘);  INSERT INTO `t_permission` VALUES (‘1‘, ‘用户列表‘, ‘用户列表‘, ‘0‘, ‘1‘, ‘userlist.do‘, ‘1‘);  INSERT INTO `t_permission` VALUES (‘2‘, ‘用户新增‘, ‘用户新增‘, ‘0‘, ‘1‘, ‘useradd.do‘, ‘1‘);  INSERT INTO `t_permission` VALUES (‘3‘, ‘用户修改‘, ‘用户修改‘, ‘0‘, ‘1‘, ‘userModify.do‘, ‘1‘);  INSERT INTO `t_permission` VALUES (‘4‘, ‘用户删除‘, ‘用户删除‘, ‘0‘, ‘1‘, ‘userDel.do‘, ‘2‘);  INSERT INTO `t_permission` VALUES (‘5‘, ‘用户查询‘, ‘用户查询‘, ‘0‘, ‘1‘, ‘userSearch.do‘, ‘3‘);  INSERT INTO `t_role` VALUES (‘1‘, ‘系统管理员‘, ‘系统管理员‘);  INSERT INTO `t_role` VALUES (‘2‘, ‘测试角色1‘, ‘测试角色1‘);  INSERT INTO `t_role` VALUES (‘3‘, ‘测试角色2‘, ‘测试角色2‘);  INSERT INTO `t_role_permission` VALUES (‘1‘, ‘1‘);  INSERT INTO `t_role_permission` VALUES (‘1‘, ‘2‘);  INSERT INTO `t_role_permission` VALUES (‘1‘, ‘3‘);  INSERT INTO `t_role_permission` VALUES (‘2‘, ‘2‘);  INSERT INTO `t_role_permission` VALUES (‘3‘, ‘4‘);  INSERT INTO `t_user` VALUES (‘1‘, ‘admin‘, ‘admin‘, ‘管理员‘, ‘0‘, ‘1‘, ‘管理员‘);  INSERT INTO `t_user_role` VALUES (‘1‘, ‘1‘);  INSERT INTO `t_user_role` VALUES (‘1‘, ‘2‘);  INSERT INTO `t_user_role` VALUES (‘1‘, ‘3‘);