Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dr S.A.f.
Starting Member
13 Posts |
 Posted - 2008-02-15 : 05:47:45
|
| Hi,We are currently developing a crm that has the following main tables: client, sales, actions, contacts and employees with up to 4 million records. The db contains several other tables with extra information but these aren't relevant for this problem.Clients have sales, contacts and actions. sales and actions can have seperate contacts as well, and contacts can be related to employees in a way.Offcourse a security model was implemented but this only provides the option to hide or show all the records of these main tables. Now people noticed that it is a must to let people see for example only companies they created or do sales with. In other words access must be given at a record level.The first thing that pops in my head is to create a client_security, sales_security, ... table that contains the employee_id and client_id or sales_id, or ... And add a join with one of these security tables for each select that is executed.I wonder (because of the huge amount of data) if there isn't a better way to get this done?Another possible issue that came to mind was, that when some data doesn't need protection or some people have the rigth to see everything, we would have to create the records in the security table anyways, resulting in probably hundreds of thousands of records.Feedback on our solution (if we can call it that allready), or even keywords to find more about this issue via google (record based security really isn't the best description I guess) are most appreciated.thx in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-15 : 08:11:33
|
| Cant you use views to hide the data selectively to different users?http://msdn2.microsoft.com/en-us/library/aa905180(SQL.80).aspx |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-16 : 14:31:24
|
| And sql can grant permission at row level. |
|
|
|
dr S.A.f.
Starting Member
13 Posts |
Posted - 2008-02-25 : 06:13:27
|
| The problem is that the app always uses the same user to access the database. I think the requirements 'll have to be reconsidered or we 'll have to use a machine with a better performance. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-25 : 07:16:05
|
| http://weblogs.sqlteam.com/mladenp/archive/2006/08/12/11153.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
dr S.A.f.
Starting Member
13 Posts |
Posted - 2008-03-03 : 04:34:34
|
| Thx for the replies, I looked in to views a bit closer, and that's indeed what I needed, I just didn't know that views could be used like that. |
|
|
|
|
|
|
|
|
Record based security