Creating a row-level role

Hello, is there a way to do something like this?

@RowLevelRole(
name = “userPartOfProjectTeam”,
code = “user-part-of-project-team”)
public interface UserPartOfProjectTeamRole {

@JpqlRowLevelPolicy(
        entityClass = Project.class,
        where = "{E}.teams.users IN :current_user"
)
void userProjects();

}

I didn’t found a way parse current logged user to jpql query… I want to get all projects where current user is assigned. Teams and Users are Lists.

TEAMS <> USERS @ManyToMany
PROJECTS <> TEAMS @ManyToMany

In SQL it will be something like this:

SELECT dbo.TEAM_USER_LINK.USER_ID, dbo.TEAM_PROJECT_LINK.PROJECT_ID
FROM dbo.TEAM INNER JOIN
dbo.TEAM_USER_LINK ON dbo.TEAM.ID = dbo.TEAM_USER_LINK.TEAM_ID AND dbo.TEAM.ID = dbo.TEAM_USER_LINK.TEAM_ID INNER JOIN
dbo.TEAM_PROJECT_LINK ON dbo.TEAM.ID = dbo.TEAM_PROJECT_LINK.TEAM_ID
WHERE (dbo.TEAM_USER_LINK.USER_ID = ‘6FA8A5A0-E14E-8BE6-00C3-6E877B9D7A45’)

How to do it as Row-Level Policy?

Okay I handled it. If someone would need a solution here you go:

@RowLevelRole(
name = “userPartOfProjectTeam”,
code = “user-part-of-project-team”)
public interface UserPartOfProjectTeamRole {

@JpqlRowLevelPolicy(
        entityClass = Project.class,
        join = "join e.teams t join t.users u",
        where = "u.username = :current_user_username"
)
void userProjects();

}

Have a nice day :wink:

2 Likes