Pre-filtering data

[Paul Wood]

How is it possible to pre-filter the data exposed through OpenAccess?

We have a SaaS application where users should only be able to see certain subsets of data within the various application database tables (based on their role, company etc. - stored in a .NET Profile) and we'd like to encapsulate that filtering logic in the ORM so then the application or anything else needing access to the data would go through that and see just the data they are authorised to.

Currently we are applying these data filters through logic in every data access SQL Stored Procedures - not very maintainable), but going forward would like to move away from this. Ideally we want the application to use Telerik Reporting and just give access to the OpenAccess objects through that so user's can do reporting on their own data.

[Boris Georgiev]

Hello Paul,

I agree that using stored procedures is not optimal. All the security can be implemented in your data access layer based on Telerik OpenAccess ORM. You can extend the base functionality OpenAccess is offering, in order to meet your security targets. There are different ways to achieve that:

A) If you prefer working with Visual Designer, the extensions have to be defined in partial classes, so that they are not replaced by the code generation.
B) If you prefer Fluent API, you don't have to worry about that.

In both cases, there are a couple of things, that will probably be useful for you when creating security code:
1) The OpenAccessContext events, which you can use to track the changes in your context.
2) With customizing the code generation template, you are able to track changes on the object/property level with Visual Designer.
3) If you use Fluent API, you can only add tracking code manually.

If you need any further assistance, do not hesitate to post your questions.

All the best,
Boris Georgiev
the Telerik team

[Paul Wood]

Thanks Boris,

do you have any samples of doing something similar to what I am trying to do? Also is it possible to plug OpenAccess into an existing single tier Website application? When I tried setting it up, it seemed to want to create a new Project which isn't the way I've got this all setup right now.

Paul

[Boris Georgiev]

Hello Paul, 

Unfortunately we do not have a sample to demonstrate different access to database depending on user's level with Telerik OpenAccess ORM.
Can you be more specific how would you restrict the access to data?

• Are you restricting access per user group for each table?
• Are you restricting access per user per table row?
• Do you know in advance the number or security groups that you have to support?
• Do you need a generic approach that can accommodate changes in both data model and security groups?
• Does your original solution query and join to the original data query additional security data from the database in order to grant/deny access to data resources?

Answering these questions and providing any additional details that you consider important will help us greatly to understand the whole picture and assist you in the best possible way. 

It is not possible to plug OpenAccess model and entities into an existing Website Application. 
To run Web site projects, you deploy source files and rely on ASP.NET dynamic compilation to compile pages and classes in the application. That is why you need to have class library which contains your data model. You can easily add an additional Telerik OpenAccess Class library project to your solution. Then map your database to an Entities model and add a reference from web site application to the newly created project. Doing so should not break the concept of Single Tier application and your web site should work fine. Do you have any specific restrictions that keep you away from implement such scenario?

I am looking forward to hearing from you.

All the best,
Boris Georgiev
the Telerik team

[Paul Wood]

Thanks Boris,

In answer to your questions:

• Are you restricting access per user group for each table?

Yes, users are part of a wide range of groups and scopes that would filter the data differently on a table by table basis

• Are you restricting access per user per table row?

Access to a table row will be dependant on whether the data inside the row is viewable by that user's group and scope

• Do you know in advance the number or security groups that you have to support?

No, there are four main security groups (Admin, Manager, Assistant, Writer), but there is also the scope (Region, City, Department) which for each organisation could have hundreds of records in them

• Do you need a generic approach that can accommodate changes in both data model and security groups?

Yes, essentially all we want to do is something like override the GetAll method for each table class with GetAll(WHERE.......) so that if a user of e.g. Telerik Reporting binds to the DataSource they only get returned the table rows their user should be able to see - the filtering is invisible to them.

• Does your original solution query and join to the original data query additional security data from the database in order to grant/deny access to data resources?

Yes

Paul

[Boris Georgiev]

Hi Paul,

There are two steps that you should take:
1) You need to prevent the access to the original IQueryable properties which provide you all entities(optional).
2) You should implement new properties which filter the Collections based on your security rules.

Here I will provide you with instructions how to do that:

1) How to prevent the access to the original IQueryable properties:

