SpringBoot/JPA - data row level authorization

user_1315787 0
Viewed 376

I'm looking for a way to authorize access based on ROLES and DATA CONTENT (table row) using Spring Boot & JPA

The use case is the following:

Given a User with a Role 'ROLE_AAA'
When user fetches data from table 'REPORT'
Then only reports with specific 'REPORT_ROLE' association will be returned

ER

Table: role

ID ROLE
1 ROLE_AAA
2 ROLE_BBB

Table: report

ID CONTENT
1 ...
2 ...
3 ...
4 ...
5 ...

Table: report_roles

REPORT_ID ROLE_ID
1 1
2 1
3 2
4 2
5 1

In this case a user with role ROLE_AAA will get reports with ID 1,2 & 5 only.

SELECT re.*
FROM report re
JOIN report_role rr
ON re.id = rr.report_id
JOIN role ro
ON ro.id = rr.role_id
WHERE ro.name = 'ROLE_AAA'

I could easily write queries (@org.springframework.data.jpa.repository.Query) joining on table I want to get data from, with the association role table, but I feel I'm missing a JPA or Spring Security feature that might offer it out off the box.

Any design/implementation suggestion will be very appreciated.

UPDATE

Since returns will be paginated (retuning Page<Object> from JPARepository), I believe PostFiltering is not an option here.

spring-boot spring-security jpa hibernate java
1 Answers

Here is an older article I wrote on the subject of using @Pre/Post annotations with a custom PermissionEvaluator. It seems your best bet here, and allows you to purely use annotations instead of custom queries. Of course, it only works if there aren't too many reports being returned from your query (without a ro.name = 'ROLE_AAA' filter).

In your case however, you aren't really trying to use the hasPermission(filterObject, 'xyz') syntax because your use case is role-based, meaning permissions are roles and are already in the authentication object (e.g. authorities) and the database (e.g. report_roles). With a M:N relationship between user roles and report roles, you can implement a helper to do the check for you, like this:

@RestController
public class ReportsController {
    @GetMapping("/reports")
    @PostFilter("@reportExpressions.hasAnyRole(filterObject, authentication)")
    public List<Report> getReports() {
        return new ArrayList<>(List.of(
            new Report("Report #1", Set.of("ROLE_AAA", "ROLE_BBB")),
            new Report("Report #2", Set.of("ROLE_AAA", "ROLE_CCC"))
        ));
    }

    @Component("reportExpressions")
    public static class ReportExpressions {
        public boolean hasAnyRole(Report report, Authentication authentication) {
            Set<String> authorities = AuthorityUtils.authorityListToSet(
                authentication.getAuthorities());
            return report.getRoles().stream().anyMatch(authorities::contains);
        }
    }

    public static class Report {
        private final String name;
        private final Set<String> roles;

        public Report(String name, Set<String> roles) {
            this.name = name;
            this.roles = roles;
        }

        public String getName() {
            return name;
        }

        public Set<String> getRoles() {
            return roles;
        }
    }
}

If the current user has ROLE_AAA, they will see both reports, etc. My example uses a controller, but you can apply the same annotation to a JPA repository.

user_158350390
Related