Got entity relation query permission application working

May be issues at points of use though, Added todo for this in code.
Also added extra indexes to collapsed table for better query
performance.

diff --git a/app/Actions/ActivityQueries.php b/app/Actions/ActivityQueries.php
index 0e9cbdebb4b003305bba1045dc59ac4888d764ad..852913e634eb46e67909bf39154552d7d3e49515 100644 (file)
--- a/app/Actions/ActivityQueries.php
+++ b/app/Actions/ActivityQueries.php
@@ -25,8 +25,9 @@ class ActivityQueries
      */
     public function latest(int $count = 20, int $page = 0): array
     {
+        $query = Activity::query()->select(['id', 'type', 'detail', 'activities.entity_type', 'activities.entity_id', 'user_id', 'created_at']);
         $activityList = $this->permissions
-            ->restrictEntityRelationQuery(Activity::query(), 'activities', 'entity_id', 'entity_type')
+            ->restrictEntityRelationQuery($query, 'activities', 'entity_id', 'entity_type')
             ->orderBy('created_at', 'desc')
             ->with(['user', 'entity'])
             ->skip($count * $page)

diff --git a/app/Auth/Permissions/PermissionApplicator.php b/app/Auth/Permissions/PermissionApplicator.php
index 00c957c3bb5777d3b64689b76a598dfc54276dd4..64850fd8adda6f5c5bd4442141e896e4230a7949 100644 (file)
--- a/app/Auth/Permissions/PermissionApplicator.php
+++ b/app/Auth/Permissions/PermissionApplicator.php
@@ -161,33 +161,49 @@ class PermissionApplicator
      */
     public function restrictEntityQuery(Builder $query, string $morphClass): Builder
     {
-        $this->getCurrentUserRoleIds();
-        $this->currentUser()->id;
-
         // TODO - Leave this as the new admin workaround?
         //   Or auto generate collapsed role permissions for admins?
         if (\user()->hasSystemRole('admin')) {
             return $query;
         }
 
-        // Apply permission level joins
-        $this->applyFallbackJoin($query, $morphClass, 'id', '');
-        $this->applyRoleJoin($query, $morphClass, 'id', '');
-        $this->applyUserJoin($query, $morphClass, 'id', '');
-
-        // Where permissions apply
-        $this->applyPermissionWhereFilter($query, $morphClass);
+        $this->applyPermissionsToQuery($query, $query->getModel()->getTable(), $morphClass, 'id', '');
 
         return $query;
     }
 
-    protected function applyPermissionWhereFilter($query, string $entityTypeLimiter)
+    /**
+     * @param Builder|QueryBuilder $query
+     * @return void
+     */
+    protected function applyPermissionsToQuery($query, string $queryTable, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
+    {
+        $this->applyFallbackJoin($query, $queryTable, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
+        $this->applyRoleJoin($query, $queryTable, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
+        $this->applyUserJoin($query, $queryTable, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
+        $this->applyPermissionWhereFilter($query, $queryTable, $entityTypeLimiter, $entityTypeColumn);
+    }
+
+    /**
+     * Apply the where condition to a permission restricting query, to limit based upon the values of the joined
+     * permission data. Query must have joins pre-applied.
+     * Either entityTypeLimiter or entityTypeColumn should be supplied, with the other empty.
+     * Both should not be applied since that would conflict upon intent.
+     * @param Builder|QueryBuilder $query
+     */
+    protected function applyPermissionWhereFilter($query, string $entityTypeLimiter, string $entityTypeColumn)
     {
-        // TODO - Morph for all types
-        $userViewAll = userCan($entityTypeLimiter . '-view-all');
-        $userViewOwn = userCan($entityTypeLimiter . '-view-own');
+        $abilities = ['all' => [], 'own' => []];
+        $types = $entityTypeLimiter ? [$entityTypeLimiter] : ['page', 'chapter', 'bookshelf', 'book'];
+        foreach ($types as $type) {
+            $abilities['all'][$type] = userCan($type . '-view-all');
+            $abilities['own'][$type] = userCan($type . '-view-own');
+        }
 
-        $query->where(function (Builder $query) use ($userViewOwn, $userViewAll) {
+        $abilities['all'] = array_filter($abilities['all']);
+        $abilities['own'] = array_filter($abilities['own']);
+
+        $query->where(function (Builder $query) use ($abilities, $entityTypeColumn) {
             $query->where('perms_user', '=', 1)
                 ->orWhere(function (Builder $query) {
                     $query->whereNull('perms_user')->where('perms_role', '=', 1);
@@ -196,14 +212,22 @@ class PermissionApplicator
                         ->where('perms_fallback', '=', 1);
                 });
 
-            if ($userViewAll) {
-                $query->orWhere(function (Builder $query) {
+            if (count($abilities['all']) > 0) {
+                $query->orWhere(function (Builder $query) use ($abilities, $entityTypeColumn) {
                     $query->whereNull(['perms_user', 'perms_role', 'perms_fallback']);
+                    if ($entityTypeColumn) {
+                        $query->whereIn($entityTypeColumn, array_keys($abilities['all']));
+                    }
                 });
-            } else if ($userViewOwn) {
-                $query->orWhere(function (Builder $query) {
+            }
+
+            if (count($abilities['own']) > 0) {
+                $query->orWhere(function (Builder $query) use ($abilities, $entityTypeColumn) {
                     $query->whereNull(['perms_user', 'perms_role', 'perms_fallback'])
                         ->where('owned_by', '=', $this->currentUser()->id);
+                    if ($entityTypeColumn) {
+                        $query->whereIn($entityTypeColumn, array_keys($abilities['all']));
+                    }
                 });
             }
         });
@@ -212,9 +236,9 @@ class PermissionApplicator
     /**
      * @param Builder|QueryBuilder $query
      */
-    protected function applyPermissionJoin(callable $joinCallable, string $subAlias, $query, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
+    protected function applyPermissionJoin(callable $joinCallable, string $subAlias, $query, string $queryTable, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
     {
-        $joinCondition = $this->getJoinCondition($subAlias, $entityIdColumn, $entityTypeColumn);
+        $joinCondition = $this->getJoinCondition($queryTable, $subAlias, $entityIdColumn, $entityTypeColumn);
 
         $query->joinSub(function (QueryBuilder $joinQuery) use ($joinCallable, $entityTypeLimiter) {
             $joinQuery->select(['entity_id', 'entity_type'])->from('entity_permissions_collapsed')
@@ -230,43 +254,43 @@ class PermissionApplicator
     /**
      * @param Builder|QueryBuilder $query
      */
-    protected function applyUserJoin($query, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
+    protected function applyUserJoin($query, string $queryTable, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
     {
         $this->applyPermissionJoin(function (QueryBuilder $joinQuery) {
             $joinQuery->selectRaw('max(view) as perms_user')
                 ->where('user_id', '=', $this->currentUser()->id);
-        }, 'p_u', $query, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
+        }, 'p_u', $query, $queryTable, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
     }
 
 
     /**
      * @param Builder|QueryBuilder $query
      */
-    protected function applyRoleJoin($query, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
+    protected function applyRoleJoin($query, string $queryTable, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
     {
         $this->applyPermissionJoin(function (QueryBuilder $joinQuery) {
             $joinQuery->selectRaw('max(view) as perms_role')
                 ->whereIn('role_id', $this->getCurrentUserRoleIds());
-        }, 'p_r', $query, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
+        }, 'p_r', $query, $queryTable, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
     }
 
     /**
      * @param Builder|QueryBuilder $query
      */
-    protected function applyFallbackJoin($query, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
+    protected function applyFallbackJoin($query, string $queryTable, string $entityTypeLimiter, string $entityIdColumn, string $entityTypeColumn)
     {
         $this->applyPermissionJoin(function (QueryBuilder $joinQuery) {
             $joinQuery->selectRaw('max(view) as perms_fallback')
                 ->whereNull(['role_id', 'user_id']);
-        }, 'p_f', $query, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
+        }, 'p_f', $query, $queryTable, $entityTypeLimiter, $entityIdColumn, $entityTypeColumn);
     }
 
-    protected function getJoinCondition(string $joinTableName, string $entityIdColumn, string $entityTypeColumn): callable
+    protected function getJoinCondition(string $queryTable, string $joinTableName, string $entityIdColumn, string $entityTypeColumn): callable
     {
-        return function (JoinClause $join) use ($joinTableName, $entityIdColumn, $entityTypeColumn) {
-            $join->on($entityIdColumn, '=', $joinTableName . '.entity_id');
+        return function (JoinClause $join) use ($queryTable, $joinTableName, $entityIdColumn, $entityTypeColumn) {
+            $join->on($queryTable . '.' . $entityIdColumn, '=', $joinTableName . '.entity_id');
             if ($entityTypeColumn) {
-                $join->on($entityTypeColumn, '=', $joinTableName . '.entity_type');
+                $join->on($queryTable . '.' . $entityTypeColumn, '=', $joinTableName . '.entity_type');
             }
         };
     }
@@ -295,49 +319,12 @@ class PermissionApplicator
      */
     public function restrictEntityRelationQuery($query, string $tableName, string $entityIdColumn, string $entityTypeColumn)
     {
-        $tableDetails = ['tableName' => $tableName, 'entityIdColumn' => $entityIdColumn, 'entityTypeColumn' => $entityTypeColumn];
-        $pageMorphClass = (new Page())->getMorphClass();
+        $this->applyPermissionsToQuery($query, $tableName, '', $entityIdColumn, $entityTypeColumn);
+        // TODO - Test page draft access (Might allow drafts which should not be seen)
+        // TODO - Test each use of this to check column/relation fetching.
+        //    Original queries might need selects applied to limit field exposure and to get right original table columns.
 
-        // TODO - Abstract the permission queries above to make their join columns configurable
-        //   so the query methods can be used on non-entity tables if possible.
         return $query;
-
-        $q = $query->where(function ($query) use ($tableDetails) {
-            $query->whereExists(function ($permissionQuery) use ($tableDetails) {
-                /** @var Builder $permissionQuery */
-                $permissionQuery->select(['role_id'])->from('joint_permissions')
-                    ->whereColumn('joint_permissions.entity_id', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityIdColumn'])
-                    ->whereColumn('joint_permissions.entity_type', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityTypeColumn'])
-                    ->whereIn('joint_permissions.role_id', $this->getCurrentUserRoleIds())
-                    ->where(function (QueryBuilder $query) {
-                        $this->addJointHasPermissionCheck($query, $this->currentUser()->id);
-                    });
-            })->orWhereExists(function ($permissionQuery) use ($tableDetails) {
-                /** @var Builder $permissionQuery */
-                $permissionQuery->select(['user_id'])->from('joint_user_permissions')
-                    ->whereColumn('joint_user_permissions.entity_id', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityIdColumn'])
-                    ->whereColumn('joint_user_permissions.entity_type', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityTypeColumn'])
-                    ->where('joint_user_permissions.user_id', '=', $this->currentUser()->id)
-                    ->where('joint_user_permissions.has_permission', '=', true);
-            });
-        })->whereNotExists(function ($query) use ($tableDetails) {
-            $query->select(['user_id'])->from('joint_user_permissions')
-                ->whereColumn('joint_user_permissions.entity_id', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityIdColumn'])
-                ->whereColumn('joint_user_permissions.entity_type', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityTypeColumn'])
-                ->where('joint_user_permissions.user_id', '=', $this->currentUser()->id)
-                ->where('joint_user_permissions.has_permission', '=', false);
-        })->where(function ($query) use ($tableDetails, $pageMorphClass) {
-            /** @var Builder $query */
-            $query->where($tableDetails['entityTypeColumn'], '!=', $pageMorphClass)
-                ->orWhereExists(function (QueryBuilder $query) use ($tableDetails, $pageMorphClass) {
-                    $query->select('id')->from('pages')
-                        ->whereColumn('pages.id', '=', $tableDetails['tableName'] . '.' . $tableDetails['entityIdColumn'])
-                        ->where($tableDetails['tableName'] . '.' . $tableDetails['entityTypeColumn'], '=', $pageMorphClass)
-                        ->where('pages.draft', '=', false);
-                });
-        });
-
-        return $q;
     }
 
     /**

diff --git a/database/migrations/2022_12_22_103318_create_collapsed_role_permissions_table.php b/database/migrations/2022_12_22_103318_create_collapsed_role_permissions_table.php
index c35fe973d40e7f086cad24fb9c0bc47e9215164a..6974532ced1749bcbe8068ab64471e6661fafe6f 100644 (file)
--- a/database/migrations/2022_12_22_103318_create_collapsed_role_permissions_table.php
+++ b/database/migrations/2022_12_22_103318_create_collapsed_role_permissions_table.php
@@ -18,8 +18,8 @@ class CreateCollapsedRolePermissionsTable extends Migration
 
         Schema::create('entity_permissions_collapsed', function (Blueprint $table) {
             $table->id();
-            $table->unsignedInteger('role_id')->nullable();
-            $table->unsignedInteger('user_id')->nullable();
+            $table->unsignedInteger('role_id')->nullable()->index();
+            $table->unsignedInteger('user_id')->nullable()->index();
             $table->string('entity_type');
             $table->unsignedInteger('entity_id');
             $table->boolean('view')->index();