结构化面试应用的权限设计问题（基于Hibernate/JPA与MS SQL）

针对你用Hibernate/JPA + MS SQL开发结构化面试应用，需要给Technical和NonTechnical类型的Question分别配置CRUD权限的需求，我整理了几个实用的方案，从应用层到数据库层都覆盖到了：

首先建议调整实体设计，把Question设为抽象父类，Technical和NonTechnical作为子类，用JPA继承策略来区分类型，这样后续权限控制会更清晰。这里用Joined继承（数据分离更干净，适合不同类型字段差异大的场景）：

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
public abstract class Question {
    @Id
    private String id;
    private String title;
    private String description;
    
    // 省略getter/setter
}

@Entity
@DiscriminatorValue("Technical")
public class Technical extends Question {
    private String topic;
    private String subtopic;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "question")
    private List<Example> examples;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "question")
    private List<Solution> solutions;
    
    // 省略getter/setter
}

@Entity
@DiscriminatorValue("NonTechnical")
public class NonTechnical extends Question {
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "question")
    private List<Solution> solutions;
    
    // 省略getter/setter
}

这是最灵活的权限控制方式，通过@PreAuthorize注解在服务层方法上直接配置角色与操作的对应关系。假设你定义了这些角色：TECH_ADMIN、TECH_EDITOR、TECH_VIEWER、NONTECH_ADMIN、NONTECH_EDITOR、NONTECH_VIEWER，示例代码如下：

服务层接口配置 #

public interface QuestionService {
    // Technical类型Question权限
    @PreAuthorize("hasRole('TECH_ADMIN') or (hasRole('TECH_EDITOR') and #question.class == T(com.yourpackage.Technical))")
    Technical createTechnicalQuestion(Technical question);

    @PreAuthorize("hasRole('TECH_ADMIN') or hasRole('TECH_EDITOR') or hasRole('TECH_VIEWER')")
    Technical getTechnicalQuestion(String id);

    @PreAuthorize("hasRole('TECH_ADMIN') or (hasRole('TECH_EDITOR') and #question.class == T(com.yourpackage.Technical))")
    Technical updateTechnicalQuestion(Technical question);

    @PreAuthorize("hasRole('TECH_ADMIN')")
    void deleteTechnicalQuestion(String id);

    // NonTechnical类型Question权限
    @PreAuthorize("hasRole('NONTECH_ADMIN') or (hasRole('NONTECH_EDITOR') and #question.class == T(com.yourpackage.NonTechnical))")
    NonTechnical createNonTechnicalQuestion(NonTechnical question);

    @PreAuthorize("hasRole('NONTECH_ADMIN') or hasRole('NONTECH_EDITOR') or hasRole('NONTECH_VIEWER')")
    NonTechnical getNonTechnicalQuestion(String id);

    @PreAuthorize("hasRole('NONTECH_ADMIN') or (hasRole('NONTECH_EDITOR') and #question.class == T(com.yourpackage.NonTechnical))")
    NonTechnical updateNonTechnicalQuestion(NonTechnical question);

    @PreAuthorize("hasRole('NONTECH_ADMIN')")
    void deleteNonTechnicalQuestion(String id);
}

Spring Security配置 #

开启方法级安全，并配置用户与角色的关联（实际项目建议从数据库加载用户角色）：

@Configuration
@EnableGlobalMethodSecurity(prePostEnabled = true)
public class SecurityConfig extends WebSecurityConfigurerAdapter {
    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        // 示例：内存用户，实际替换为UserDetailsService从DB获取
        auth.inMemoryAuthentication()
            .withUser("tech_admin").password("{noop}admin123").roles("TECH_ADMIN")
            .and()
            .withUser("tech_editor").password("{noop}editor123").roles("TECH_EDITOR")
            .and()
            .withUser("nontech_admin").password("{noop}admin456").roles("NONTECH_ADMIN")
            .and()
            .withUser("nontech_viewer").password("{noop}viewer456").roles("NONTECH_VIEWER");
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
            .anyRequest().authenticated()
            .and()
            .formLogin(); // 根据你的需求替换为JWT/OAuth2等认证方式
    }
}

如果需要从底层防止绕过应用直接操作数据库的未授权访问，可以用MS SQL的行级安全（RLS），创建安全策略过滤数据：

1. 创建数据库角色 #

CREATE ROLE TechAdmin;
CREATE ROLE TechEditor;
CREATE ROLE TechViewer;
CREATE ROLE NonTechAdmin;
CREATE ROLE NonTechEditor;
CREATE ROLE NonTechViewer;

2. 创建权限判断函数 #

-- 读取权限判断函数
CREATE FUNCTION dbo.fn_QuestionReadAccess(@Type VARCHAR(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT 1 AS AccessAllowed
    WHERE 
        (@Type = 'Technical' AND IS_MEMBER('TechAdmin') = 1)
        OR (@Type = 'Technical' AND IS_MEMBER('TechEditor') = 1)
        OR (@Type = 'Technical' AND IS_MEMBER('TechViewer') = 1)
        OR (@Type = 'NonTechnical' AND IS_MEMBER('NonTechAdmin') = 1)
        OR (@Type = 'NonTechnical' AND IS_MEMBER('NonTechEditor') = 1)
        OR (@Type = 'NonTechnical' AND IS_MEMBER('NonTechViewer') = 1)
);

-- 修改/删除权限判断函数
CREATE FUNCTION dbo.fn_QuestionModifyAccess(@Type VARCHAR(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT 1 AS AccessAllowed
    WHERE 
        (@Type = 'Technical' AND IS_MEMBER('TechAdmin') = 1)
        OR (@Type = 'Technical' AND IS_MEMBER('TechEditor') = 1)
        OR (@Type = 'NonTechnical' AND IS_MEMBER('NonTechAdmin') = 1)
        OR (@Type = 'NonTechnical' AND IS_MEMBER('NonTechEditor') = 1)
);

3. 创建安全策略 #

-- 读取数据的过滤策略
CREATE SECURITY POLICY dbo.QuestionReadPolicy
ADD FILTER PREDICATE dbo.fn_QuestionReadAccess(type) ON dbo.Question
WITH (STATE = ON);

-- 修改/删除数据的拦截策略
CREATE SECURITY POLICY dbo.QuestionModifyPolicy
ADD BLOCK PREDICATE dbo.fn_QuestionModifyAccess(type) ON dbo.Question
FOR UPDATE, DELETE
WITH (STATE = ON);

4. 给数据库用户分配角色 #

ALTER ROLE TechAdmin ADD MEMBER [your_db_user];
-- 根据实际需求给不同用户分配对应角色

建议结合**应用层（Spring Security方法级控制）和数据库层（MS SQL RLS）**的方案：应用层负责业务逻辑中的灵活权限校验，数据库层做底层防护，双重保障更安全。你可以根据实际的角色权限需求，调整上面的角色定义和规则。

内容的提问来源于stack exchange，提问作者ThinkGeek