Additional Filtering in Odata Controllers for Access Control

Question

We are using WebAPI/OData project to expose a data layer for our application.

.NET Framework 4.6.1
System.Web.OData, Version=5.9.0.0

We have several OData controllers for various datasets (Surgeons, Patients etc.). If I am trying to search for patients I would issue an OData Query something like the following to get a list of patients matching my desired criteria.

 http://localhost/MyService/Patients?$filter=contains($it/Name,”Joe”)

The problem is that I may not be allowed to view all the patients. Since the RESTful API will be externally exposed I can’t rely the client to enforce the security requirements through manipulation of the url.

If I use claims to grant user access to specific patients I could feasibly protect access to an individual user but then the searching for a list of matching users wouldn’t work at all.

 http://localhost/MyService/Patients(Id)

If I try to work around this and get access through the entity owning the patients the same type of problem occurs at the next higher level. In other words if Surgeons own Patients then how do I get a list of the Surgeons that I have access to?

Is there a way to to inject some additional criteria to the query constructed by OData to enforce the access controls?

RonnBlack RonnBlack · Accepted Answer · 2016-04-14T20:46:28

This is the GET method for the Patients OData controller

public class PatientsController : BaseODataController<Patient>
{
    public override IQueryable<Patient> Get()
    {
        return base.Get();
    }
    ...
}

It is returning IQueryable<Patient>...

The only thing we need to do to add access control filtering is add on the additional predicates… For example:

public override IQueryable<Patient> Get()
{
    var qry = base.Get();
    return qry.Where(itm => itm.Name.FirstName.Contains("R"));
}

When the controller infrastructure enumerates the IQueryable<T> it evaluates the entire unit of work and generates the final query.

If I run trace against the database and give it a more complex OData query like

http://localhost/MyService/Patients?$filter=Surgeries/any(d:d/PreOpDataComplete%20eq%20true).

(In other words find Surgeries where the PreOpDataComplete flag is true) I will see the following query is executed.

    exec sp_executesql N'SELECT 
        [Project1].[C1] AS [C1], 
        [Project1].[Id] AS [Id], 
        [Project1].[Name_FirstName] AS [Name_FirstName], 
        [Project1].[Name_LastName] AS [Name_LastName], 
        [Project1].[Gender] AS [Gender], 
        [Project1].[BirthDate] AS [BirthDate], 
        [Project1].[MedicalRecordId] AS [MedicalRecordId], 
        [Project1].[Surgeon_Id] AS [Surgeon_Id]
        FROM ( SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Name_FirstName] AS [Name_FirstName], 
            [Extent1].[Name_LastName] AS [Name_LastName], 
            [Extent1].[Gender] AS [Gender], 
            [Extent1].[BirthDate] AS [BirthDate], 
            [Extent1].[MedicalRecordId] AS [MedicalRecordId], 
            [Extent1].[Surgeon_Id] AS [Surgeon_Id], 
            1 AS [C1]
            FROM [dbo].[Patient] AS [Extent1]
            WHERE [Extent1].[Name_FirstName] LIKE N''%R%''
        )  AS [Project1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Surgery] AS [Extent2]
            WHERE ([Project1].[Id] = [Extent2].[Patient_Id]) AND ([Extent2].[PreOpDataComplete] = @p__linq__0)
        )',N'@p__linq__0 bit',@p__linq__0=1

And I get the expected result

{
    "@odata.context":"http://localhost/MyService/$metadata#Patients",
    "value":[{
        "Name":{
            "FirstName":"Ronn",
            "LastName":"Black"
        },
        "Gender":"M",
        "BirthDate":"1917-02-02T00:00:00-08:00",
        "MedicalRecordId":"MRN 0001",
        "Id":"8bf6dcc4-3f00-4a40-980c-ceb13f8f5360"
    }]
}

If I assume a simple security model where Surgeons own their own patients, and the I have a list of surgeon ids I'm allowed to access in a claim. I would have a Surgeon entity that looks something like this:

public partial class Surgeon : IBaseEntity
{
    [Key]
    public Guid Id { get; set; }
    public virtual ICollection<Patient> Patients { get; set; }
    . . .
}

Now if I make the following modification I can restrict any searches to just the patients I’m allowed to see:

public override IQueryable<Patient> Get()
{
    //IQueriable from the OData Selection
    var qry = base.Get();
 
    //Enforce Access Security
    var accessList = {get list of authorized surgeon id's from claims};
    var finalQry = uow.Surgeons
        .Where(s => accessList.Contains(s.Id))      //Restrict to Surgeons I'm allowed to see
        .SelectMany(surgeon => surgeon.Patients)    //All the patients I'm allowed to see (Left)
        .Join(qry,                                 //Join to Query (Right)
            allowedPatients => allowedPatients.Id, //Key for Left
            qryPatients => qryPatients.Id,         //Key for Right
            (allowedPatients, qryPatients) =>      //Iterate through Matches
            qryPatients);                          //Return the Matches from Right
 
    return finalQry;
}

Additional Filtering in Odata Controllers for Access Control

1 Answers