我们接着上一篇博客SSM+springSecurity实现简单认证、自定义登录页面,成功与失败返回json数据、控制访问权限等功能来实现springSecurity与数据库连接实现动态角色权限控制
1. 创建项目
这个参考我的第一篇[SSM搭建springSecurity环境],然后我们在此基础上进行改造,最终项目结构如下:(https://blog.csdn.net/SnowDujc/article/details/104772935)
- 修改pom文件,增加相关倚赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.4</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.7</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
- 对springSecurity.xml进行修改:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:security="http://www.springframework.org/schema/security"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/security
http://www.springframework.org/schema/security/spring-security-4.2.xsd">
<!-- security:http :spring过滤器链配置可以配置如下:
1)需要拦截什么资源
2)什么资源什么角色权限
3)定义认证方式:HttpBasic、Fromlogin(*)
4)定义登录页面,定义登录请求地址,定义错误处理方式
-->
<security:http>
<!-- pattern: 需要拦截的资源
access:拦截方式
isFullyAuthenticated()该资源需要认证才可以访问
isAnonymous:匿名用户(未登录)才可以访问,已经登录的无法访问
permitAll:所有用户(不管是否登录)都可以访问
hasRole:拥有什么角色才可以访问
-->
<security:intercept-url pattern="/user/login" access="permitAll()"/>
<security:intercept-url pattern="/user/index" access="permitAll()"/>
<security:intercept-url pattern="/user/list" access="hasRole('ROLE_LIST')"/>
<security:intercept-url pattern="/user/update" access="hasRole('ROLE_UPDATE')"/>
<security:intercept-url pattern="/user/add" access="hasRole('ROLE_ADD')"/>
<security:intercept-url pattern="/user/delete" access="hasRole('ROLE_DELETE')"/>
<security:intercept-url pattern="/**" access="isFullyAuthenticated()"/>
<!-- 使用form-login的方式进行登录认证 -->
<security:form-login login-page="/user/login" authentication-success-forward-url="/user/index" />
<!--access-denied-handler访问被拒绝后的跳转页面-->
<security:access-denied-handler error-page="/user/accessDenied" />
<!-- 关闭csrf防护 -->
<security:csrf disabled="true"/>
</security:http>
<!-- security:authentication-manager:认证管理器
1)认证信息的提供方式(账户名,密码,当前用户权限),可以使用配置写死,也可以连接数据库
-->
<security:authentication-manager>
<!--调用myUserDetailsService中的逻辑实现与数据库对接-->
<security:authentication-provider user-service-ref="myUserDetailsService">
</security:authentication-provider>
</security:authentication-manager>
</beans>
- 创建jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/security?characterEncoding=utf-8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.password=123
- 对ApplicationContext.xml进行修改
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:c="http://www.springframework.org/schema/c" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<context:property-placeholder location="classpath:jdbc.properties"/>
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="maxWait" value="3000"/>
<property name="maxActive" value="10"/>
</bean>
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="typeAliasesPackage" value="domain"/>
<property name="configLocation" value="classpath:mybatis/mybatis.xml"/>
<property name="mapperLocations" value="classpath:mybatis/mapper/*Mapper.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="dao"></property>
</bean>
<bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<tx:annotation-driven ></tx:annotation-driven>
<bean id="myUserDetailsService" class="security.MyUserDetailsService"/>
</beans>
- 创建数据库表以及测试数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_permission
-- ----------------------------
DROP TABLE IF EXISTS `sys_permission`;
CREATE TABLE `sys_permission` (
`id` int(10) NOT NULL,
`permName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`permTag` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sys_permission
-- ----------------------------
INSERT INTO `sys_permission` VALUES (1, '增加', 'ROLE_ADD');
INSERT INTO `sys_permission` VALUES (2, '删除', 'ROLE_DELETE');
INSERT INTO `sys_permission` VALUES (3, '修改', 'ROLE_UPDATE');
INSERT INTO `sys_permission` VALUES (4, '查询', 'ROLE_LIST');
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` int(10) NOT NULL,
`roleName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`roleDesc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES (1, '管理员', '管理员');
INSERT INTO `sys_role` VALUES (2, '普通用户', '普通用户');
-- ----------------------------
-- Table structure for sys_role_permission
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_permission`;
CREATE TABLE `sys_role_permission` (
`role_id` int(10) DEFAULT NULL,
`perm_id` int(10) DEFAULT NULL,
INDEX `FK_Reference_3`(`role_id`) USING BTREE,
INDEX `FK_Reference_4`(`perm_id`) USING BTREE,
CONSTRAINT `FK_Reference_3` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_Reference_4` FOREIGN KEY (`perm_id`) REFERENCES `sys_permission` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sys_role_permission
-- ----------------------------
INSERT INTO `sys_role_permission` VALUES (1, 1);
INSERT INTO `sys_role_permission` VALUES (1, 2);
INSERT INTO `sys_role_permission` VALUES (1, 3);
INSERT INTO `sys_role_permission` VALUES (1, 4);
INSERT INTO `sys_role_permission` VALUES (2, 1);
INSERT INTO `sys_role_permission` VALUES (2, 2);
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int(10) NOT NULL,
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`realname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`createDate` date DEFAULT NULL,
`lastLoginTime` date DEFAULT NULL,
`enabled` int(5) DEFAULT NULL,
`accountNonExpired` int(5) DEFAULT NULL,
`accountNonLocked` int(5) DEFAULT NULL,
`credentialsNonExpired` int(5) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, 'user1', '用户1', '123', '2019-10-01', '2019-10-02', 1, 1, 1, 1);
INSERT INTO `sys_user` VALUES (2, 'user2', '用户2', '123', '2019-10-01', '2019-10-02', 1, 1, 1, 1);
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`user_id` int(10) DEFAULT NULL,
`role_id` int(10) DEFAULT NULL,
INDEX `FK_Reference_1`(`user_id`) USING BTREE,
INDEX `FK_Reference_2`(`role_id`) USING BTREE,
CONSTRAINT `FK_Reference_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_Reference_2` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES (1, 1);
INSERT INTO `sys_user_role` VALUES (2, 2);
SET FOREIGN_KEY_CHECKS = 1;
- 生成实体
创建SysPermissionEntity实体
@Data
public class SysPermissionEntity implements java.io.Serializable {
/** 版本号 */
private static final long serialVersionUID = -2165521275432675728L;
/** id */
private Integer id;
/** permName */
private String permName;
/** permTag */
private String permTag;
}
创建SysRoleEntity实体
@Data
public class SysPermissionEntity implements java.io.Serializable {
/** 版本号 */
private static final long serialVersionUID = -2165521275432675728L;
/** id */
private Integer id;
/** permName */
private String permName;
/** permTag */
private String permTag;
}
创建SysUserEntity实体,特别需要注意这个实体,需要实现UserDetails 接口,那样在认证后就可以直接返回这个类的对象了
@Data
public class SysUserEntity implements UserDetails {
private Integer id;
private String username;
private String realname;
private String password;
private Date createDate;
private Date lastLoginTime;
private boolean enabled;
private boolean accountNonExpired;
private boolean accountNonLocked;
private boolean credentialsNonExpired;
private List<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>();
}
- 编写dao
@Repository
public interface UserMapper {
public SysUserEntity findByName(String userName);
List<SysPermissionEntity> findPermissionByName(String userName);
}
- 编写sysUsermapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- SYS_USER -->
<mapper namespace="dao.UserMapper">
<!-- 查询所有数据 -->
<select id="findByName" resultType="sysUserEntity" parameterType="string">
SELECT
*
FROM SYS_USER where username=#{userName}
</select>
<select id="findPermissionByName" resultType="sysPermissionEntity" parameterType="string">
SELECT sys_permission.id,sys_permission.permName,sys_permission.permTag
from (select id ,username from sys_user where username=#{userName}) user
join sys_user_role user_role on user.id=user_role.user_id
join sys_role_permission role_permission on user_role.role_id=role_permission.role_id
join sys_permission on sys_permission.id=role_permission.perm_id
</select>
</mapper>
- 至此可以进行测试一下是否与数据库连接通畅
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/ApplicationContext.xml")
public class SpringTest {
@Autowired
private UserMapper userMapper;
@Test
public void findByName(){
SysUserEntity user = userMapper.findByName("user1");
System.out.println(user);
}
@Test
public void findPerByName(){
List<SysPermissionEntity> permissions = userMapper.findPermissionByName("user1");
for (SysPermissionEntity permission : permissions) {
System.out.println(permission.toString());
}
}
}
例如运行findPerByName方法,测试通过:
- 创建验证类MyUserDetailsService
public class MyUserDetailsService implements UserDetailsService {
@Autowired
private UserMapper userMapper;
@Override
public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
SysUserEntity sysUserEntity = userMapper.findByName(userName);
List<GrantedAuthority> list = new ArrayList();
if (sysUserEntity != null) {
List<SysPermissionEntity> permissionList = userMapper.findPermissionByName(userName);
for (SysPermissionEntity sysPermissionEntity : permissionList) {
String permTag = sysPermissionEntity.getPermTag();
list.add(new SimpleGrantedAuthority(permTag));
}
sysUserEntity.setAuthorities(list);
}
return sysUserEntity;
}
}