I am in the early days of developing an app that has (I think) some unusual security requirements. It will be deployed eventually on an intranet.
As an example, please see the following:
There is a Clients table that everybody has access to. this will have the usual fields such as ID, LName, FName, ONames. It will not contain any address info, as there is a requirement to keep a history of address changes.
There is a 2nd table called ClientConfidentialDetails that includes the UserID of the user that has added another record to that table.
There will also be a table called LocalOffices (of the org) & there will also be a table called OfficeUsers that will contain a list of the UserID's, the corresp users name & the LocalOfficeID (FK to the LocalOfficesID fld).
Now the problem is that there is a requirement that only the User or group of Users in the same Local Office will (by default) have access to the records in the ClientConfidentialDetails table. However, there is also a requirement that the user that has inserted a record in that tbl be able to grant access to those records to a user (or the group of users in that 2nd LocalOffice).
So, how would I do this?
Normally, most articles I have read about WS access rights to SQL Server recommend using Windows Integrated Security. If that method is used, how would I get the LoginID passed through & inserted into the appropriate table?
Or perhaps in terms of the Architecture I am barking up the wrong tree (forgive the Australianism). If there is a better way, perhaps somebody could give me a clue or two.
Thanks in advance.