一、RBAC介绍
https://www.jb51.net/article/121510.htm
https://www.jb51.net/article/121512.htm
二、建模
三、SQL
CREATE TABLE `user` (
`id ` int UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL,
`mobile` varchar(11) NOT NULL COMMENT '手机号',
`email` varchar(255) NOT NULL COMMENT '邮箱',
`status` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户状态 0-未激活 1-正常 2-已停用',
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id `) ,
UNIQUE INDEX (`username` ASC),
UNIQUE INDEX (`mobile` ASC)
)
COMMENT = '系统-用户表';
CREATE TABLE `role` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`role_name` varchar(255) NOT NULL COMMENT '角色名',
`role_type` enum('system','custom') NOT NULL DEFAULT 'custom' COMMENT '角色类型',
`role_desc` varchar(0) NOT NULL COMMENT '角色描述',
`status` tinyint(255) UNSIGNED NOT NULL DEFAULT 1 COMMENT '角色状态 0-已停用 1-使用中',
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
)
COMMENT = '系统-角色表';
CREATE TABLE `user_role` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`role_id` int NOT NULL,
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `user_role` (`uid` ASC, `role_id` ASC)
)
COMMENT = '系统-用户角色关联表';
CREATE TABLE `role_resource` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`role_id` int(11) NOT NULL,
`resource_type` enum('menu','element') NOT NULL,
`resource_id` int(11) NULL,
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `role_resource` (`role_id` ASC, `resource_type` ASC, `resource_id` ASC)
)
COMMENT = '系统-角色资源表';
CREATE TABLE `menu` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`parent_id` int NOT NULL DEFAULT 0 COMMENT '父菜单id',
`menu_name` varchar(30) NOT NULL COMMENT '菜单名称',
`menu_icon` varchar(50) NOT NULL COMMENT '菜单图标',
`menu_href` varchar(80) NOT NULL COMMENT '菜单跳转链接',
`menu_desc` varchar(255) NOT NULL COMMENT '菜单描述',
`sort_order` int NOT NULL COMMENT '排序 越小越前',
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
)
COMMENT = '系统-菜单表';
CREATE TABLE `element` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`element_name` varchar(30) NOT NULL,
`element_type` enum('page','block') NOT NULL DEFAULT 'page' COMMENT '元素类型',
`element_code` varchar(50) NOT NULL COMMENT '页面元素编号(路由)',
`element_desc` varchar(255) NOT NULL COMMENT '元素描述',
`sort_order` int NOT NULL DEFAULT 0 COMMENT '排序',
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) ,
INDEX ()
)
COMMENT = '系统-元素表';
CREATE TABLE `api` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`api_name` varchar(30) NOT NULL COMMENT '接口名称',
`api_route` varchar(50) NOT NULL COMMENT '接口路由',
`api_method` enum('post','get','put','delete') NOT NULL COMMENT '接口请求方法',
`api_desc` varchar(255) NOT NULL COMMENT '接口描述',
`sort_order` int NOT NULL DEFAULT 0,
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
)
COMMENT = '系统-接口表';
CREATE TABLE `element_api` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`element_id` int(11) NOT NULL,
`api_id` int(11) NOT NULL,
`created_at` int NOT NULL DEFAULT 0,
`updated_at` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `element_api` (`element_id` ASC, `api_id` ASC)
)
COMMENT = '系统-元素接口关联表';