December 30, 2004 at 10:49 am
Guys and Gals,
Any help will be greatly appreciated. I'm just really looking into getting pointed into the right direction. A manager came to me today wanting to develop and new database with a VB.net front end. Usually no problem for me. But he threw a little fork in the plans. He would like a database developed for our yearly budget process. But he would like to have one database that multiple people can use. He wants only Person A to see person A's stuff, Person B to be able to see person B, C, D's stuff, etc....all while using a VB.net front end. I would like to keep it all the budget detail on one table but might be forced to have a table for each person. Any Ideas? It would be great if I could have Person A only access the rows in the table in which there id is in a column. Any of this make sense?
December 30, 2004 at 11:14 am
Yes, you can develop a row-level security solution through the use of a view. Vyas Kondreddi has a good explanation about how to go about this:
Implementing row level security in SQL Server databases
K. Brian Kelley
@kbriankelley
December 31, 2004 at 10:14 am
Vas has a good explanation, but this can be cumbersome to setup and administer. Be sure you spend lots of time testing and build some good tools to handle the administration part of this security.
January 3, 2005 at 2:29 pm
Thanks guys,
I'll have to look deeper into this avenue. I browsed it over and I'm not sure if I can make it fit my needs. I'm going to need multiple people accessing multiple rows. For Instance
Person A - All rows with 03 budget number
Person B - All rows with 12,14,21,22,01,03,04
Person C - All rows with 14,21
January 3, 2005 at 11:11 pm
As a suggestion (I haven't read that article)
Three tables (or more as your system requires)
A person table (contans person's name, id, etc)
A Person-BudgetNumber table (essentially a many-to-many link between Budget and Person) (just contains person_id and budget_number)
The Budget table. (containing amoung others the budget number)
Then in your views you join the above three tables and filter on the current user.
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2005 at 7:30 am
I second what GilaMonster has posted. Basically what you're doing in this case is creating your own permissions table. That Person-BudgetNumber join table is what you use to grant access to the appropriate budget numbers. If you use INNER JOINs on the views, only those entries that match up across all three tables will be displayed to the user.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply