What is the best way to validate access to CRUD operations based on permissions and attributes?

[B]

I have an existing ASP.Net Web Application. I will be migrating from SQLDataSources and Code SQL(bad, i know) to a service model and want to use the DataAccess ORM and an ASP.Net Web API. My database is shared between multiple companies. These companies should not be able to modify each others data or view each others data. I have a column on many of the tables where by there is a company ID that signifies which user is allowed to modify or view a record. Also, i also have a permissions table that i need to validate against to make sure the particular user has the permissions needed to be able to perform that operation on a particular set of data for a company(so it's not just company level validation). A short example of the data structure is as follows:

Table -->Companies
                   Company ID
                   Company Name

Table-->Projects
                  Project ID
                  Company ID
                  Project Name

Table-->Users
                 UserID
                 Password
                 Company ID
Table--> Permissions
                 User ID
                 Allowed Permission


I have been reading through the documentation and watching the videos and would like to post the question. In the example above, I want to validate that a user is attempting to CRUD on the 'Projects' table is only doing so if their company ID matches the Company ID of the projects they wish to create, read, update, or delete and they have the available permissions. Where is the best place to implement the validation of a user who is attempting to CRUD records? By best, i mean the safest(#1) and most application and DB efficient. I currently have forms based authentication with the user data stored in the same database(it actually is much more complicated that what is above). I have no issues controlling the access because i can validated on the application side each time a CRUD is attempted using the sessions variable acquired whenever a session is renewed. Going forward i do not want to have to add this logic in at each operation and i would like to create the logic once and apply to most of the operations. What would be the best way to go about this using Telerik DataAccess?


I saw the documentation about adding an interceptor which seems like it is a possible solution. Are there other solutions that can be recommended for this use case? http://docs.telerik.com/data-access/developers-guide/using-web-services/data-services/developer-guide-wcfservices-data-service-validation 

Thanks!

 

[Doroteya]

Hello Brannon,

You are raising a very interesting, complex and yet generic subject.

In general, Telerik Data Access is capable of supporting a multitenant data architecture, but this support does not come out-of-the-box. When it comes to the model itself, our code generation allows you to modify the code generation templates a little bit, so that the end-points of the context produce queries, which apply the necessary WHERE clause in the SQL statements issued against the database. When it comes to the Web API service layer, if you choose to generate it with Telerik Data Access, you will need to extend the repositories in order to provide both the authentication and the correct value for CompanyID.

A limitation you may experience is related to the handling of the collection navigational properties of the persistent classes. Suppose the following scenario: Each project has a list of employees that work on it. If the employees of two companies work on a given project, when a user from one of the companies logs in and looks at the project, he/she will see a list of employees, which includes non only his/her colleagues, but the employees from the other company as well. This limitation can be handled in several ways but each one is scenario specific.

As for an example, currently we do not offer an end-to-end sample, that demonstrates the architecture of such a solution, but you can find attached to this post a demo of the change in the code generation templates that basically will allow you to query the database correctly.

You can have a look in the generated EntitiesModel1.cs in the attached project:

public IQueryable<DomainClass1> DomainClass1
{
    get
    {
        return this.GetAll<DomainClass1>().Where(x=>x.TenantId == this.TenantId);
    }
}

To reproduce it on you side, the first you need to do is execute the steps from the Copy Templates Locally section of this article.

Then you can apply the change in file CodeGeneration\Includes_ver.2\ContextGenerator.ttinclude file, line 135:

public <#= property.ToTypeString() #> <#= property.Name #>
{
    get
    {
        return this.GetAll<<#= property.Type #>>().Where(x=>x.TenantId == this.TenantId);
    }
}

Additionally, the context class is extended in a partial class in order to expose the TenantId property (the EntitiesModel1.partial.cs file). 

Setup this way, the end-points of the context will filter the database content based on the value of TenantId.


I hope this helps. If you need further Data Access related information and assistance, do not hesitate to get back to us.
 


Regards,
Doroteya
Telerik