mysql查询某个表权限

转载

mob64ca13f2b62d 2024-09-15 14:02:48

文章标签 mysql查询某个表权限 spring ci ide 文章分类 MySQL 数据库

基于Mysql实现security

实现认证

新建springboot项目

相关依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--security-->
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <!--jsp页面使用jstl标签 不引用的话就没法使用js的一些标签-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>
        <!-- 内置tomcat对Jsp支持的依赖，用于编译Jsp -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <scope>provided</scope>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>8.0.17</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
    </dependencies>

application.yml配置文件

spring:
  mvc:
    view:
      prefix: /WEB-INF/jsp/
      suffix: .jsp
  datasource:
    url: jdbc:mysql://localhost:3306/security_demo01?userSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&allowMultiQueries=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  #configuration和配置文件mybatis-config.xml只能指定一个
  configuration:
    #    开启驼峰命名
    map-underscore-to-camel-case: true
  type-aliases-package: com.dong.security02.pojo
  mapper-locations: classpath:mapper/*.xml

logging:
  level:
    com.bs.dao: debug

  server:
    port: 8080

数据库

CREATE DATABASE`security_demo01` CHARACTER SET 'utf8' COLLATE'utf8_general_ci';

##用户表
CREATE TABLE `user`(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` varchar(64) NOT NULL,
`password` varchar(64) NOT NULL,
`realname` varchar(255) NOT NULL COMMENT '用户姓名',
`mobile` varchar(11) DEFAULT NULL COMMENT '手机号',
PRIMARY KEY (`id`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROw_FORMAT=DYNAMIC

## 密码是123456加密后的密码
INSERT INTO `user` (`username`,`password`,`realname`,`mobile`) 
VALUES("dong","$2a$10$EvBTRL44Wyva2NOCQb6av.2YPugIxXbdtzRhKHq1J1idTVtgh0Ioe","小东","12346578998")

后端代码

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserDto {
    private Integer id;
    private String username;//账号
    private String password;
    private String realName;//用户名
    private String mobile;
}

dao层

package com.dong.security02.dao;

import com.dong.security02.pojo.UserDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

@Repository
@Mapper
public interface UserDao {
    //根据用户名查询用户信息
    @Select("select id,username,realName,mobile from user where username=#{username}")
    UserDto findUserDetailByUserName(String username);
}

service层

import com.dong.security02.dao.UserDao;
import com.dong.security02.pojo.UserDto;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class UserDetailsServiceImpl implements UserDetailsService {
    /**
     * 根据账号查询用户信息
     * 通过接口的方式实现，可以替代继承WebSecurityConfigurerAdapter类的配置类的UserDetailsService方法
     * 源码中：DaoAuthenticationProvider类会根据账户名来调用这个方法获得用户详细信息。
     */
    @Resource
    UserDao userDao;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        //从数据库中查出来的用户
        UserDto user=userDao.findUserDetailByUserName(username);
        if (user==null){
            //只要返回null,就会由DaoAuthenticationProvider类抛出异常
            return null;
        }
        UserDetails userDetails= User.withUsername(user.getUsername()).password(user.getPassword()).authorities("p1","p2").build();
        return userDetails;
    }
}

配置类（Spring Security 控制Session详解）

默认情况下，Spring Security会为每个登录成功的用户会新建一个Session，就是**ifRequired **。

若选用never，则指示Spring Security对登录成功的用户不创建Session，但若你的应用程序在某些地方新建了session，那么Spring Security会用它的。

若使用stateless，则说明Spring Security对登录成功的用尸不会创建session了，你的程序也不会允许新建session。并且它会暗示不使用cookie，所以每个请求都需要重新进行身份验证。这种无状态架构适用于
REST API及其无状态认证机制。以后通过JWT等方式生成token就是不会生成session的。

控制会话如何创建以及SpringSecurity如何与之交互

机制	描述
always	如果没有session存在就创建一个
ifRequired	如果需要就创建一个Session（默认使用的方式）
never	SpringSecurity 将不会创建Session，但是如果应用中其他地方创建了Session，那么Spring Security将会使用它。
stateless	SpringSecurity将绝对不会创建Session

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
    //1. 定义用户详细信息服务  此处通过在service层继承实现，
    // 从数据库读取数据 因此不需要再从内存中读取了
