Alrite, I am trying to explain something which most of my company people do not understand fully so please bare with me!
I have a roles/authorization/authentication and single sign (In future) on engine. Now problem is this is a central engine which used by all the products we sell. Which means
1.my product fails 4 other products sit their just waiting...
2.My transactions CANNOT be long because it implies that some request from one of the products has a chance of waiting for a long time., No I cannot allow dirty reads either for one reason! My product name is "Security Server"
Ok here is the scenario I am speaking off.. I have roles in a tree structure and managed Objects in another tree and each combination of managed object and role has unique set of actions and set of authorities associated with each action (grant , revoke and inherit)
So if there is no authority associated to an action then for that action we need to go to the parent role and find authority, and if not its parent and so forth.
While setting authorities we need to put an audit trail of what authorities are changed by whom and when.
Currently we have 2 approaches on design table. 1 as you can guess is more simple and naturally something that comes from management style people (My manager, though he thinks he is super technical)
1. Whenever there is a change in authorities traverse through all the possible children and enter an audit record for all the managed objects and roles that will be affected. (This is manager proposal and as you can guess he is adamant we go this way)
2. Store sufficient information in audit tables of the state of Objects at that given point of a time and recreate the authorities of each role and managed objects when an audit report is asked. ( My wonderful approach, I understand it is more algorithmic and more error prone. But believe me we have almost all the Business logic written already)
Approach 1 I am not veering to be because of 3 reasons.
1. All these Audit inserts need to happen in 1 transaction (it can be 10 or 10,000 records). Which means basically my OR mappings layer will have a ton of objects ready to be committed.
2. I have no control on how much time it can take, and in the mean time I cannot also allow dirty reads so a request comes on a locked row, request has to wait.
3. Just knowing I cannot project the volume of a transaction gives me jitters.
Please comment and help!!! Am I in correct direction?