Row level security

I have a requirement in my dynamic data web application to restrict table rows depending on the user logged in.

I have a Clients table with a ClientType field and a Projects table related to it (Projects table contains a ClientID). When users log in they should only see the Projects that relate to their ClientType (each user also has a ClientType).

Searching around I can see that lots of people have asked about a solution for row level security, but I can't see an obvious answer.

I'm not necessarily looking for a generalised solution. A specific "where" clause added in a Linq query somewhere or something similar would be ok. I'm not sure on the best place to add the query.

Re: Row level security

Hi BigA, there are two options at the moment Domain Service DD Project or use QueryExtender, the last is limited as yu can't pre filter dropdown lists etc. however Domain Service does not support Many to Many relationships.

Re: Row level security

Hi, BigA, Hi Sjnaughton.

It seems to me that this issue had been discussed at the forum http://forums.asp.net/t/1788200.aspx/1?Data+Filtering. And http://forums.asp.net/post/4946450.aspx is marked as answer. Sjnaughton, you can check it on my dd_site, if you will be willing and free time.
Especially for this issue, I created a data model DD_Forum.
Choose the model from the list of models by clicking "Dynamic Data Site" button. Open the "Projects" table. You'll see all the rows of the table.
Then Login as admin. In the table "Accounts" add "Forum_prj2" role to your line. Now in the "Projects" table, you will see only the "Project2" line.

Regards.

Re: Row level security

Thanks for your help Steve and valZ.

I missed your solution ValZ when searching (I thought is was related to filtering in the app). I'll give that a try as it looks to what I'm after.

Re: Row level security

I also looked at Domain Service DD from http://channel9.msdn.com/Events/MIX/MIX09

This looks like a powerful and tidy way of filtering so will also look at using this. SJNaughton has commented this doesn't support many to many's but that should be ok for me at the moment.

Re: Row level security

biga

When users log in they should only see the Projects that relate to their ClientType (each user also has a ClientType).

Does this mean that the Projects table should also have a ClietType field?

Re: Row level security

biga

I have a requirement in my dynamic data web application to restrict table rows depending on the user logged in.

I have a Clients table with a ClientType field and a Projects table related to it (Projects table contains a ClientID). When users log in they should only see the Projects that relate to their ClientType (each user also has a ClientType).

Searching around I can see that lots of people have asked about a solution for row level security, but I can't see an obvious answer.

I'm not necessarily looking for a generalised solution. A specific "where" clause added in a Linq query somewhere or something similar would be ok. I'm not sure on the best place to add the query.

First, you might want to verify your requirements. As described, if you and I had the same client type we could see each other's projects. The words, "are you sure" come to mind when I see stuff like this.

As far as the best place to add the query, it's probably a several way tie for first. I prefer stored procedures, but other techniques are equally valid.

Re: Row level security

Hi ValZ,

Because the Projects table is related to Clients this shouldn't be necessary (at least I'm hoping so :-)

Because each Project has a Client I should be able to filter by joining to the Clients table.

Re: Row level security

Hi Dan,

Yes the requirements are correct. People with the same client type need to be able to see other people's projects.

The use of stored procedures would be good, but being fairly new to this stuff I'm not sure about:

1. How to pass the logged in user's details to the stored procedure, so it can filter the results by the users client type.

2. How Dynamic Data can make use of stored procedures. My data source is currently build from the tables in the database, so the user can view, edit and delete rows.

Thanks

Re: Row level security

Hi, Biga.

Sorry for my importunity. That is, each Client must be able to see projects of all the Clients who belong to his ClietType. I understand it correctly?

Re: Row level security

Hi BigA, Understand your requirements :) and DD only offers two options as already stated ValZ's option is a good way to go, I will be doing somthing like that shortly filtering rows is relativly easy, the big issue you will always face if users typing in a URL they know will get them access to a Edit/Details of some data they should not have access to this is easily fixed using Domain Service project as all Select statments can be prefiltered. In my opinion this would be the most secure method it all depends on how much security you will need.

Re: Row level security

Hi, BigA, Hi, Sjnaughton.

FilterByRole attribute is one of my little accomplishments. I apply it in real projects about two years. I am sure that it is able to solve this problem.

In this case, that's what I suggest. There are tables Clients, Projects and ClientTypes. Projects table must have a foreign key columns clientId and clientTypeId. To the tables Clients and ClientTypes you must apply FilterByRole attribute. For example [FilterByRole("clnt", "clientId")] and [FilterByRole("clntType", "clientTypeId")]. Add this roles to a specific client.

A small note. Since the FilterByRole attribute deletes the ALL item, you must supply an additional parameter - withAll.

Thus, the Projects table will have two filters: Client (with All), and ClientType (without All). As a result, if the Client filter will be selected All, you can see all Projects of your clientType. Otherwise - only your own projects.

Regards.

Re: Row level security

valZ
A small note. Since the FilterByRole attribute deletes the ALL item, you must supply an additional parameter - withAll.

Hi ValZ this should be doable (adding an ALL that only shows your own Projects) as I am working on somthing like this for my Cascading Hierachcal Filter :) I will try and remember to post here when its done.

Re: Row level security

Hi, Sjnaughton.

In my view, the Client filter should have two items - All and user`s own ClientId. ClientType filter should have only one item - user`s own clientTypeId. Thus, if the Client filter will be selected All, the user will see all their own and others projects of the same clientType. Is not it?

Oh, and one more thing. Although I did not quite understand the place of the cascade filter in this example, I want to express my gratitude and admiration of thy works and in particular of Cascading Hierachca :).

Re: Row level security

HI ValZ, the thing I am working on with Cascade Hierachical Filter is take this hierachy

Manufacturer->Model->Style

and lets say you have three Manufacturers

VW
Audi
Ford

at the moment if you select a Manufacturer or Model then the filter will not be applied byt with me new version that I am working on if you select say Ford you will get all Ford cars listed in the list page. So the query will essentialy get a list of all Ford car PKs and then do a mult FK filter on them :)

Re: Row level security

Hi Steve, ValZ,

Thanks for your advice. I am going to try Domain Service because if users can edit the URL and get to data they shouldn't see that will be a problem for me.

Re: Row level security

biga

if users can edit the URL and get to data they shouldn't see that will be a problem for me.

I decide this issue in such a way

protected void Page_Init(object sender, EventArgs e)
{
        if (Request.UrlReferrer == null)
	           Response.Redirect("~/Default.aspx");
}

Re: Row level security

Hi valZ,

Thanks, presumably that is a foolproof method and UrlReferrer will always be null if they don't get to the page by linking from a previous one?