//    @Override
//    public UserDetailsService userDetailsService(){
//        InMemoryUserDetailsManager manager = new InMemoryUserDetailsManager();
//        manager.createUser(User.withUsername("dong").password(passwordEncoder().encode("123456")).roles("admin").authorities("p1","p2").build());
//        manager.createUser(User.withUsername("lang").password(passwordEncoder().encode("123456")).roles("student").authorities("p1").build());
//        //或下面这样写
//        //manager.createUser(new User("admin",passwordEncoder().encode("123456"), AuthorityUtils.createAuthorityList("p1","p2")));
//        return manager;
//    }

    //2. 密码编码器（定义密码是否加密等）
    @Bean
    public PasswordEncoder passwordEncoder() {
        //加密的方式 BCrypt每次加密相同的密码，得到的结果都不同
        //但相同密码加密后解密又都能匹配（解密后原始密码是相同的）
        return new BCryptPasswordEncoder();
    }

    //3. 安全拦截机制
    @Override
    protected void configure(HttpSecurity http) throws Exception {
        //自定义登录：403
        // security为防止CSRE ，限制了除 get以外的大多数方法。
        //解决方法1:屏蔽CSRF控制，即spring security不再限制CSRF。
        http.csrf().disable()
                .authorizeRequests()
                .antMatchers("/r/r1").hasAnyAuthority("p1")
                .antMatchers("/r/r2").hasAnyAuthority("p2")
                .antMatchers("/r/**","/login-success").authenticated() //需要认证才能访问
                .anyRequest().permitAll()//其它请求都允许
                .and()
                .formLogin() //允许基于Form表单登录验证
                .loginPage("/login-page")//自定义登录界面
                .loginProcessingUrl("/login")//对应表单提交时的action="login"
                .successForwardUrl("/login-success")//自定义登录成功的页面地址（可以不写）
             	//下面使用的会话方式是默认的方式。（不写就使用的下面的方式）
                .and()
                .sessionManagement()
                .sessionCreationPolicy(SessionCreationPolicy.IF_REQUIRED);
    }
}

@Configuration
public class WebConfig implements WebMvcConfigurer {
    @Override
    public void addViewControllers(ViewControllerRegistry registry) {
        //控制页眉跳转到登录页面
        registry.addViewController("/").setViewName("redirect:/login-page");
        registry.addViewController("/login-page").setViewName("login");
    }
}

controller

@RestController
public class LoginController {

    @RequestMapping(value = "/login-success" , produces = {"text/plain; charset=UTF-8"})
    public String loginSuccess(){
        return getUsername()+"登录成功";
    }

    /**
     * 用户登录成功后获取用户的信息
     * 获取用户名称
     */
    public String getUsername(){
        String username=null;
        //得到认证通过（登录成功）的用户身份（包含了用户的信息）
        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        //获取用户的身份
        Object principal=authentication.getPrincipal();
        if (principal==null){
            username="匿名用户";
        }
        if(principal instanceof UserDetails){
            UserDetails userDetails=(UserDetails)principal;
            username=userDetails.getUsername();
        }else {
            username=principal.toString();
        }
        return username;
    }

    @RequestMapping(value = "/r/r1" , produces = {"text/plain; charset=UTF-8"})
    public String r1(){
        return getUsername()+"访问资源r1";
    }

    @RequestMapping(value = "/r/r2" , produces = {"text/plain; charset=UTF-8"})
    public String r2(){
        return getUsername()+"访问资源r2";
    }
}

前端

webapp\WEB-INF\jsp\login.jsp（自定义的登录界面）

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <form action="login" method="post">
        用户名：<input type="text" name="username"><br>
        密 码：<input type="password" name="password"><br>
        <input type="submit" value="登录">
    </form>
</body>
</html>

运行程序，登录后因为dong拥p1权限可以访问r/r1,访问r/r2显示403没有权限。（测试可以参考上一篇文章）

自定义退出

退出时会发生的事情：

使HTTP session无效
清除SecurityContextHolder
跳转页面（退出成功后我们一般可以设置跳转到登录页面）

我们简单实现退出功能修改配置文件如下

@Override
    protected void configure(HttpSecurity http) throws Exception {
        //自定义登录：403
        // security为防止CSRE ，限制了除 get以外的大多数方法。
        //解决方法1:屏蔽CSRF控制，即spring security不再限制CSRF。
        http.csrf().disable()
                .authorizeRequests()
                .antMatchers("/r/r1").hasAnyAuthority("p1")
                .antMatchers("/r/r2").hasAnyAuthority("p2")
                .antMatchers("/r/**","/login-success").authenticated() //需要认证才能访问
                .anyRequest().permitAll()//其它请求都允许 注意位置（放在最前面所有请求都会被放行）
                .and()
                .formLogin() //允许基于Form表单登录验证
                .loginPage("/login-page")//自定义登录界面
                .loginProcessingUrl("/login")//对应表单提交时的action="login"
                .successForwardUrl("/login-success")//自定义登录成功的页面地址（可以不写）
                //下面使用的会话方式是默认的方式。（不写就使用的下面的方式）
                .and()
                .sessionManagement()
                .sessionCreationPolicy(SessionCreationPolicy.IF_REQUIRED)
                .and()
                .logout()
                .logoutUrl("/logout")
                .logoutSuccessUrl("/login-page?logout");
    }

当我们输入链接：http://localhost:8080/logout 后，将会退出登录。在访问资源r/r1就不能访问了，需要再次登录后才能访问。

