Constrain a select query, only retrieve records that user is allowed to view

[jszobody]

I'm trying to do something that seems like it should be straightforward and I'm struggling. Feel like I'm missing something obvious.

I have an app where users are granted explicit permissions to access specific projects. You know, something like this:

$user->allow('view', Project::find(1));
$user->allow('view', Project::find(2));

Simple stuff.

So then when the user logs in to the app, they should be able to view a list of all their projects, paginated. This is where I get stuck.

Project::orderBy('name')
    -> // how the heck do I filter by projects that the user is permitted to view here?
    ->paginate(20);

I think I could probably do something like this (untested):

Project::orderBy('name')
    ->whereIn('id', 
        $user->abilities()->where('entity_type', 'project')->get()->pluck('entity_id')->toArray()
    )->paginate(20);

But, ewww. That looks nasty, and it runs two queries. At a minimum I'd want to figure out a sub-query approach.

What am I missing? What's the elegant Bouncer way to constrain a big SELECT query and filter the records by permission?

Thanks!

[jszobody]

So I found this helper:

$user->abilities()->forModel(Project::class)

But when I inspect that query I'm seeing at the end...

 `abilities`.`entity_type` = '*' or (`abilities`.`entity_type` = 'project' and (`abilities`.`entity_id` is null))

So this is only looking for project abilities where the id is null, hrmm. I need to also include project IDs explicitly granted so I can include that in my project query.

[cjaoude]

Caveat, just reading thru Bouncer now, so don't know the methods...

I would think a User can have many Projects, and a Project can have many users, so first you'll need a pivot. Then you constrain the query, something like this...

$projects = Project::whereHas('users', function (Builder $query) {
    $query->where('user_id', auth()->id());
    $query->bouncerAbilityFoo();  // something like that here i'm thinking
})->get();

@jszobody

[poirelpa]

I have added this scope to my BaseModel class, inherited by all my models.

// in BaseModel.php, or in each model
public function scopeWhereUserCan($query, $ability, $user = null){
    if(empty($user)) $user = auth()->id();

    return $query->whereRaw("id in (
        select e.id from {$this->table} e
        inner join abilities a
            on (a.entity_type = ? or a.entity_type = '*') 
            and (a.entity_id = e.id or a.entity_id is null) and (a.name =? or a.name='*')
        inner join permissions p
            on p.ability_id = a.id and not p.forbidden
        left join roles r
            on p.entity_id = r.id and p.entity_type = 'roles'
        left join assigned_roles ar
            on ar.role_id = r.id
        inner join users u
            on (u.id = ? and (u.id = ar.entity_id and ar.entity_type = 'user') 
            or u.id = (p.entity_id and p.entity_type = 'user'))
            ". (in_array('user_id',$this->fillable) ? 'and (a.only_owned = 0 or u.id = e.user_id)':'')."
        )",[$this->getMorphClass(), $ability, $user]);
}

// in the controller
Post::whereUserCan('read')->get()

This solution does not handle forbidden abilities, and it is not thoroughly tested, but it seems to work for my needs. It would be better to filter the collection by $user->can()

[devmsh]

Any plan to support this in the library? It looks perfect when we authorize a User against a Model, but it's really become useless when we need to query the database and return Model(s) based on permissions!

I think a global scope must be booted by default (just like SoftDelete) with the option to disable this feature from the config file?

[pintend]

I was able to get a list of Projects by first making a custom Ability model with

// Somewhere in a service provider
Bouncer::useAbilityModel(\App\Models\Bouncer\Ability::class);

i then added the project relationship

namespace App\Models\Bouncer;

use Silber\Bouncer\Database\Ability as BaseAbility;

class Ability extends BaseAbility
{
    public function account()
    {
        return $this->morphTo('entity');
    }
}

I can now get a list of the relationship with

Abilities::forAuthority(Auth::user(), true)
    ->byName('*') // name of permission your looking for
    ->where(Models::table('abilities').'.entity_type', \App\Models\Project::class) // ->forModel($project) requires an instance, we are trying to get all 'instances'
    ->with('project')
    ->select(['id', 'entity_id', 'entity_type']) // Optional, we are just selecting the columns from abilities table that we need
    ->get()->map->entity; // returns all the projects

You can cleanup the where clause by creating a custom scope. For example:

    public function scopeForModelClass($query, $model)
    {
        return $query->where(Models::table('abilities').'.entity_type', $model);
    }

you can then replace your ->where() with:

    ->forModelClass(\App\Models\Project::class) // Must be fully qualified class name

You can also query like this

$user->abilities()
    ->byName('*')
    ->forModelClass(\App\Models\project::class)
    ->with('account')
    ->select(['abilities.id', 'abilities.entity_id', 'abilities.entity_type']) // Need to have the table name if your querying from the user (this select is still optional)
    ->get()->map->entity;

If using the cache, you can eliminate the extra query

$ids = $user->getAbilities()
    ->filter(function ($ability) {
        return $ability->name == '*' && // ->byName()
            $ability->entity_type == \App\Models\Project::class; // ->forModelClass()
    })
    ->pluck('entity_id');

Project::whereIn('id', $ids)->get();

(i didn't notice much difference between the two versions, but i didn't test with large data)