Spring security 动态权限管理(基于数据库) - ToB企服应用市场:ToB评测及商务社交产业平台

-- 菜单表
CREATE TABLE `t_authority_menu`(
id int(11) NOT NULL AUTO_INCREMENT primary key ,
pattern_url varchar(128) DEFAULT ''
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- 添加数据
BEGIN;
insert into t_authority_menu values (1,'/admin/**');
insert into t_authority_menu values (2,'/user/**');
insert into t_authority_menu values (3,'/guest/**');
commit ;

复制代码

-- 角色表
CREATE TABLE `t_authority_role`(
id int(11) NOT NULL AUTO_INCREMENT primary key ,
role_name varchar(128) DEFAULT '' comment '角色标识',
role_desc varchar(128) DEFAULT '' comment '角色描述'
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- 添加数据
BEGIN;
insert into t_authority_role values (1,'ROLE_ADMIN','系统管理员');
insert into t_authority_role values (2,'ROLE_USER','普通用户');
insert into t_authority_role values (3,'ROLE_GUEST','游客');
commit ;

复制代码

-- 角色菜单关系表 primary key (m_id,r_id)
CREATE TABLE `t_authority_role_menu`(
id int(11) NOT NULL AUTO_INCREMENT primary key,
m_id int(11) DEFAULT NULL comment '菜单id',
r_id int(11) DEFAULT NULL comment '角色id'
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- 创建唯一索引
CREATE UNIQUE INDEX i_authority_role_menu1 ON t_authority_role_menu(m_id, r_id);
-- 添加数据
BEGIN;
insert into t_authority_role_menu(m_id, r_id) values (1,1);
insert into t_authority_role_menu(m_id, r_id) values (2,1);
insert into t_authority_role_menu(m_id, r_id) values (2,2);
insert into t_authority_role_menu(m_id, r_id) values (3,3);
insert into t_authority_role_menu(m_id, r_id) values (3,2);
commit ;

复制代码

-- 用户表
CREATE TABLE t_authority_user (
id int(11) NOT NULL AUTO_INCREMENT primary key,
user_name varchar(32) DEFAULT '' comment '用户名',
password varchar(32) DEFAULT '' comment '密码',
enabled tinyint(1) DEFAULT 1 comment '是否启用 1启用',
locked tinyint(1) DEFAULT 1 comment '是否锁定 1 未锁定'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
BEGIN;
insert into t_authority_user values (1,'admin','{noop}123456','1','1');
insert into t_authority_user values (2,'user','{noop}123456','1','1');
insert into t_authority_user values (3,'guest','{noop}123456','1','1');
commit ;

复制代码

-- 角色用户关系表
CREATE TABLE `t_authority_role_user`(
id int(11) NOT NULL AUTO_INCREMENT primary key ,
u_id int(11) DEFAULT 0 comment '用户id',
r_id int(11) DEFAULT 0 comment '角色id'
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- 创建唯一索引
CREATE UNIQUE INDEX i_authority_role_user1 ON t_authority_role_user(u_id, r_id);
-- 添加数据
BEGIN;
insert into t_authority_role_user(u_id, r_id) values (1,1);
insert into t_authority_role_user(u_id, r_id) values (1,2);
insert into t_authority_role_user(u_id, r_id) values (2,2);
insert into t_authority_role_user(u_id, r_id) values (3,3);
commit ;

复制代码

// 用户
@Data
public class AuthorityUser implements Serializable {
private Integer id;
/**
* 用户名
*/
private String userName;
/**
* 密码
*/
private String password;
/**
* 是否启用 1启用
*/
private Boolean enabled;
/**
* 是否锁定 1 未锁定
*/
private Boolean locked;
private static final long serialVersionUID = 1L;
}
// 角色
@Data
public class AuthorityRole implements Serializable {
private Integer id;
/**
* 角色标识
*/
private String roleName;
/**
* 角色描述
*/
private String roleDesc;
private static final long serialVersionUID = 1L;
}
// 菜单
@Data
public class AuthorityMenu implements Serializable {
private Integer id;
private String patternUrl;
/**
* 一个菜单对应多个角色
*/
private List<AuthorityRole> roles;
private static final long serialVersionUID = 1L;
}

复制代码

// 用户dao
public interface AuthorityUserDao {
/**
* 根据用户名获取用户信息认证使用
* @param id
* @return
*/
AuthorityUser selectByusername(String id);
int deleteByPrimaryKey(Integer id);
int insert(AuthorityUser record);
int insertSelective(AuthorityUser record);
AuthorityUser selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(AuthorityUser record);
int updateByPrimaryKey(AuthorityUser record);
}
// 角色
public interface AuthorityRoleDao {
/**
* 根据用户编码获取角色列表
* @param userId
* @return
*/
List<AuthorityRole> selectRoleListByUserId(int userId);
int deleteByPrimaryKey(Integer id);
int insert(AuthorityRole record);
int insertSelective(AuthorityRole record);
AuthorityRole selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(AuthorityRole record);
int updateByPrimaryKey(AuthorityRole record);
}
// 权限
public interface AuthorityMenuDao {
AuthorityMenu selectByPrimaryKey(Integer id);
/**
* 获取所有的菜单权限
* @return
*/
List<AuthorityMenu> getAllMenu();
}

复制代码

// 用户
<?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">
<mapper namespace="com.fashion.mapper.mysql.AuthorityUserDao">
<resultMap id="BaseResultMap" type="com.fashion.model.AuthorityUser">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="enabled" jdbcType="BOOLEAN" property="enabled" />
<result column="locked" jdbcType="BOOLEAN" property="locked" />
</resultMap>
<sql id="Base_Column_List">
id, user_name, `password`, enabled, locked
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_authority_user
where id = #{id,jdbcType=INTEGER}
</select>
<select id="selectByusername" parameterType="java.lang.String" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_authority_user
where user_name = #{userName}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_authority_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.fashion.model.AuthorityUser" useGeneratedKeys="true">
insert into t_authority_user (user_name, `password`, enabled,
locked)
values (#{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{enabled,jdbcType=BOOLEAN},
#{locked,jdbcType=BOOLEAN})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.fashion.model.AuthorityUser" useGeneratedKeys="true">
insert into t_authority_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null">
user_name,
</if>
<if test="password != null">
`password`,
</if>
<if test="enabled != null">
enabled,
</if>
<if test="locked != null">
locked,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userName != null">
#{userName,jdbcType=VARCHAR},
</if>
<if test="password != null">
#{password,jdbcType=VARCHAR},
</if>
<if test="enabled != null">
#{enabled,jdbcType=BOOLEAN},
</if>
<if test="locked != null">
#{locked,jdbcType=BOOLEAN},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.fashion.model.AuthorityUser">
update t_authority_user
<set>
<if test="userName != null">
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="password != null">
`password` = #{password,jdbcType=VARCHAR},
</if>
<if test="enabled != null">
enabled = #{enabled,jdbcType=BOOLEAN},
</if>
<if test="locked != null">
locked = #{locked,jdbcType=BOOLEAN},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.fashion.model.AuthorityUser">
update t_authority_user
set user_name = #{userName,jdbcType=VARCHAR},
`password` = #{password,jdbcType=VARCHAR},
enabled = #{enabled,jdbcType=BOOLEAN},
locked = #{locked,jdbcType=BOOLEAN}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
// 角色
<?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">
<mapper namespace="com.fashion.mapper.mysql.AuthorityRoleDao">
<resultMap id="BaseResultMap" type="com.fashion.model.AuthorityRole">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="role_name" jdbcType="VARCHAR" property="roleName" />
<result column="role_desc" jdbcType="VARCHAR" property="roleDesc" />
</resultMap>
<sql id="Base_Column_List">
id, role_name, role_desc
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_authority_role
where id = #{id,jdbcType=INTEGER}
</select>
<select id="selectRoleListByUserId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select a.role_name,a.role_desc from
t_authority_role a
left join t_authority_role_user b on a.id = b.r_id
where b.u_id = #{userId}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_authority_role
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.fashion.model.AuthorityRole" useGeneratedKeys="true">
insert into t_authority_role (role_name, role_desc)
values (#{roleName,jdbcType=VARCHAR}, #{roleDesc,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.fashion.model.AuthorityRole" useGeneratedKeys="true">
insert into t_authority_role
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="roleName != null">
role_name,
</if>
<if test="roleDesc != null">
role_desc,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="roleName != null">
#{roleName,jdbcType=VARCHAR},
</if>
<if test="roleDesc != null">
#{roleDesc,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.fashion.model.AuthorityRole">
update t_authority_role
<set>
<if test="roleName != null">
role_name = #{roleName,jdbcType=VARCHAR},
</if>
<if test="roleDesc != null">
role_desc = #{roleDesc,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.fashion.model.AuthorityRole">
update t_authority_role
set role_name = #{roleName,jdbcType=VARCHAR},
role_desc = #{roleDesc,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
// 菜单
<?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">
<mapper namespace="com.fashion.mapper.mysql.AuthorityMenuDao">
<resultMap id="BaseResultMap" type="com.fashion.model.AuthorityMenu">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="pattern_url" jdbcType="VARCHAR" property="patternUrl" />
</resultMap>
<sql id="Base_Column_List">
id, pattern_url
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_authority_menu
where id = #{id,jdbcType=INTEGER}
</select>
<resultMap id="MenuResultMap" type="com.fashion.model.AuthorityMenu">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="pattern_url" jdbcType="VARCHAR" property="patternUrl" />
<collection property="roles" ofType="com.fashion.model.AuthorityRole">
<id column="rId" property="id"/>
<result column="role_name" jdbcType="VARCHAR" property="roleName" />
<result column="role_desc" jdbcType="VARCHAR" property="roleDesc" />
</collection>
</resultMap>
<select id="getAllMenu" resultMap="MenuResultMap">
select a.*,c.id rId,c.role_name,c.role_desc
from t_authority_menu a
left join t_authority_role_menu b on a.id = b.m_id
left join t_authority_role c on c.id = b.r_id
</select>
</mapper>

复制代码

@Service
public class UserServiceDetailImpl implements UserDetailsService {
@Autowired
private AuthorityUserDao authorityUserDao;
@Autowired
private AuthorityRoleDao authorityRoleDao;
/**
* 认证
* @param username
* @return
* @throws UsernameNotFoundException
*/
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
// 1、根据用户名获取信息
AuthorityUser authorityUser = authorityUserDao.selectByusername(username);
if (null == authorityUser) {
throw new UsernameNotFoundException("用户不存在！");
}
// 2、获取用户对应的角色
List<AuthorityRole> roles = authorityRoleDao.selectRoleListByUserId(authorityUser.getId());
UserDetailInf userDetailInf = new UserDetailInf(authorityUser, roles);
return userDetailInf;
}
}

复制代码

public class UserDetailInf implements UserDetails {
private AuthorityUser user;
private List<AuthorityRole> roles;
public UserDetailInf(AuthorityUser user, List<AuthorityRole> roles) {
this.user = user;
this.roles = roles;
}
@Override
public Collection<? extends GrantedAuthority> getAuthorities() {
return roles.stream()
.map(r -> new SimpleGrantedAuthority(r.getRoleName()))
.collect(ArrayList::new, List::add,List::addAll);
}
@Override
public String getPassword() {
return user.getPassword();
}
@Override
public String getUsername() {
return user.getPassword();
}
@Override
public boolean isAccountNonExpired() {
return true;
}
@Override
public boolean isAccountNonLocked() {
return true;
}
@Override
public boolean isCredentialsNonExpired() {
return true;
}
@Override
public boolean isEnabled() {
return true;
}
}

复制代码

@Configuration
@MapperScan(basePackages = MysqlDsConfiguration.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory" )
@Slf4j
public class MysqlDsConfiguration {
static final String PACKAGE = "com.fashion.mapper.mysql";
static final String MAPPER_LOCATION = "classpath:mybatis/mapper/mysql/*.xml";
/**
* 配置数据源
* @return
*/
@Primary
@Bean
public DataSource mysqlDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("12345");
return dataSource;
}
@Primary
@Bean
public SqlSessionFactory mysqlSqlSessionFactory(@Autowired DataSource mysqlDataSource){
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
try {
sessionFactory.setDataSource(mysqlDataSource);
sessionFactory.setConfigLocation(new ClassPathResource("/mybatis/mybatis-config.xml"));
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(MysqlDsConfiguration.MAPPER_LOCATION));
return sessionFactory.getObject();
} catch (Exception e) {
log.error("mysql数据源初始化失败",e);
}
return null;
}
}

复制代码

@Component
public class UrlAntPatchMetadataSource implements FilterInvocationSecurityMetadataSource {
@Autowired
private AuthorityMenuDao authorityMenuDao;
private AntPathMatcher antPathMatcher = new AntPathMatcher();
@Override
public Collection<ConfigAttribute> getAttributes(Object object) throws IllegalArgumentException {
// 当前obj 实际上是一个url
String requestURI = ((FilterInvocation) object).getRequest().getRequestURI();
// 获取所有的url 对应的角色集合
List<AuthorityMenu> allAuthorityMenus = authorityMenuDao.getAllMenu();
String[] roles = allAuthorityMenus.stream()
.filter(menu -> antPathMatcher.match(menu.getPatternUrl(),requestURI))
.flatMap(authorityMenu -> authorityMenu.getRoles().stream())
.map(AuthorityRole::getRoleName)
.toArray(String[]::new);
if (null != roles && roles.length > 0) {
return SecurityConfig.createList(roles);
}
// for (AuthorityMenu menu : allAuthorityMenus) {
// if (antPathMatcher.match(menu.getPatternUrl(),requestURI)) {
// String[] roles = menu.getRoles().stream().map(r -> r.getRoleName()).toArray(String[]::new);
// return SecurityConfig.createList(roles);
// }
// }
return null;
}
@Override
public Collection<ConfigAttribute> getAllConfigAttributes() {
return null;
}
@Override
public boolean supports(Class<?> clazz) {
return FilterInvocation.class.isAssignableFrom(clazz);
}
}

复制代码

@Configuration
@EnableGlobalMethodSecurity(prePostEnabled = true)
public class SecurityConfiguration extends WebSecurityConfigurerAdapter {
@Autowired
private UserDetailsService userDetailsService;
@Autowired
private UrlAntPatchMetadataSource urlAntPatchMetadataSource;
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.userDetailsService(userDetailsService);
}
@Override
protected void configure(HttpSecurity http) throws Exception {
// 1、获取工厂对象
ApplicationContext applicationContext = http.getSharedObject(ApplicationContext.class);
// 2、设置自定义url 匹配规则
http.apply(new UrlAuthorizationConfigurer<>(applicationContext))
.withObjectPostProcessor(new ObjectPostProcessor<FilterSecurityInterceptor>() {
@Override
public <O extends FilterSecurityInterceptor> O postProcess(O object) {
object.setSecurityMetadataSource(urlAntPatchMetadataSource);
// 如果没有权限是否拒绝
object.setRejectPublicInvocations(true);
return object;
}
});
http.authorizeRequests()
.anyRequest().authenticated()
.and().formLogin()//开启表单登录
.and().csrf().disable();
}
}

复制代码

@RestController
public class HelloController {
/**
*
* @return
*/
@RequestMapping("/admin/hello")
public String admin() {
return "hello admin";
}
@RequestMapping("/user/hello")
public String user() {
return "hello user";
}
@RequestMapping("/guest/hello")
public String guest() {
return "hello guest";
}
@RequestMapping("/hello")
public String hello() {
return "hello";
}
}

复制代码