Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
Timestamp:
12/20/17 11:27:33 (7 years ago)
Author:
jzenisek
Message:

#2839 worked on permission checks in listing methods

Location:
branches/HiveProjectManagement
Files:
9 edited

Legend:

Unmodified
Added
Removed

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs

    r15530 r15552  
    1818    }
    1919
     20    public void DeleteByProjectId(Guid projectId) {
     21      DataContext.ExecuteCommand(DeleteByProjectIdQueryString, projectId);
     22    }
     23
     24    public void DeleteByProjectIdAndUserIds(Guid projectId, IEnumerable<Guid> userIds) {
     25      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
     26      if (!string.IsNullOrWhiteSpace(paramUserIds)) {
     27        string queryString = string.Format(DeleteByProjectIdAndUserIdsQueryString, projectId, paramUserIds);
     28        DataContext.ExecuteCommand(queryString);
     29      }
     30    }
     31
     32    public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) {
     33      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
     34      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
     35      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
     36        string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds);
     37        DataContext.ExecuteCommand(queryString);
     38      }
     39    }
     40
     41    public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) {
     42      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
     43      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
     44        string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
     45        DataContext.ExecuteCommand(queryString);
     46      }
     47    }
     48
     49    public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) {
     50      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
     51      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
     52      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) {
     53        string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds);
     54        DataContext.ExecuteCommand(queryString);
     55      }
     56    }
     57
    2058    public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
    2159      return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
    … …  
    4482
    4583    #region String queries
     84    private const string DeleteByProjectIdQueryString = @"
     85      DELETE FROM [AssignedJobResource] ajr
     86      WHERE ajr.JobId IN
     87        (
     88          SELECT j.JobId
     89          FROM [Job] j
     90          WHERE j.ProjectId = {0}
     91        )
     92    ";
     93    private const string DeleteByProjectIdAndUserIdsQueryString = @"
     94      DELETE FROM [AssignedJobResource] ajr
     95      WHERE ajr.JobId IN
     96        (
     97          SELECT j.JobId
     98          FROM [Job] j
     99          WHERE j.ProjectId = {0}
     100          AND j.OwnerUserId IN ({1})
     101        )
     102    ";
     103    private const string DeleteByProjectIdsAndUserIdsQueryString = @"
     104      DELETE FROM [AssignedJobResource] ajr
     105      WHERE ajr.JobId IN
     106        (
     107          SELECT j.JobId
     108          FROM [Job] j
     109          WHERE j.ProjectId IN ({0})
     110          AND j.OwnerUserId IN ({1})
     111        )
     112    ";
     113    private const string DeleteByProjectIdAndResourceIdsQueryString = @"
     114      DELETE FROM [AssignedJobResource] ajr
     115      WHERE ajr.JobId IN
     116        (
     117          SELECT j.JobId
     118          FROM [Job] j
     119          WHERE j.ProjectId = {0}
     120        )
     121      AND ajr.ResourceId IN ({1})
     122    ";
     123    private const string DeleteByProjectIdsAndResourceIdsQueryString = @"
     124      DELETE FROM [AssignedJobResource] ajr
     125      WHERE ajr.JobId IN
     126        (
     127          SELECT j.JobId
     128          FROM [Job] j
     129          WHERE j.ProjectId IN ({0})
     130        )
     131      AND ajr.ResourceId IN ({1})
     132    ";
    46133    private const string CheckJobGrantedForResourceQueryString = @"
    47134      WITH rbranch AS (

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs

    r15540 r15552  
    3131    public override Project GetById(Guid id) {
    3232      return GetByIdQuery(DataContext, id);
     33    }
     34
     35    public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
     36      return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds);
    3337    }
    3438
    … …  
    6367         where project.ProjectId == projectId
    6468         select project).SingleOrDefault());
     69    private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery =
     70      CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) =>
     71      (from project in db.GetTable<Project>()
     72       join projectPermission in db.GetTable<ProjectPermission>()
     73       on project.ProjectId equals projectPermission.ProjectId
     74       where userAndGroupIds.Contains(projectPermission.GrantedUserId)
     75       select project).Distinct());
    6576    #endregion
    6677
    67     #region String queries
     78      #region String queries
    6879    private const string GetChildProjectsByIdQuery = @"
    6980      WITH ptree AS

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs

    r15530 r15552  
    8888      AND pp.GrantedUserId IN ({1})
    8989    ";
     90    private const string GetGrantedProjectsForUserQueryString = @"
     91      SELECT DISTINCT p.*
     92      FROM [ProjectPermission] pp, [Project] p
     93      WHERE pp.GrantedUserId IN ({0})
     94      AND  pp.ProjectId = p.ProjectId
     95    ";
    9096    #endregion
    9197  }

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs

    r15546 r15552  
    3232    #region Hive daos
    3333    AssignedTaskResourceDao AssignedTaskResourceDao { get; }
     34    AssignedJobResourceDao AssignedJobResourceDao { get; }
    3435    AssignedProjectResourceDao AssignedProjectResourceDao { get; }
    3536    DowntimeDao DowntimeDao { get; }

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs

    r15546 r15552  
    4141    public AssignedTaskResourceDao AssignedTaskResourceDao {
    4242      get { return assignedTaskResourceDao ?? (assignedTaskResourceDao = new AssignedTaskResourceDao(dataContext)); }
     43    }
     44
     45    private AssignedJobResourceDao assignedJobResourceDao;
     46    public AssignedJobResourceDao AssignedJobResourceDao {
     47      get { return assignedJobResourceDao ?? (assignedJobResourceDao = new AssignedJobResourceDao(dataContext)); }
    4348    }
    4449

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

    r15528 r15552  
    3636  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
    3737  )
     38CREATE TABLE [dbo].[AssignedJobResource](
     39  [ResourceId] UniqueIdentifier NOT NULL,
     40  [JobId] UniqueIdentifier NOT NULL,
     41  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
     42  )
    3843CREATE TABLE [dbo].[Plugin](
    3944  [PluginId] UniqueIdentifier NOT NULL,
    … …  
    7378  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
    7479  )
    75 CREATE TABLE [dbo].[ResourcePermission](
    76   [ResourceId] UniqueIdentifier NOT NULL,
    77   [GrantedUserId] UniqueIdentifier NOT NULL,
    78   [GrantedByUserId] UniqueIdentifier NOT NULL,
    79   CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
    80   )
    8180CREATE TABLE [dbo].[Task](
    8281  [TaskId] UniqueIdentifier NOT NULL,
    … …  
    180179ALTER TABLE [dbo].[AssignedTaskResource]
    181180  ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
     181ALTER TABLE [dbo].[AssignedJobResource]
     182  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
     183ALTER TABLE [dbo].[AssignedJobResource]
     184  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
    182185ALTER TABLE [dbo].[RequiredPlugins]
    183186  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
    … …  
    186189ALTER TABLE [dbo].[Resource]
    187190  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
    188 ALTER TABLE [dbo].[ResourcePermission]
    189   ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
    190191ALTER TABLE [dbo].[Task]
    191192  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

    r15528 r15552  
    4242ALTER TABLE [dbo].[AssignedTaskResource]  DROP  CONSTRAINT [Resource_AssignedTaskResource]
    4343ALTER TABLE [dbo].[AssignedTaskResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId])
     44REFERENCES [dbo].[Resource] ([ResourceId])
     45ON UPDATE CASCADE
     46ON DELETE CASCADE
     47GO
     48
     49ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
     50ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
     51REFERENCES [dbo].[Job] ([JobId])
     52ON UPDATE CASCADE
     53ON DELETE CASCADE
     54GO
     55ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
     56ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
    4457REFERENCES [dbo].[Resource] ([ResourceId])
    4558ON UPDATE CASCADE
    … …  
    105118REFERENCES [dbo].[Project] ([ProjectId])
    106119ON UPDATE CASCADE
    107 ON DELETE CASCADE
     120-- ON DELETE CASCADE
    108121GO
    109122

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/HiveService.cs

    r15547 r15552  
    6969
    7070    #region Task Methods
     71    [Obsolete]
    7172    public Guid AddTask(DT.Task task, DT.TaskData taskData, IEnumerable<Guid> resourceIds) {
    7273      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    … …  
    353354      var pm = PersistenceManager;
    354355      using (new PerformanceLogger("GetJobs")) {
     356        // TODO-JAN: optimization potential - avoid using too many joins in linq
    355357        var jobDao = pm.JobDao;
    356358        var jobPermissionDao = pm.JobPermissionDao;
    … …  
    364366            .Select(x => x.ToDto())
    365367            .ToList();
     368          // calculate stats only for owned & permitted jobs; TODO: query only needed ones, not all
    366369          var statistics = taskDao.GetAll()
     370              .Where(x => jobs.Select(y => y.Id).Contains(x.JobId))
    367371              .GroupBy(x => x.JobId)
    368372              .Select(x => new {
    … …  
    393397    }
    394398
     399    [Obsolete]
    395400    public Guid AddJob(DT.Job jobDto) {
    396401      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    … …  
    515520        var jobDao = pm.JobDao;
    516521        pm.UseTransaction(() => {
    517           // child task will be deleted by db-trigger
     522          // child tasks will be deleted by db-trigger
     523          // entries in AssignedJobResource will be deleted by foreign key clause CASCADE ON DELETE
    518524          jobDao.Delete(jobId);
    519525          pm.SubmitChanges();
    … …  
    728734    public void DeleteProject(Guid projectId) {
    729735      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator);
    730       AuthorizationManager.AuthorizeForProjectAdministration(projectId);
    731736      var pm = PersistenceManager;
    732737      using (new PerformanceLogger("DeleteProject")) {
    733738        var projectDao = pm.ProjectDao;
    734         pm.UseTransaction(() => {
     739        var assignedJobResourceDao = pm.AssignedJobResourceDao;
     740        pm.UseTransaction(() => {
     741          assignedJobResourceDao.DeleteByProjectId(projectId);
    735742          projectDao.Delete(projectId);
    736743          pm.SubmitChanges();
    … …  
    748755    }
    749756
     757    // query granted projects for use (i.e. to calculate on)
    750758    public IEnumerable<DT.Project> GetProjects() {
    751759      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    … …  
    757765        var currentUserId = UserManager.CurrentUserId;
    758766        return pm.UseTransaction(() => {
    759           var projectPermissions = projectPermissionDao.GetAll();
    760           return projectDao.GetAll().ToList()
    761             .Where(x => isAdministrator
    762               || x.OwnerUserId == currentUserId
    763               || UserManager.VerifyUser(currentUserId, projectPermissions
    764                   .Where(y => y.ProjectId == x.ProjectId)
    765                   .Select(z => z.GrantedUserId)
    766                   .ToList())
    767               )
    768             .Select(x => x.ToDto())
    769             .ToList();
     767          var userAndGroupIds = new List<Guid> { currentUserId };
     768          userAndGroupIds.AddRange(UserManager.GetUserGroupIdsOfUser(currentUserId));
     769          return projectDao.GetGrantedProjectsForUser(userAndGroupIds)
     770            .Select(x => x.ToDto());
     771          //var projectPermissions = projectPermissionDao.GetAll();
     772          //return projectDao.GetAll().ToList()
     773          //  .Where(x => isAdministrator
     774          //    || x.OwnerUserId == currentUserId
     775          //    || UserManager.VerifyUser(currentUserId, projectPermissions
     776          //        .Where(y => y.ProjectId == x.ProjectId)
     777          //        .Select(z => z.GrantedUserId)
     778          //        .ToList())
     779          //    )
     780          //  .Select(x => x.ToDto())
     781          //  .ToList();
    770782        });
    771783      }
    … …  
    816828        var projectPermissionDao = pm.ProjectPermissionDao;
    817829        var projectDao = pm.ProjectDao;
     830        var assignedJobResourceDao = pm.AssignedJobResourceDao;
    818831        pm.UseTransaction(() => {
    819832          if (cascading) {
    820833            var childProjectIds = projectDao.GetChildProjectIdsById(projectId);
    821834            projectPermissionDao.DeleteByProjectIdsAndGrantedUserIds(childProjectIds, grantedUserIds);
     835            assignedJobResourceDao.DeleteByProjectIdsAndUserIds(childProjectIds, grantedUserIds);
    822836          }
    823837          projectPermissionDao.DeleteByProjectIdAndGrantedUserIds(projectId, grantedUserIds);
     838          assignedJobResourceDao.DeleteByProjectIdAndUserIds(projectId, grantedUserIds);
    824839          pm.SubmitChanges();
    825840        });
    … …  
    829844    public IEnumerable<DT.ProjectPermission> GetProjectPermissions(Guid projectId) {
    830845      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
     846      AuthorizationManager.AuthorizeForProjectAdministration(projectId);
    831847      var pm = PersistenceManager;
    832848      using (new PerformanceLogger("GetProjectPermissions")) {
    … …  
    880896      using (new PerformanceLogger("UnassignProjectResources")) {
    881897        var assignedProjectResourceDao = pm.AssignedProjectResourceDao;
     898        var assignedJobResourceDao = pm.AssignedJobResourceDao;
    882899        var projectDao = pm.ProjectDao;
    883900        pm.UseTransaction(() => {
    … …  
    885902            var childProjectIds = projectDao.GetChildProjectIdsById(projectId);
    886903            assignedProjectResourceDao.DeleteByProjectIdsAndResourceIds(childProjectIds, resourceIds);
     904            assignedJobResourceDao.DeleteByProjectIdsAndResourceIds(childProjectIds, resourceIds);
    887905          }
    888906          assignedProjectResourceDao.DeleteByProjectIdAndResourceIds(projectId, resourceIds);
    889           pm.SubmitChanges();
    890         });
    891       }
    892     }
    893 
    894     public IEnumerable<AssignedProjectResource> GetAssignedResourcesForProject(Guid projectId) {
     907          assignedJobResourceDao.DeleteByProjectIdAndResourceIds(projectId, resourceIds);
     908          pm.SubmitChanges();
     909        });
     910      }
     911    }
     912
     913    public IEnumerable<DT.AssignedProjectResource> GetAssignedResourcesForProject(Guid projectId) {
    895914      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    896915      var pm = PersistenceManager;
    … …  
    903922      }
    904923    }
     924
    905925    #endregion
    906926

  • branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/Manager/AuthorizationManager.cs

    r15540 r15552  
    3535    private const string NOT_AUTHORIZED_USERRESOURCE = "Current user is not authorized to access the requested resource";
    3636    private const string NOT_AUTHORIZED_USERPROJECT = "Current user is not authorized to access the requested project";
     37    private const string NOT_AUTHORIZED_USERJOB = "Current user is not authorized to access the requested job";
    3738    private const string NOT_AUTHORIZED_PROJECTRESOURCE = "Selected project is not authorized to access the requested resource";
    3839
    … …  
    169170      if (permission == Permission.NotAllowed
    170171          || ((permission != requiredPermissionEntity) && requiredPermissionEntity == Permission.Full)) {
    171         throw new SecurityException(NOT_AUTHORIZED_USERRESOURCE);
     172        throw new SecurityException(NOT_AUTHORIZED_USERJOB);
    172173      }
    173174    }
Note: See TracChangeset for help on using the changeset viewer.