The PDS and Project Server Security Architecture

Summary

This article provides developers with the background and details of the Microsoft Office Project Server 2003 security architecture, and explains the role of the Project Data Service (PDS) in accessing project data. The developer of a client solution that accesses data from Project Server database views should use this information as the basis for maintaining data security.

The Project Data Service

The PDS component was introduced in Microsoft Project 2002. Clients such as Project Professional, Project Web Access, or a client developed by a solution provider all access the PDS to obtain portfolio data. Requests to the PDS are made through an Extensible Markup Language (XML) interface and responses are returned to the client in XML.

A client application queries the PDS for connection information to the Project Server database. Using this information, the application connects to the Project Server database and queries database views to retrieve and update information for the projects and resources that the application is authorized to view.

In Figure 1, you can see the relationship between client applications and Project Server. The PDS is a middle-tier component hosted within Microsoft Internet Information Services (IIS) that acts as the gateway for accessing Project Server. The PDS receives XML requests, maintains a connection on behalf of the client to the Project Server database, queries the database, and returns XML responses to the client.

Figure 1. The PDS acts as a gateway between client applications and the Project Server database

The PDS ensures that Project Server data stored in the database remains consistent. The PDS opens a connection to the Project Server database on behalf of the client. The PDS authenticates the client and provides access to the project data. The client can only see data relevant to the project.

You can also query the Project Server database directly through views, which gives you access to rich project and resource information. The PDS provides methods to obtain the database connection information needed to connect to the Project Server database. You then can use views provided by the Project Server database to run queries, for example, to gather data for a custom report. These views ensure that clients only see data that they have permission to see. Clients never access the actual database tables themselves.

Accessing Microsoft Project Data

Client applications that need to access Project Server data need to operate within the Project Server security constraints.

To access Microsoft Project data:

  1. Log on to Project Server.

  2. Call the PDS to request the database connection information.

  3. Make a connection to the Project Server database using that information and gather the SQL Process ID (SPID) for that connection.

  4. Call the PDS to request access to the project or resource that the client needs to access and pass along the SPID from the previous step.

  5. Make queries against the appropriate set of Microsoft SQL Server™ views to gather and/or update the required information.

  6. Call the PDS when the access is no longer required to maintain the security of the data.

  7. Disconnect from the Project Server database.

Figure 2. How a client accesses the Project Server database views

Requesting Database Connection Information

A client can query the PDS to obtain the information needed to create a connection to the Project Server database.

Once logged on to Project Server, the client calls the GetLoginInformation method by sending the following XML using the Simple Object Access Protocol (SOAP) protocol, which is passed to the PDS object through the SoapXMLRequest interface:

<Request>
    <GetLoginInformation/>
</Request>

The response from GetLoginInformation is similar to the following XML:

<Reply>
    <HRESULT>0</HRESULT>
    <STATUS>0</STATUS>
    <UserName>Administrator</UserName>
    <GetLoginInformation>
        <DBType>0</DBType>
        <DVR>{SQL Server}</DVR>
        <DB>ProjectServer</DB>
        <SVR>MYPROJECTS</SVR>
        <ResGlobalID>1</ResGlobalID>
        <ResGlobalName>resglobal</ResGlobalName>
        <UserName>MSProjectUser</UserName>
        <Password>password</Password>
    </GetLoginInformation>
</Reply>

The elements in the GetLoginInformation node contain the information needed to make a database connection using, for example, Microsoft ActiveX Data Objects (ADO). Once you've connected to the Project Server database, you can then query the views to obtain more detailed information about your projects.

Notice that the password is sent in clear text. Because of this, it is strongly recommended that your Project Server administrator require Secure Sockets Layer (SSL) when connecting to Project Server. Although your connection to the server will be slightly slower, your database connection information will be encrypted. This is especially important when your Project Server can be accessed over the Internet.

PDS Management of View Access

The PDS manages access to the Project Server database views by tracking connections based on the SPID of the client connection. After you've connected to the Project Server database, you can gather your connection's SPID by executing the following query:

SELECT @@SPID

The Project Server database has two tables for managing access to views: the MSP_PROJ_SECURITY table for project views and the MSP_RES_SECURITY table for global resource views. For each table, access is tracked separately for read and read/write views for a given SPID.

The PDS provides methods for requesting access to views: ProjectsAccess for project views and ResourcesAccess for resource views. (See the following section "Client Access to Views" for details on calling these methods.) You call the access method, specifying the SPID, the timestamp of the SPID creation, and the access mode (read or read/write).

For example, if you want to access project views for a report, you must first call the ProjectsAccess method of the PDS with the appropriate parameters. If the parameters passed in are ProjectID = 1, SPID = 51, an SPIDTimestamp value of 20011017105500, and mode = 0 (read-only), the following row is created in the MSP_PROJ_SECURITY table:

PROJ_ID SEC_SPID SEC_SPIDDATESTAMP        SEC_READCOUNT SEC_WRITECOUNT
------- -------- -----------------------  ------------- --------------
1       51       2001-10-17 10:55:00.000  1             0

The read-only project views check the value of SEC_READCOUNT for the row corresponding to the connection SPID that is attempting to access the views. In this case, the value of SEC_READCOUNT is 1, which allows the client to retrieve data from the view. However, if the client attempts to access a read/write view, the view contains no data because the value of SEC_WRITECOUNT is 0. The client first has to call ProjectsAccess again, this time specifying a mode of 1 (read/write).

The previous example illustrates the PDS role in views security. A client cannot retrieve data from the Project Server database views without first logging on to Project Server and requesting access via the PDS to a view. This additional security check ensures that a client application sees only portfolio data accessible to the specified Project Server user logon ID.

