Scenario:
1000s of users
1000s of dealers
100000s of customers (child of Dealers)
Many Orders (Child of Customers)
SecurityTable: This table lists the Dealers a User has access to, with role fields stating whether they may View, Modify, etc. Customers, orders, etc.
I plan to implement security of Customers and Orders at 3 levels:
1.) Individual row viewing, updating, inserting, deleting. I will call a SQL based Function/SP that will return a boolean(0,1). To it will be passed:
a.) Authenticated userID
b.) DealerID
c.) Operation (whether viewing, updating, inserting, deleting)
At this time I want individual row security to be stateless - I know I am paying a price for (double DB access with each operation).
2.) Multiple row viewing, updating, inserting, deleteing. When this is needed I will code specialized Stored Procs to apply the security at the server.
3.) Multi select. For efficiency, I want to the security to be applied at the server. I hope to use LLBLGen dynamic SQL.
It seems I need to be able to do the following on multi selects:
Select O.OrderID...
From Orders O
Inner join Customers C on C.CustomerID = O.CustomerID
Where exists(Select * from SecurityTable s where s.UseriD=@UserID and s.DealerID=C.DealerID and s.MayViewOrder=1)
My questions:
1.) How/where do I implement the "where exists" clauses for my multi selects?
2.) Any comments on my implementation plan?
Thanks,