I would recommend you to generate your own EntitiesModel where you will not have public IQueryable properties with access to all entities. If you are using Telerik OpenAccess Domain Model, open the RLINQ file and from Model Settings Dialog, choose Code Generation tab and uncheck the Generate Context check box.

After this step you should create a new .cs file where you will implement your new EntitiesModel class.
I recommend you to copy the basic code generated from Code Generator to avoid writing it:

public partial class EntitiesModel : OpenAccessContext, IEntitiesModelUnitOfWork
{
    private static string connectionStringName = @"write your connection string here";

    private static BackendConfiguration backend = GetBackendConfiguration();


    private static MetadataSource metadataSource = XmlMetadataSource.FromAssemblyResource("EntitiesModel.rlinq");

    public EntitiesModel()
        :base(connectionStringName, backend, metadataSource)
    { }

    public EntitiesModel(string connection)
        :base(connection, backend, metadataSource)
    { }

    public EntitiesModel(BackendConfiguration backendConfiguration)
        :base(connectionStringName, backendConfiguration, metadataSource)
    { }

    public EntitiesModel(string connection, MetadataSource metadataSource)
        :base(connection, backend, metadataSource)
    { }

    public EntitiesModel(string connection, BackendConfiguration backendConfiguration, MetadataSource metadataSource)
        :base(connection, backendConfiguration, metadataSource)
    { }

    public static BackendConfiguration GetBackendConfiguration()
    {
        BackendConfiguration backend = new BackendConfiguration();
        backend.Backend = "MsSql"; // target proper backend
        backend.ProviderName = "System.Data.SqlClient";
        return backend;
    }
}

Note that for the Fluent Model, the approach will be similar.

2) How to create the new filtered IQueryable properties:

After you do this step you can extend your EntitiesModel class to have new IQueryable properties that filter the entities. Lets say you have a class for UserInfo based on which you are filtering:

public sealed class UserInfo
{
    public UserInfo()
    {
    }

    public string Group { get; set; }
    public string Region { get; set; }
}

Then extend EntitiesModel with new constructor which takes a UserInfo object as a parameter:

public partial class EntitiesModel
{
    public EntitiesModel(UserInfo currentUser)
        : base(connectionStringName, backend, metadataSource)
    {
        this.CurrentUser = currentUser;
    }

    public UserInfo CurrentUser { get; protected set; }

  ...
}

After that you can write your new IQueryable properties which filter entities. Here is a very basic code sample how to do that:

public IQueryable<Car> CarsSecured
{
    get
    {
        var query = from car in this.Cars
                    join securityDescriptor in this.SecurityDescriptors on car.CarID equals securityDescriptor.SecurableId
                    where securityDescriptor.Group = CurrentUser.Group && securityDescriptor.Region == CurrentUser.Region
                    select car;

        return query;
    }
}

I assume SecurityDescriptors is a table from your database which provide you with information about the different access of user groups. 

If you have many tables and you need a lot of properties and filters I would recommend you to create a T4 template that generates IQueryable properties for you. 
 
In conclusion, after you do the two steps above, you will prevent the access to all entities after step 1) and you will create a collections filterable by security access you need after step 2).

I hope that helps.

All the best,
Boris Georgiev
the Telerik team

[Adam]

Hi Boris,

Thanks for the information...  (I work with Paul)....

I was able to get this to work with guidance from your post...   :-)

I was pretty close before, but couldn't figure out how to stop the default "all data" table classes from being visible too..  I was extending the partial class fine, but I had "customer_filtered" and the original "customer" table visible...

We now have a default filter on the table...  Just need to implement our security properly now..
I did have issues linking (pardon the pun) an OpenAccessDataSource to the context project..  it kept saying that the project was empty and to recompile..  Seems I have to add a OpenAccessLinqDataSource to the aspx page and link that to the project and then my radgrid to that.....


Thanks for your help..  


Adam

[Boris Georgiev]

Hi Adam,

I am glad that I have helped you with your tasks.

OpenAccessDataSource is from the Telerik OpenAccess ORM Classic (old API) and it is deprecated now. The new one which you should use is OpenAccessLinqDataSource. You can take a look at the dedicated video on Telerik TV.

If you have any additional questions, do not hesitate to get back to us.

Kind regards,
Boris Georgiev
the Telerik team