Client Access to Views

Once a client has a database connection, the client then logs on to Project Server and requests permission to access project views through the ProjectsAccess method of the PDS. In this example, the database connection SPID is 58, the SPID timestamp is 2001-10-17 10:55:00 (note that the value corresponds to the date numerals without formatting), and the project ID is 3. This call to ProjectsAccess uses the following XML request:

<Request>
    <ProjectsAccess>
        <Mode>0</Mode>
        <SPID>58</SPID>
        <SPIDTimestamp>20011017105500</SPIDTimestamp>
        <Project>
            <ProjectID>3</ProjectID>
        </Project>
    </ProjectsAccess>
</Request>

Note The value of the SPID creation timestamp is specified by the client and does not have to be the actual creation time of the connection SPID. Any unique timestamp will do (for example, the current time).

The following is an example of a successful reply from ProjectsAccess:

<Reply>
    <HRESULT>0</HRESULT>
    <STATUS>0</STATUS>
    <UserName>Administrator</UserName>
    <ProjectsAccess>
        <Mode>0</Mode>
        <ResGlobalID>1</ResGlobalID>
        <ResGlobalName>resglobal</ResGlobalName>
    </ProjectsAccess>
</Reply>

To access enterprise resource views, a client calls ResourcesAccess, which is similar to ProjectsAccess except that it also allows a particular resource ID to be specified.

In addition, the SQL Server user account specified in the database connection information must have permission to access the views. Typically, this user is MSProjectUser (the name may be different in different installations), which is the default SQL Server user account created by the Project Server setup program. The MSProjectUser user belongs to the MSProjectRole role, which is given permission to use the database views.

If you examine the Project Server database tables in SQL Server Enterprise Manager, you will notice that users in MSProjectRole do not have access to any database tables. You should never change these setting by granting users direct access to the tables. Doing so circumvents the PDS security and opens the possibility for writing inconsistent data to the database.

Client-side applications should only perform read operations on the Project Server database. If you need to create or update data, consider using a server-side PDS extension. If you absolutely must update Project Server database tables directly, refer to the "Microsoft Office Project 2003 Data Reference" article in the PJDB.HTM file of your Project Server installation. In particular, the section "Ensuring Data Integrity" contains guidelines to help prevent the database from becoming corrupted when the tables are being updated.

Note Clients that write directly to the Project Server database will likely be incompatible with future versions of Project Server.

Project, Resource, and Task Views

The Project Server database defines many views for retrieving information about projects, resources, tasks, assignments, and other data. For example, the MSP_TASKS_PROJ_READVIEW is defined as follows:

CREATE VIEW dbo.MSP_TASKS_PROJ_READVIEW
AS
    SELECT *
    FROM MSP_TASKS
    WHERE PROJ_ID IN
    (SELECT PROJ_ID FROM MSP_PROJ_SECURITY
        WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
    WITH CHECK OPTION

There is a corresponding read/write view for project tasks named MSP_TASKS_PROJ_WRITEVIEW.Similar views exist for resources (MSP_TASKS_RES_READVIEW and MSP_TASKS_RES_WRITEVIEW). If you look at the column definitions, the resource task views appear to be identical to the project task views; however, the resource task views apply to enterprise resources and are not specific to a given project. This distinction can be seen in the definition of MSP_TASKS_RES_READVIEW:

CREATE VIEW dbo.MSP_TASKS_RES_READVIEW
AS
    SELECT *
    FROM MSP_TASKS
    WHERE PROJ_ID IN
         (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_TYPE = 3)
    WITH CHECK OPTION

The PROJ_TYPE value of 3 corresponds to enterprise resources (resource global).

The different views allow a client to create sophisticated queries. For example, the following query provides task information for a given project and displays the resources assigned to those tasks:

select p.TASK_ID 'Task Number',
        p.TASK_NAME 'Task Name',
        CONVERT(VARCHAR(4), p.TASK_DUR_VAR/(60*60)) + 'd' 'Duration',
        r.RES_NAME 'Resource Name'
    from MSP_TASKS_PROJ_READVIEW p
    inner join MSP_ASSIGNMENTS_PROJ_READVIEW a
        on (p.TASK_UID = a.TASK_UID)
    inner join MSP_RESOURCES_PROJ_READVIEW r
        on (a.RES_UID = r.RES_UID)
    order by TASK_OUTLINE_NUM

The preceding query produces output similar to the following:

Task Number Task Name     Duration Resource Name
----------- ------------- -------- -------------
1           Write outline 1d       Writer
2           Write draft   2d       Writer
3           Create art    1d       Artist

Remember, you must first request read access to the project views by calling ProjectsAccess for the specific connection SPID. Otherwise, the query does not return any data because of the following check found in project views:

WHERE PROJ_ID IN
(SELECT PROJ_ID FROM MSP_PROJ_SECURITY
    WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)

Disconnecting from Views

When you disconnect from the Project Server database, you must also notify the PDS that you no longer need to access the views. For project views, you do this by calling ProjectsAccessCompleted, specifying the access mode, connection SPID, and project ID.

<Request>
    <ProjectsAccessCompleted>
        <Mode>0</Mode>
        <SPID>58</SPID>
        <Project>
            <ProjectID>3</ProjectID>
        </Project>
    </ProjectsAccessCompleted>
</Request>

ProjectsAccessCompleted removes the corresponding row in the MSP_PROJ_SECURITY table. The PDS then returns a reply similar to the following:

<Reply>
    <HRESULT>0</HRESULT>
    <STATUS>0</STATUS>
    <UserName>Administrator</UserName>
</Reply>

After ProjectsAccessCompleted is successfully called, you can no longer access view data for that particular database connection.