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.
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.
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
It seems to methat this issuehad
been discussedat the forum
http://forums.asp.net/t/1788200.aspx/1?Data+Filtering.Andhttp://forums.asp.net/post/4946450.aspx
is marked asanswer.Sjnaughton,you
cancheck iton mydd_site,if
youwill bewilling andfree
time. Especially forthis issue, I created adata
modelDD_Forum. Choosethe modelfrom
the list ofmodels
by clicking "Dynamic Data Site" button.Open
the"Projects"table.You'll
seeall therows of the table. ThenLogin as admin.In
the table"Accounts"add
"Forum_prj2"role
to yourline.Now in the"Projects"table,you
willsee
only the"Project2"line.
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.
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.
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.
biga
Member
72 Points
54 Posts
Row level security
May 04, 2012 03:50 PM|LINK
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.
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Row level security
May 04, 2012 09:30 PM|LINK
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.
Always seeking an elegant solution.
valZ
Member
128 Points
40 Posts
Re: Row level security
May 05, 2012 05:10 AM|LINK
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.
biga
Member
72 Points
54 Posts
Re: Row level security
May 05, 2012 10:46 AM|LINK
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.
biga
Member
72 Points
54 Posts
Re: Row level security
May 05, 2012 11:56 AM|LINK
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.
valZ
Member
128 Points
40 Posts
Re: Row level security
May 05, 2012 12:03 PM|LINK
Does this mean that the Projects table should also have a ClietType field?
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Row level security
May 05, 2012 12:53 PM|LINK
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.
biga
Member
72 Points
54 Posts
Re: Row level security
May 05, 2012 01:37 PM|LINK
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.
biga
Member
72 Points
54 Posts
Re: Row level security
May 05, 2012 01:40 PM|LINK
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
valZ
Member
128 Points
40 Posts
Re: Row level security
May 05, 2012 03:44 PM|LINK
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?