A quick method for performing Object.count() with Grails and ACL security

ACLcounting.md

When doing pagination in Grails, you need to know the total number of objects a person can see.

When Domain Objects (in this example a Domain Class Project) are secured by ACL, this becomes problematic.

Here's a quick solution to find the number of objects under ACL security that a given user can see.

Basically, if the user is not ROLE_ADMIN, then we join the ACL tables, and return a count of those where the current user is the owner, or has READ | WRITE | ADMINISTRATION flags set

Bootstrap.groovy

    ...

    def init = { servletContext ->
    
        // Add a shim for sql bitwise masking based on db type
        grailsApplication.config.sqlBitwise = { ->
            switch( grailsApplication.config.dataSource.driverClassName ) {
                case ~/.*postgres.*/:
                case ~/.*mysql.*/:
                    return { fieldName, mask -> "$fieldName & $mask" }
                case ~/.*oracle.*/:
                case ~/.*h2.*/:
                    return { fieldName, mask -> "BITAND( $fieldName, $mask )" }
                default:
                    throw new RuntimeException( 'Unknown DB' )
            }
        }()
        
        ...
    }
    
    ...

ProjectService.groovy

import grails.plugins.springsecurity.Secured
import org.codehaus.groovy.grails.plugins.springsecurity.SpringSecurityUtils
import org.springframework.security.access.prepost.PostFilter
import static org.springframework.security.acls.domain.BasePermission.ADMINISTRATION
import static org.springframework.security.acls.domain.BasePermission.READ
import static org.springframework.security.acls.domain.BasePermission.WRITE

import groovy.sql.Sql

class ProjectService {
    def dataSource
    def grailsApplication
    def springSecurityService

    @Secured( 'ROLE_USER' )
    int count() {
        // Administrators see all
        if( SpringSecurityUtils.ifAllGranted( 'ROLE_ADMIN' ) ) {
            Project.count()
        }
        else {
            // Count the number of Projects seen by this user
            def sql = $/SELECT count( distinct project.id ) AS cnt FROM project
                       |  JOIN acl_object_identity AS aoi ON ( ae.acl_object_identity = project.id )
                       |  JOIN acl_entry AS ae ON ( ae.acl_object_identity = aoi.id )
                       |  JOIN acl_class as aclazz ON ( aclazz.class = :domainClass AND aclazz.id = aoi.object_id_class )
                       |  JOIN acl_sid as entry_si ON ( entry_si.id = ae.sid )
                       |  JOIN acl_sid as owner ON ( owner.id = aoi.owner_sid )
                       |  WHERE owner.sid = :username OR 
                       |          ( entry_si.sid = :username AND ${grailsApplication.config.sqlBitwise( 'ae.mask', ':mask' )} > 0 )/$.stripMargin()
            new Sql( dataSource ).firstRow( sql, [ username: springSecurityService.currentUser.username,
                                                   domainClass: Project.name,
                                                   mask: READ.mask | WRITE.mask | ADMINISTRATION.mask ] ).CNT
        }
    }
}