Springboot+SpringSecurity权限控制学习笔记（五）RBAC用户权限管理数据库创建

创建数据库和表不像增删改查那样常用，做个笔记

创建数据库

create database demo
default character set utf8
default collate set utf8_general_ci;

创建数据表

--用户表
drop table if exists `sys_user`;
create table `sys_user` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`username` varchar(20) character set utf8 collate utf8_general_ci NOT NULL,
	`password` varchar(127) character set utf8 collate utf8_general_ci NOT NULL,
	primary key (`id`) USING BTREE
) ENGINE = InnoDB character set = utf8 collate=utf8_general_ci ROW_FORMAT=Dynamic;

--角色表
drop table if exists `sys_role`;
create table `sys_role` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`code` varchar(100) character set utf8 collate utf8_general_ci NOT NULL,
	`name` varchar(100) character set utf8 collate utf8_general_ci NOT NULL,
	primary key (`id`) USING BTREE
) ENGINE = InnoDB character set = utf8 collate=utf8_general_ci ROW_FORMAT=Dynamic;

--用户角色关联表
drop table if exists `sys_user_role`;
create table `sys_user_role` (
	`user_id` int(11) NOT NULL,
	`role_id` int(11) NOT NULL,
	primary key (`user_id`,`role_id`) USING BTREE,
	index `fk_role_id`(`role_id`) using btree,
	CONSTRAINT `fk_role_id` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB character set = utf8 collate=utf8_general_ci ROW_FORMAT=Dynamic;

插入示例数据

--插入用户数据
insert into `sys_user` (username,password) values
('admin1','$2a$10$f.SFzr0kQeS475nFkZuf7ufeVrVSYZwZZGbo3Jwe0/F.zdFBydD0S'),
('admin2','$2a$10$gY3TPpt2RbFpD1ZAkKXfletDDSfnj9qTMyjPfQhgoOJIU.6mCy74m');
--插入角色数据
insert into `sys_role` (code,name) values
('ROLE_admin','管理员'),
('ROLE_user','普通用户');
--插入用户、角色关联数据
insert into `sys_user_role` (user_id,role_id) values
(1,1),
(2,2);

数据库创建后的结构

每个表中各有两条测试数据,角色表code字段ROLE_admin，必须以ROLE_开头

配置依赖

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <!-- Mybatis-plus 的依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <!-- 代码自动生成器 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--模板引擎-->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>