实现数据库的方式授权

授权的方式包括web授权和方法授权，他们都会调用AccessDecisionManager进行授权决策。

web授权是通过url拦截进行授权，web授权则拦截器为FilterSecurityInterceptor ;
方法授权是通过方法拦截进行授权，方法授权则拦截器为MethodSecurityInterceptor。
如果同时通过web授权和方法授权则先执行web授权，再执行方法授权，最后决策通过，则允许访问资源，否则将禁止访问。

Mysql

##角色表
CREATE TABLE `role`(
`id` INT(32) NOT NULL AUTO_INCREMENT COMMENT '权限id',
`role_name`  varchar(255) DEFAULT NULL COMMENT '权限名称',
`description` varchar( 255) DEFAULT NULL COMMENT '权限描述',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`status` char(1) NOT NULL COMMENT '状态',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name`( `role_name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `role` (`role_name`,`description`,`create_time`,`update_time`,`status`)
values ('管理员',NULL,now() ,now(),'1');

## 用户角色关系表
CREATE TABLE user_role(
`user_id` INT(32) NOT NULL COMMENT '用户id',
`role_id` INT(32) NOT NULL COMMENT '角色id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`creator` varchar(255) DEFAULT NULL COMMENT '创建人',
PRIMARY KEY (`user_id` , `role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into user_role (`user_id` , `role_id` , `create_time`, `creator`) 
values( 1, 1 ,now(),NULL);

## 权限表
CREATE TABLE `permission`(
`id` int(32) NOT NULL AUTO_INCREMENT,
`code` varchar(32) NOT NULL COMMENT '权限标识符',
`description` varchar(64) DEFAULT NULL COMMENT '描述',
`url` varchar(128) DEFAULT NULL COMMENT '请求地址',
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `permission` (`code`, `description` , `url` )
values ( 'p1','测试资源1 ', '/r/r1 '),('p2', '测试资源2' ,'/r/r2 ' );

## 角色权限关系表
CREATE TABLE `role_permission`  (
  `role_id` INT(20) NOT NULL COMMENT '角色id',
  `permission_id` INT(20) NOT NULL COMMENT '权限id',
  PRIMARY KEY (`role_id`,`permission_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into role_permission (`role_id`,`permission_id`)
VALUES (1,1),(1,2)

查询用户的权限

查询某个用户所拥有的角色id：SELECT role_id FROM user_role where user_id=1
查询角色所拥有的权限id

SELECT `permission_id` FROM `role_permission` WHERE `role_id` 
IN(SELECT `role_id` FROM `user_role` where `user_id`=1)

查询用户所拥有的全部权限【根据用户的id查询到了用户的权限】

SELECT * FROM `permission` WHERE id IN(
	SELECT `permission_id` FROM `role_permission` WHERE `role_id` IN(
		SELECT `role_id` FROM `user_role` where `user_id`=1
		)
)

改造程序

原来权限是写死的，现在我们通过上面的sql查询到用户的权限。

pojo

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Permission {
    private Integer id;
    private String code;//权限标识符
    private String description;//描述
    private String url;// 请求地址
}

dao层 根据用户的id查询用户的权限

@Repository
@Mapper
public interface UserDao {
    //根据用户名查询用户信息
    @Select("select * from user where username=#{username}")
    UserDto findUserDetailByUserName(String username);

    //根据用户的id查询用户的权限
    List<Permission> findPermissionByUserId(UserDto userDto);
}

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">

<!-- namespace：命名空间，用于隔离sql，还有一个很重要的作用，后面会讲 -->
<mapper namespace="com.dong.security02.dao.UserDao">
    <!-- 根据用户的id查询用户的权限-->
    <select id="findPermissionByUserId" parameterType="UserDto" resultType="Permission">
        SELECT * FROM `permission` WHERE id IN(
            SELECT `permission_id` FROM `role_permission` WHERE `role_id` IN(
                SELECT `role_id` FROM `user_role` where `user_id`=#{id}
            )
        )
    </select>
</mapper>

UserDetailsServiceImpl

@Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        //从数据库中查出来的用户
        UserDto user=userDao.findUserDetailByUserName(username);
        if (user==null){
            //只要返回null,就会由DaoAuthenticationProvider类抛出异常
            return null;
        }
        //根据用户的id查询用户的权限
        List<Permission> permissions=userDao.findPermissionByUserId(user);
        //authorities需要的是一个数组
        String[] authorities=new String[permissions.size()];
        for (int i = 0; i < permissions.size(); i++) {
            authorities[i]=permissions.get(i).getCode();
        }
        UserDetails userDetails= User.withUsername(user.getUsername()).password(user.getPassword()).roles("admin").authorities(authorities).build();
        return userDetails;
    }

运行程序，登录dong用户就能成功访问r/r1和r/r。到此用户的认证和授权都从内存改为了从数据查询的方式了。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。