SQL Server Performance Forum – Threads Archive
Securing Data within an application
In terms of performance, what do you think is the best way to restrict access to data within an application? For example, lets say you have buyers who can only buy from certain vendors: Table: BuyersFields: BuyerID, BuyerName Table: Vendors
Fields: VendorID, VendorName Table: VendorDataAccess
Fields: VendorID, BuyerID Furthermore, some buyers can buy from any vendor. What’s the best way to structure this? It would be easy to add an inner join to VendorDataAccess (dropping rows that don’t exist) but I don’t want to have a record for every combination of buyer/vendor for the buyers with access to all. I was planning on doing something in the where clause "WHERE v.VendorID IN ( SELECT …)" but I’m not sure if that’s the best method. Any ideas?
you could add a flag into the Buyers table which indicates access to all, that would eliminate the need for buyer/vendor mapping for this group of buyers. select vendorid, vendorname
from vendors
where vendorid in ( select vendorid from VendorDataAccess where buyerid = @buyerid )
or CanAccessAll = 1 As for buyers that can access a limited number of vendors then the VendorDataAccess table would be the cleanest and easiest method.
Yes, that’s basically what I was planning to do. I just wanted to make sure the in clause is the best way to go.
]]>