hibernate - Grails Spring Security查询没有特定角色的用户

Question

使用Grails spring security REST(本身使用Grails Spring Security Core)，我生成了User，Role和UserRole类。

用户:

class User extends DomainBase{

    transient springSecurityService

    String username
    String password
    String firstName
    String lastNameOrTitle
    String email
    boolean showEmail
    String phoneNumber
    boolean enabled = true
    boolean accountExpired
    boolean accountLocked
    boolean passwordExpired

    static transients = ['springSecurityService']

    static hasMany = [
            roles: Role,
            ratings: Rating,
            favorites: Favorite
    ]

    static constraints = {
        username blank: false, unique: true
        password blank: false
        firstName nullable: true, blank: false
        lastNameOrTitle nullable: false, blank: false
        email nullable: false, blank: false
        phoneNumber nullable: true
    }

    static mapping = {
        DomainUtil.inheritDomainMappingFrom(DomainBase, delegate)
        id column: 'user_id', generator: 'sequence', params: [sequence: 'user_seq']
        username column: 'username'
        password column: 'password'
        enabled column: 'enabled'
        accountExpired column: 'account_expired'
        accountLocked column: 'account_locked'
        passwordExpired column: 'password_expired'
        roles joinTable: [
                name: 'user_role',
                column: 'role_id',
                key: 'user_id']
    }

    Set<Role> getAuthorities() {
//        UserRole.findAllByUser(this).collect { it.role }
//        userRoles.collect { it.role }
        this.roles
    }

    def beforeInsert() {
        encodePassword()
    }

    def beforeUpdate() {
        super.beforeUpdate()
        if (isDirty('password')) {
            encodePassword()
        }
    }

    protected void encodePassword() {
        password = springSecurityService?.passwordEncoder ? springSecurityService.encodePassword(password) : password
    }
}

角色:

class Role {

    String authority

    static mapping = {
        cache true
        id column: 'role_id', generator: 'sequence', params: [sequence: 'role_seq']
        authority column: 'authority'
    }

    static constraints = {
        authority blank: false, unique: true
    }
}

UserRole:

class UserRole implements Serializable {

    private static final long serialVersionUID = 1

    static belongsTo = [
            user: User,
            role: Role
    ]
//    User user
//    Role role

    boolean equals(other) {
        if (!(other instanceof UserRole)) {
            return false
        }

        other.user?.id == user?.id &&
                other.role?.id == role?.id
    }

    int hashCode() {
        def builder = new HashCodeBuilder()
        if (user) builder.append(user.id)
        if (role) builder.append(role.id)
        builder.toHashCode()
    }

    static UserRole get(long userId, long roleId) {
        UserRole.where {
            user == User.load(userId) &&
                    role == Role.load(roleId)
        }.get()
    }

    static boolean exists(long userId, long roleId) {
        UserRole.where {
            user == User.load(userId) &&
                    role == Role.load(roleId)
        }.count() > 0
    }

    static UserRole create(User user, Role role, boolean flush = false) {
        def instance = new UserRole(user: user, role: role)
        instance.save(flush: flush, insert: true)
        instance
    }

    static boolean remove(User u, Role r, boolean flush = false) {
        if (u == null || r == null) return false

        int rowCount = UserRole.where {
            user == User.load(u.id) &&
                    role == Role.load(r.id)
        }.deleteAll()

        if (flush) {
            UserRole.withSession { it.flush() }
        }

        rowCount > 0
    }

    static void removeAll(User u, boolean flush = false) {
        if (u == null) return

        UserRole.where {
            user == User.load(u.id)
        }.deleteAll()

        if (flush) {
            UserRole.withSession { it.flush() }
        }
    }

    static void removeAll(Role r, boolean flush = false) {
        if (r == null) return

        UserRole.where {
            role == Role.load(r.id)
        }.deleteAll()

        if (flush) {
            UserRole.withSession { it.flush() }
        }
    }

    static constraints = {
        role validator: { Role r, UserRole ur ->
            if (ur.user == null) return
            boolean existing = false
            UserRole.withNewSession {
                existing = UserRole.exists(ur.user.id, r.id)
            }
            if (existing) {
                return 'userRole.exists'
            }
        }
    }

    static mapping = {
        id composite: ['role', 'user']
        version false
    }
}

现在，我希望创建一个管理员区域，管理员可以在其中修改/启用用户帐户，但不能接触其他管理员，因此，我决定创建一个可分页查询，该查询将仅选择没有 ROLE_ADMIN的用户角色，因为管理员同时具有 ROLE_USER和 ROLE_ADMIN角色。

从上面的代码可以看出，我已经稍微修改了默认生成的代码，并在 joinTable类中添加了 User而不是 hasMany: [roles:UserRole]或将其保留为默认值而不引用任何角色。进行此更改的原因是因为查询UserRole时，偶尔会出现重复，这会使分页变得很困难。

因此，使用当前的设置，我设法创建了两个查询，这些查询使我可以仅提取没有管理员角色的用户。

def rolesToIgnore = ["ROLE_ADMIN"]
def userIdsWithGivenRoles = User.createCriteria().list() {
    projections {
        property "id"
    }
    roles {
        'in' "authority", rolesToIgnore
    }
}

def usersWithoutGivenRoles = User.createCriteria().list(max: 10, offset: 0) {
    not {
        'in' "id", userIdsWithGivenRoles
    }
}

第一个查询获取具有 ROLE_ADMIN角色的所有用户ID的列表，然后第二个查询获取其ID不在上一个列表中的所有用户。

这有效且可分页，但是由于两个原因而困扰我:

用户上的

joinTable对我来说似乎“很讨厌”。当我已经有一个特定的类joinTable时，为什么要使用UserRole，但是该类很难查询，即使我只需要Role，我也担心为每个找到的User映射User可能会产生开销。

两个查询，只有第二个可以分页。

所以我的问题是:
有没有一种更优化的方法来构造查询来获取不包含某些角色的用户(而无需将数据库重组为每个用户只有一个角色的金字塔角色系统)？

两个查询绝对必要吗？我试图构造一个纯SQL查询，没有子查询我就做不到。

Answer 1

如果您的UserRole具有用户和角色属性而不是belongsTo，则如下所示:

class UserRole implements Serializable {

    private static final long serialVersionUID = 1

    User user
    Role role
    ...
}

然后，您可以执行以下操作:

def rolesToIgnore = ["ROLE_ADMIN"]

def userIdsWithGivenRoles = UserRole.where {
    role.authority in rolesToIgnore
}.list().collect { it.user.id }.unique()

def userIdsWithoutGivenRoles = UserRole.where {
    !(role.authority in rolesToIgnore)
}.list().collect { it.user.id }.unique()

我很讨厌投影，所以我用unique()删除了重复项。

SQL等效项是:

SELECT DISTINCT ur.user_id
FROM    user_role AS ur INNER JOIN role AS r
        ON ur.authority_id = r.id
WHERE   r.authority IN ('ROLE_ADMIN');

SELECT DISTINCT ur.user_id
FROM    user_role AS ur INNER JOIN role AS r
        ON ur.authority_id = r.id
WHERE   r.authority NOT IN ('ROLE_ADMIN');