Row-level data scoping#

Palmyra’s @Permission and the ACL extension control what operations a user can perform — feature-level security. Which rows the user can see or edit is a different concern — data-level security. This page shows how to implement row-level scoping through applyQueryFilter and a shared access-manager class.

The scenario#

Users belong to departments. A regular user should see only records in their own department. Specific senior roles (director, secretary) see everything. The scoping applies to every list endpoint, every export, and every dashboard query.

Access manager#

A single @Component encapsulates the scoping rules. Every handler and controller injects it.

@Component
@RequiredArgsConstructor
public class DataAccessManager {

    private final UserRoleRepo userRoleRepo;
    private final AuthProvider auth;

    /** Global roles that bypass department scoping. */
    private static final Set<String> UNRESTRICTED_ROLES = Set.of(
        "DIRECTOR", "SECRETARY", "ADMIN"
    );

    /**
     * Returns the department IDs the current user is allowed to see,
     * or empty if the user has unrestricted access.
     */
    public Optional<List<Long>> getAllowedDepartments() {
        String user = auth.getUser();
        List<String> roles = userRoleRepo.findRolesByUser(user);

        if (roles.stream().anyMatch(UNRESTRICTED_ROLES::contains)) {
            return Optional.empty();   // no restriction
        }

        List<Long> deptIds = userRoleRepo.findDepartmentIdsByUser(user);
        return Optional.of(deptIds);
    }

    /**
     * Build a SQL WHERE fragment for injection into native queries.
     */
    public String toSqlFragment(String tableAlias) {
        return getAllowedDepartments()
            .map(ids -> tableAlias + ".department_id IN ("
                + ids.stream().map(String::valueOf).collect(Collectors.joining(","))
                + ")")
            .orElse("1=1");
    }
}

Applying to a Palmyra handler#

Override applyQueryFilter and add the department condition. The same handler serves both restricted and unrestricted users — the access manager decides.

@Component
@CrudMapping(mapping = "/project", type = ProjectModel.class,
             secondaryMapping = "/project/{id}")
public class ProjectHandler extends AbstractHandler
        implements QueryHandler, ReadHandler, SaveHandler {

    @Autowired private DataAccessManager accessManager;

    @Override
    public QueryFilter applyQueryFilter(QueryFilter filter, HandlerContext ctx) {
        accessManager.getAllowedDepartments().ifPresent(deptIds -> {
            // Dotted path — Palmyra adds the JOIN automatically
            for (Long deptId : deptIds) {
                filter.addCondition(new SimpleCondition("department.id", deptId));
            }
        });

        if (!filter.hasOrderBy()) filter.addOrderDesc("id");
        return filter;
    }
}

For handlers where the department column is a direct field (not a FK):

filter.sqlExpression("department_id IN (" + deptIds.stream()
    .map(String::valueOf).collect(Collectors.joining(",")) + ")");

Applying to a custom controller / native query#

For dashboard endpoints that use raw SQL, inject the fragment:

@GetMapping("/dashboard/projects-by-stage")
public List<Map<String, Object>> projectsByStage() {
    String baseQuery = DashboardQueries.PROJECTS_BY_STAGE;
    String filter = accessManager.toSqlFragment("p");
    String sql = QueryModifier.inject(baseQuery, Map.of(filter, ""));
    // or simply: String sql = baseQuery + " WHERE " + filter;
    return jdbc.queryForList(sql);
}

Scoping writes too#

The same access manager gates mutations — a user can’t create a project in a department they don’t belong to:

@Override
public Tuple preCreate(Tuple tuple, HandlerContext ctx) {
    Long deptId = tuple.getAttributeAsLong("departmentId");
    accessManager.getAllowedDepartments().ifPresent(allowed -> {
        if (!allowed.contains(deptId)) {
            throw new BusinessException("You are not assigned to department " + deptId);
        }
    });
    return tuple;
}

How this relates to Palmyra’s ACL#

Layer	What it controls	Where it’s defined
`@Permission` / ACL extension	Feature-level — can this user access this endpoint at all?	ACL tables or `@Permission` annotation
`DataAccessManager`	Row-level — which rows does this user see?	Handler `applyQueryFilter` + user-role assignments

Both run on every request. Feature-level runs first (in the filter chain); row-level runs second (in the handler’s query hook). A user who passes the ACL check can still see only their department’s data.