Project Server Security Mode / Reading user's group assignments

[JohannesFrankeTPG]

Hi Folks,

I am looking for a way to read the security groups the current user is assigned to in Project Server or Project Online. We want to use security groups to control access to specific functions we implemented in our solution which is mostly VBA-based, so I am restricted to what WinHTTP can do. No fancy CSOM or anything that sort.

To say it simple, each critical function has a corresponding security group, and only users who are members in the security group will have access.

In 2016, Microsoft discontinued the method GetProjectServerSettingsEx method in the Project Professional object model. It will now just raise an automation error. It used to be a simple way to read the Project Server security groups that the active user was assigned to.

But more things have changed since then. As an alternative to the Project Server security model, SharePoint could also be used in its place. Probably that's the reason behind said method no longer working as it would only work with Project Server security, not with SharePoint's.

More and more projects are coming up now where a requirement is that functionality can be restricted to specific users with the help of custom user groups. However, I am completely stuck finding out the data needed.

What we would need is:

• a way to find out what security model the Project Server in question is running
  I know that there is the PowerShell Get-SPProjectPermissionMode cmdlet but I cannot integrate PowerShell the VBA solution. There is no obvious ODATA / REST method to find out the same
• once the security model is known (if need be, we can provide it redundantly in our local configuration):
  • for Project Server security model: a REST API that allows to read the group assignments for the active user, or if need be, for all users, being filtered to reveal only the current user's data
  • for SharePoint security model: I found that using the https://<server>/_api/web/CurrentUser?$expand=groups will give me a list of groups, however, they are not related to the ones used in Project Server because the systems I have are all running Project Server security model. So what I'm getting are probably SharePoint groups that I cannot use for my needs. But it would still be nice to know if this URL would be the place to go in the SharePoint security model context

I cannot find any ProjectServer related REST endpoint revealing any user data at all. There are only two I know of, ProjectServer for draft projects and some config data, and ProjectData for published projects and reporting purposes.

Here's what the ProjectServer REST endpoint returns. The URL that produced this result is https://tpgpj2016.dom.local/PWA/_api/ProjectServer

<?xml version="1.0" encoding="utf-8"?>
<entry xml:base="https://tpgpj2016.dom.local/_api/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml">
  <id>https://tpgpj2016.dom.local/_api/ProjectServer</id>
  <category term="PS.ProjectServer" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
  <link rel="edit" href="ProjectServer" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Calendars" type="application/atom+xml;type=feed" title="Calendars" href="ProjectServer/Calendars" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CustomFields" type="application/atom+xml;type=feed" title="CustomFields" href="ProjectServer/CustomFields" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/EnterpriseProjectTypes" type="application/atom+xml;type=feed" title="EnterpriseProjectTypes" href="ProjectServer/EnterpriseProjectTypes" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/EnterpriseResources" type="application/atom+xml;type=feed" title="EnterpriseResources" href="ProjectServer/EnterpriseResources" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/EntityTypes" type="application/atom+xml;type=entry" title="EntityTypes" href="ProjectServer/EntityTypes" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/EventHandlers" type="application/atom+xml;type=feed" title="EventHandlers" href="ProjectServer/EventHandlers" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Events" type="application/atom+xml;type=feed" title="Events" href="ProjectServer/Events" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/LookupTables" type="application/atom+xml;type=feed" title="LookupTables" href="ProjectServer/LookupTables" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Phases" type="application/atom+xml;type=feed" title="Phases" href="ProjectServer/Phases" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ProjectDetailPages" type="application/atom+xml;type=feed" title="ProjectDetailPages" href="ProjectServer/ProjectDetailPages" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Projects" type="application/atom+xml;type=feed" title="Projects" href="ProjectServer/Projects" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Stages" type="application/atom+xml;type=feed" title="Stages" href="ProjectServer/Stages" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/TimeSheetPeriods" type="application/atom+xml;type=feed" title="TimeSheetPeriods" href="ProjectServer/TimeSheetPeriods" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/WorkflowActivities" type="application/atom+xml;type=entry" title="WorkflowActivities" href="ProjectServer/WorkflowActivities" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/WorkflowDesigner" type="application/atom+xml;type=entry" title="WorkflowDesigner" href="ProjectServer/WorkflowDesigner" />
  <title />
  <updated>2022-09-02T14:27:30Z</updated>
  <author>
    <name />
  </author>
  <content type="application/xml">
    <m:properties>
      <d:IsDelegate m:type="Edm.Boolean">false</d:IsDelegate>
      <d:IsReadOnly m:type="Edm.Boolean">false</d:IsReadOnly>
    </m:properties>
  </content>
</entry>

