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 |
|
stagedancer
Starting Member
3 Posts |
 Posted - 2004-11-24 : 13:11:56
|
| Dear all, I'm writing an internet application that uses SQL server as a database. In this database you have about 20 tables that will contain information about all customers. Suppose I have about 50 customers. A customer can select, insert and update records in the database using the internet application. When a customer selects or updates records he should only be able to see or change records that only apply to him and not to another customer.So in my sql statements I must always use a where clause that points to the correct customer (where customer_nr=1 for example).Is it therefore better not let the customers use the 20 base tables but in stead create 20 views for each customer that already point to only that customer ?This means that if you have 20 base tables and 50 customers you will have 1.000 views … Is there a limit on the views to use ?What would you guys suggest ? Or any other suggestion ? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-24 : 13:16:33
|
| Best would to have all database access via stored procs then you can put the logic and security in the SPs.Next best would be to implement security via views - but that is restrictive as views aren't as flexible as SPs.Worst and to be avoided is to give the application permission on the tables - that binds the application to the database structure.seehttp://www.nigelrivett.net/BadThings.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-11-24 : 17:49:59
|
| This page really made me laugh! Don't get me wrong I agree with it totally but the database I have to work with breaks quite a few of those and there is nothing I can do about it. If I showed that to the supplier he wouldn't have a clue what it was about or how it affected his system!!!steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-25 : 01:39:41
|
| i suggest sprocs, better to have 1 sproc to handle the returning set than to have 1,000 viewsi'm not seeing the difference, you will still need to identify which view is to be accessed by a customer, probably through an account or ID and i'm imagining your application having 50 cases or ifs just to identify which view should be accessed by customer #1 (i hope not).--------------------keeping it simple... |
|
|
|
|
|
|
Views or not ??