Here is the same for ProjectData in place of ProjectServer (where ProjectData is for reporting purposes and user data would not be expected anyway):

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<service xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="https://tpgpj2016.dom.local/_api/ProjectData/">
  <workspace>
    <atom:title>Default</atom:title>
    <collection href="Projects">
      <atom:title>Projects</atom:title>
    </collection>
    <collection href="ProjectBaselines">
      <atom:title>ProjectBaselines</atom:title>
    </collection>
    <collection href="AssignmentBaselines">
      <atom:title>AssignmentBaselines</atom:title>
    </collection>
    <collection href="AssignmentBaselineTimephasedDataSet">
      <atom:title>AssignmentBaselineTimephasedDataSet</atom:title>
    </collection>
    <collection href="AssignmentTimephasedDataSet">
      <atom:title>AssignmentTimephasedDataSet</atom:title>
    </collection>
    <collection href="Deliverables">
      <atom:title>Deliverables</atom:title>
    </collection>
    <collection href="Issues">
      <atom:title>Issues</atom:title>
    </collection>
    <collection href="IssueTaskAssociations">
      <atom:title>IssueTaskAssociations</atom:title>
    </collection>
    <collection href="RiskTaskAssociations">
      <atom:title>RiskTaskAssociations</atom:title>
    </collection>
    <collection href="ProjectWorkflowStageDataSet">
      <atom:title>ProjectWorkflowStageDataSet</atom:title>
    </collection>
    <collection href="ResourceTimephasedDataSet">
      <atom:title>ResourceTimephasedDataSet</atom:title>
    </collection>
    <collection href="Risks">
      <atom:title>Risks</atom:title>
    </collection>
    <collection href="TaskBaselines">
      <atom:title>TaskBaselines</atom:title>
    </collection>
    <collection href="TaskBaselineTimephasedDataSet">
      <atom:title>TaskBaselineTimephasedDataSet</atom:title>
    </collection>
    <collection href="TaskTimephasedDataSet">
      <atom:title>TaskTimephasedDataSet</atom:title>
    </collection>
    <collection href="TimeSet">
      <atom:title>TimeSet</atom:title>
    </collection>
    <collection href="TimesheetLines">
      <atom:title>TimesheetLines</atom:title>
    </collection>
    <collection href="TimesheetLineActualDataSet">
      <atom:title>TimesheetLineActualDataSet</atom:title>
    </collection>
    <collection href="Assignments">
      <atom:title>Assignments</atom:title>
    </collection>
    <collection href="Resources">
      <atom:title>Resources</atom:title>
    </collection>
    <collection href="Timesheets">
      <atom:title>Timesheets</atom:title>
    </collection>
    <collection href="TimesheetClasses">
      <atom:title>TimesheetClasses</atom:title>
    </collection>
    <collection href="TimesheetPeriods">
      <atom:title>TimesheetPeriods</atom:title>
    </collection>
    <collection href="Tasks">
      <atom:title>Tasks</atom:title>
    </collection>
    <collection href="BusinessDrivers">
      <atom:title>BusinessDrivers</atom:title>
    </collection>
    <collection href="BusinessDriverDepartments">
      <atom:title>BusinessDriverDepartments</atom:title>
    </collection>
    <collection href="Prioritizations">
      <atom:title>Prioritizations</atom:title>
    </collection>
    <collection href="PrioritizationDrivers">
      <atom:title>PrioritizationDrivers</atom:title>
    </collection>
    <collection href="PrioritizationDriverRelations">
      <atom:title>PrioritizationDriverRelations</atom:title>
    </collection>
    <collection href="PortfolioAnalyses">
      <atom:title>PortfolioAnalyses</atom:title>
    </collection>
    <collection href="PortfolioAnalysisProjects">
      <atom:title>PortfolioAnalysisProjects</atom:title>
    </collection>
    <collection href="CostConstraintScenarios">
      <atom:title>CostConstraintScenarios</atom:title>
    </collection>
    <collection href="ResourceConstraintScenarios">
      <atom:title>ResourceConstraintScenarios</atom:title>
    </collection>
    <collection href="CostScenarioProjects">
      <atom:title>CostScenarioProjects</atom:title>
    </collection>
    <collection href="ResourceScenarioProjects">
      <atom:title>ResourceScenarioProjects</atom:title>
    </collection>
    <collection href="Engagements">
      <atom:title>Engagements</atom:title>
    </collection>
    <collection href="EngagementsTimephasedDataSet">
      <atom:title>EngagementsTimephasedDataSet</atom:title>
    </collection>
    <collection href="EngagementsComments">
      <atom:title>EngagementsComments</atom:title>
    </collection>
  </workspace>
</service>

So it appears that ProjectServer does not have any REST endpoint for administrative data such as users and security groups (?). I don't know if there is another endpoint for admin purposes but if there is, it is well-hidden and not largely documented...

If it's not ProjectServer any longer but SharePoint, I would not expect it to know much about the Project Server security model. That's still part of Project Server in my opinion, or something in between.

This is complicated a good bit more by Project Online. We should be able to offer a way to bind functionality to user groups on that platform, too.

In case the data I need are only accessible via PSI or CSOM, I would have to provide a local ASP.NET web service that is accessible to all users to give them their list of security groups. It will make the system architecture considerably more complex but if that's the way to go, I would consider doing that.

For the moment, I'm pretty lost. Would be very grateful for any advice.

Thanks everyone!

Cheers,

Johannes

[#2]

Hello, @JohannesFrankeTPG.
Have you had any success with this item? I'm in the same position and looking for alternatives.

[#3]

@ellizsillva22Sorry, no clean solution here. We are using Project Server Security Model and read from the following SQL statement:

SELECT m.SiteID, g.WSEC_GRP_NAME, m.WRES_GUID, r.RES_NAME
  FROM pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS AS m
       LEFT JOIN pjpub.MSP_RESOURCES AS r
	           ON (    (r.SiteId = m.SiteID)
			       AND (pjpub.MSP_WEB_FN_SEC_GetUserSecurityGuid(m.SiteId, r.RES_UID) = m.WRES_GUID))
	   INNER JOIN pjpub.MSP_WEB_SECURITY_GROUPS AS g
	           ON (    (g.SiteId = m.SiteId)
			       AND (g.WSEC_GRP_GUID = m.WSEC_GRP_GUID))

This would retrieve all group memberships and needs to be reduced to select only the resource in question, e.g. by adding a condition to the JOIN on MSP_RESOURCES to filter resources by name, e-mail address or similar.

To keep this at least a bit safe, we are not running SQL connections directly from the clients to SQL Server but employ an ASP.NET web service providing a REST service to run the query with the user's name as a parameter, returning the list of security groups as a JSON document, so the actual SQL is hidden from users, and not much harm can be done by executing the REST endpoint.

Sorry but no better solution was possible.

Cheers,

Joe